PDA

View Full Version : VBA LOOKUP - How to Return Multiple columns of dynamic range?



plasteredric
07-10-2018, 03:52 AM
Hi all, I have the following code in my workbook which works to return columns 18 to 23 from the RTRImport sheet and puts the values into columns 1 to 6 on the Daily sheet. Is it possible to make the range dynamic? as the number of columns to be copied over may change. I've specified the start and end columns as 'RTRImportSheetMatrixStartCol' and 'RTRImportSheetMatrixEndCol'
I just don't know how to get the code to calculate the number of columns it needs to copy. Hope i've explained that well enough.

Any help will be truly appreciated



ReDim outarr(1 To DailyListLastRow - DailyListHeaderDataRow, 1 To 6)


For i = DailyListFirstDataRow To DailyListLastRow
For j = 6 To RTRImportSheetLastRow

If DailyListVirtArray(i, 7) = RTRImportSheetVirtualArray(j, 11) And DailyListVirtArray(i, 3) = RTRImportSheetVirtualArray(j, 7) Then

outarr(i - DailyListHeaderDataRow, 1) = RTRImportSheetVirtualArray(j, 18)
outarr(i - DailyListHeaderDataRow, 2) = RTRImportSheetVirtualArray(j, 19)
outarr(i - DailyListHeaderDataRow, 3) = RTRImportSheetVirtualArray(j, 20)
outarr(i - DailyListHeaderDataRow, 4) = RTRImportSheetVirtualArray(j, 21)
outarr(i - DailyListHeaderDataRow, 5) = RTRImportSheetVirtualArray(j, 22)
outarr(i - DailyListHeaderDataRow, 6) = RTRImportSheetVirtualArray(j, 23)

Exit For
End If
Next j
Next i

Range(.Cells(DailyListFirstDataRow, DailyListStartingHeaderFound.Column), .Cells(DailyListLastRow, DailyListStartingHeaderFound.Column + 5)) = outarr

End With

georgiboy
07-10-2018, 05:35 AM
Not sure if this is what you are looking for but thought i would give it a go:
Untested


Dim lastCol As Long
lastCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column


ReDim outarr(1 To DailyListLastRow - DailyListHeaderDataRow, 1 To lastCol)


For i = DailyListFirstDataRow To DailyListLastRow
For j = 6 To RTRImportSheetLastRow
If DailyListVirtArray(i, 7) = RTRImportSheetVirtualArray(j, 11) And DailyListVirtArray(i, 3) = RTRImportSheetVirtualArray(j, 7) Then
For x = 1 To lastCol
outarr(i - DailyListHeaderDataRow, x) = RTRImportSheetVirtualArray(j, x + 17)
Next x
Exit For
End If
Next j
Next i
Range(.Cells(DailyListFirstDataRow, DailyListStartingHeaderFound.Column), .Cells(DailyListLastRow, lastCol)) = outarr
End With

Hope this helps