I am trying to vlookup for visible cells. Have 2 sheets Data (that has the required data) and Result(where i need the desired result). On the Data sheet I want to Autofilter on "sourceid" column and all that has "xtrader", i want to do a vlookup based on "entity id" and get my "source entity id" in my result sheet. similarly i want the entity id when i autofilter on "optex" as well.
Data Sheet
Below is my code - when i run this code, the values i get in result sheet is "#N/A". (I wrote this code just for getting "source entity id" based on "xtrader" only as a trial).
Dim sht, sht1 As Worksheet
Dim i As Long, LR As Long, LR1 As Long
Dim Rng As Range
Set sht = ActiveWorkbook.Worksheets("result")
Set sht1 = ActiveWorkbook.Worksheets("data")
LR = sht.UsedRange.Rows.Count
LR1 = sht1.UsedRange.Rows.Count
Set Rng = sht1.Range("B2:B" & LR1).Cells.SpecialCells(xlCellTypeVisible)
sht1.Range("A1:C1").AutoFilter Field:=2, Criteria1:="xtrader"
With sht
For i = 2 To LR
Range("B" & i).Value = (Application.VLookup(sht1.Range("B2:B" & LR1).Cells.SpecialCells(xlCellTypeVisible).Range("A" & i).Value, sht1.Range("A2:C75000"), 3, False))
Next i
End With
I get all "#N/A" values in my result sheet column B when i run the above code. But my result sheet should be as below -
Data Sheet
entity id | source id | source entity id |
---|---|---|
1001 | xtrader | xt-1 |
1002 | xtrader | xt-2 |
1003 | xtrader | xt-3 |
1004 | xtrader | xt-4 |
1005 | xtrader | xt-5 |
1006 | xtrader | xt-6 |
1007 | xtrader | xt-7 |
1008 | xtrader | xt-8 |
1009 | xtrader | xt-9 |
1010 | xtrader | xt-0 |
1001 | optex | op-1 |
1002 | optex | op-2 |
1003 | optex | op-3 |
1004 | optex | op-4 |
1005 | optex | op-5 |
1006 | optex | op-6 |
1007 | optex | op-7 |
1008 | optex | op-8 |
1009 | optex | op-9 |
1010 | optex | op-0 |
Below is my code - when i run this code, the values i get in result sheet is "#N/A". (I wrote this code just for getting "source entity id" based on "xtrader" only as a trial).
Dim sht, sht1 As Worksheet
Dim i As Long, LR As Long, LR1 As Long
Dim Rng As Range
Set sht = ActiveWorkbook.Worksheets("result")
Set sht1 = ActiveWorkbook.Worksheets("data")
LR = sht.UsedRange.Rows.Count
LR1 = sht1.UsedRange.Rows.Count
Set Rng = sht1.Range("B2:B" & LR1).Cells.SpecialCells(xlCellTypeVisible)
sht1.Range("A1:C1").AutoFilter Field:=2, Criteria1:="xtrader"
With sht
For i = 2 To LR
Range("B" & i).Value = (Application.VLookup(sht1.Range("B2:B" & LR1).Cells.SpecialCells(xlCellTypeVisible).Range("A" & i).Value, sht1.Range("A2:C75000"), 3, False))
Next i
End With
I get all "#N/A" values in my result sheet column B when i run the above code. But my result sheet should be as below -
entity id | source id - xtrader | source id - optex |
---|---|---|
1001 | xt-1 | op-1 |
1002 | xt-2 | op-2 |
1003 | xt-3 | op-3 |
1004 | xt-4 | op-4 |
1005 | xt-5 | op-5 |
1006 | xt-6 | op-6 |
1007 | xt-7 | op-7 |
1008 | xt-8 | op-8 |
1009 | xt-9 | op-9 |
1010 | xt-0 | op-0 |