plasteredric
09-23-2017, 03:21 AM
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
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
Can't seem to figure it out, my knowledge of vba is basic if anyone can help
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