Vlookup for multiple instances

[azwildcat]

I currently have an augmented 'vlookup' VBA code that I need to adjust, but I am new to creating VBA code. The code is listed below and has been modified to not only grab 1 cell of data, but the other 10 cells to the right of the cell in question(in the same row). I need this to be changed in order to find all instances for an index number(instead of grabbing the first instance of index number 2, I need this code to grab all instances of the index number 2 of which their could be 10 instances). The index number is first entered into the 'input sheet' and once the macro is executed, the information is found, copied and moved from the 'data table' to the 'input sheet'.

Please help and let me know if I can provide more information. Thanks.

The code is:


[VBA]Sub LookupTest1()
'
' LookupTest1 Macro
'
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim LC As Long
'LC= ws1.Cells(1, Columns.Count).End(xlToLeft).Column Dim C As Range
Set ws1 = Sheets("Data Table")
Set ws2 = Sheets("Input Sheet")
LR1 = ws1.Cells(Rows.Count, "B").End(xlUp).Row
With ws1.Range("$B$2:B" & LR1)
Set C = .Find(ws2.Range("$A$3"), LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
fstAdd = C.Address
Do
LR2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Cells(C.Row, "A").Resize(1, 11).Copy ws2.Cells(LR2 + 3, "A")
Set C = .FindNext(C)
Loop While Not C Is Nothing And fstAdd <> C.Address
Else: MsgBox " Value Not Found"
End If
End With

'
End Sub[/VBA]

[Bob Phillips]

Seems to work fine for me, as long as there is data in column A, otherwise it overwrites.

[azwildcat]

XLD,

thanks for taking a look. If you adjust the code from 1 to 5 for rows done in red below:
ws1.Cells(C.Row, "A").Resize(5, 11).Copy ws2.Cells(LR2 + 3, "A")

I do get 5 rows of data and 11 columns, however, I get onlly one instance of the index number that i am looking for and then i get instances of index numbers that I am not looking for that follow the index number in question. For example, the last time I ran this, my output was the 3rd instance of the index number 2(not all 3 instances of the index number 2 that I wanted) and then the all instances of the index number 3 that followed the aforementioned index number 2 in order. I can send over a copy of my spreadsheet if you think this would help.

Please let me know. Again, any and all help is very appreciated.

[Bob Phillips]

Seems to work fine for me unless I misunderstand what you mean. A workbook, with an example of what you expect to see, would help.

[azwildcat]

XLD, Thanks again for your help. I have uploaded an attachment with 3 spreadsheets. In "Input Sheet", I am using 2 as my index number, and I am receiving 1 instance of the index #2 and 3 instances of the index #3(this problem is repeated for spreadsheet "Input Sheet2". Please feel free to run the macro: 'LookupTest3()'. I have removed all other macros from this workbook. Thanks again.

[Bob Phillips]

As I said previously, as long as there is data in column A, there isn't, so don't lookup the last row based on column A

[vba]

Sub LookupCombinedTest3()
'
' LookupTest1 Macro
'
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim LC As Long
'LC= ws1.Cells(1, Columns.Count).End(xlToLeft).Column Dim C As Range
Set ws1 = Sheets("Data Table")
Set ws2 = Sheets("Input Sheet")
LR1 = ws1.Cells(Rows.Count, "B").End(xlUp).Row
With ws1.Range("$B$2:B" & LR1)
Set c = .Find(ws2.Range("$A$3"), LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
fstAdd = c.Address
Do
LR2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row
ws1.Cells(c.Row, "A").Resize(4, 11).Copy ws2.Cells(LR2 + 1, "A")
Set c = .FindNext(c)
Loop While Not c Is Nothing And fstAdd <> c.Address
Else
MsgBox " Value Not Found"
End If
End With
End Sub
[/vba]

[azwildcat]

XLD, Thanks again for your time. I made the change you suggested and everything works the way I intended it to.