VBA Lookup, how to return multiple columns?
Hi all, I have the following code in my workbook. I'm trying to get the outarr2 to return both columns 54 & 55 from the RTR_Import sheet and put the values into columns 11 & 12 on the active sheet. (column 54 to 11 & column 55 to 12)
Can't seem to figure it out, my knowledge of vba is basic if anyone can help
Code:
Sub GET_RTR_RESULTS()
Dim outarr() As Variant ' this defines an array of variants.
Dim outarr1() As Variant ' this defines an array of variants.
Dim outarr2() As Variant ' this defines an array of variants.
' so that it can write it back to the worksheet
With Sheets("RTR_Import") ' the following code is excecute on the RTR_Import sheet
lastrow = .Cells(Rows.Count, "CH").End(xlUp).Row ' this finds the last cell with data in it on column "CH"
Import = Range(.Cells(1, 1), .Cells(lastrow, 56)) ' this copies all of the data (columns 1 to column 56 [BD]) from the RTR_Import sheet to the array "Import"
End With ' the code "with " RTR_Import sheet ends here
With ActiveSheet ' do everything on the active sheet from here on
lastnam = .Cells(Rows.Count, "F").End(xlUp).Row ' This finds the last cell with data in it in column F of the active sheet
Namearr = Range(.Cells(1, 6), .Cells(lastnam, 6)) ' This loads all of the data in column 6 (F) on the active sheet in the arry Namearr
ReDim outarr(1 To lastnam - 17, 1 To 1) ' the redimensions the output array to the correct size , now that we know how many lines we have
ReDim outarr1(1 To lastnam - 17, 1 To 1) ' the redimensions the output array to the correct size , now that we know how many lines we have
ReDim outarr2(1 To lastnam - 17, 1 To 1) ' the redimensions the output array to the correct size , now that we know how many lines we have
For i = 18 To lastnam ' this controls the loop through all the names in Namearr (col F active sheet)
For j = 6 To lastrow ' this control the loop through the data in Import ( RTR_ Import )
If Namearr(i, 1) = Import(j, 51) Then ' this compares the data in column F of active sheet with data in column 51 (AY) of RTR_import '
' Namearr is Col F active sheet, Import is all the data from RTR_Import
outarr(i - 17, 1) = Import(j, 53) ' a match is found so copy the data from column 53 of the RTR_Import sheet to the output array on the same line as the Active sheet (i)
outarr1(i - 17, 1) = Import(j, 50) ' a match is found so copy the data from column 50 of the RTR_Import sheet to the output array on the same line as the Active sheet (i)
outarr2(i - 17, 1) = Import(j, 54) ' a match is found so copy the data from column 54 of the RTR_Import sheet to the output array on the same line as the Active sheet (i)
Exit For
End If
Next j
Next i
Range(.Cells(18, 8), .Cells(lastnam, 8)) = outarr ' write the output data to the active sheet in column 8 (H)
Range(.Cells(18, 9), .Cells(lastnam, 9)) = outarr1 ' write the output data to the active sheet in column 9 (I)
Range(.Cells(18, 11), .Cells(lastnam, 11)) = outarr2 ' write the output data to the active sheet in column 11 (k)
End With
End Sub
