Home

Cell extract

%3CLINGO-SUB%20id%3D%22lingo-sub-643012%22%20slang%3D%22en-US%22%3ECell%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643012%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20got%20a%20table%20like%20it%20is%20shown%20in%20a%20picture.%20I%20want%20to%20extract%20cells%20in%20a%20new%20table%20where%20the%20id%20of%20the%20column%20and%20id%20of%20the%20row%20is%20equally%20(%20in%20this%20case%20id_row%3D100%20and%20id_column%3D100).%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115907i674F8C3780A88B21%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%224.PNG%22%20title%3D%224.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643012%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643039%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347960%22%20target%3D%22_blank%22%3E%40maloks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3Euse%20this%20macro%20from%20my%20vba-tanker%20to%20do%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20TransferData()%3CBR%20%2F%3EDim%20lngRow%20As%20Long%3CBR%20%2F%3EDim%20lngRowMax%20As%20Long%3CBR%20%2F%3EDim%20rngFind%20As%20Range%3CBR%20%2F%3EDim%20lngZ%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3ElngZ%20%3D%201%3CBR%20%2F%3EWith%20Sheet1%3CBR%20%2F%3ElngRowMax%20%3D%20.Range(%22A%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20lngRow%20%3D%202%20To%20lngRowMax%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rngFind%20%3D%20Sheet1.Rows(1).Find(what%3A%3D.Range(%22A%22%20%26amp%3B%20lngRow).Value%2C%20lookat%3A%3DxlWhole)%3CBR%20%2F%3EIf%20Not%20rngFind%20Is%20Nothing%20Then%3CBR%20%2F%3ESheet2.Cells(lngZ%2C%201).Value%20%3D%20.Range(%22A%22%20%26amp%3B%20lngRow).Value%3CBR%20%2F%3ESheet2.Cells(lngZ%2C%202).Value%20%3D%20.Cells(lngRow%2C%20rngFind.Column).Value%3CBR%20%2F%3ElngZ%20%3D%20lngZ%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20lngRow%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eregards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645968%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347960%22%20target%3D%22_blank%22%3E%40maloks%3C%2FA%3E%26nbsp%3B%2C%20it%20could%20be%20done%20in%20few%20steps%20with%20Power%20Query%20as%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
maloks
Occasional Visitor

I have got a table like it is shown in a picture. I want to extract cells in a new table where the id of the column and id of the row is equally ( in this case id_row=100 and id_column=100). 

4.PNG

2 Replies

@maloks 

Hi, 

use this macro from my vba-tanker to do it

 

Sub TransferData()
Dim lngRow As Long
Dim lngRowMax As Long
Dim rngFind As Range
Dim lngZ As Long

lngZ = 1
With Sheet1
lngRowMax = .Range("A" & .Rows.Count).End(xlUp).Row

For lngRow = 2 To lngRowMax

Set rngFind = Sheet1.Rows(1).Find(what:=.Range("A" & lngRow).Value, lookat:=xlWhole)
If Not rngFind Is Nothing Then
Sheet2.Cells(lngZ, 1).Value = .Range("A" & lngRow).Value
Sheet2.Cells(lngZ, 2).Value = .Cells(lngRow, rngFind.Column).Value
lngZ = lngZ + 1
End If

Next lngRow

End With

End Sub

 

regards

Bernd

www.vba-tanker.com

@maloks , it could be done in few steps with Power Query as attached

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies