Range Offset

[kwrigh6]

In one sheet, if the value in column A is "(HAT)" then the columns next to it are 123, 456, 789, 101, respectively. (These are made up data). The problem is those data are in another sheet and I am not sure how to reference them since VBA is in R1C1 form and the location of the data in the first sheet may change. This is what I currently have:


For i = 0 To 40
If Range("A1").Offset(i, 0).Value = "(HAT)" Then
Range("C1").Offset(i, 0).Value = "123"
Range("D1").Offset(i, 0).Value = "456"
Range("E1").Offset(i, 0).Value = "789"
Range("F1").Offset(i, 0).Value = "101"
End If
Next i

[JoeFoot]

try:

Please Login or Register  to view this content.

this assumes your column A is in sheet1 and your data is to be sent to sheet2

[JoeFoot]

alternatively use this for multiple rows:

Please Login or Register  to view this content.

[kwrigh6]

I don't want the data sent to sheet2, I am pulling the data from sheet2 to be sent to sheet1. But the location of the data may change (if the user of this excel workbook adds rows in sheet1 or sheet2).

[JoeFoot]

If you need to send the data to a different sheet then just change the names as needed.

As for the size of the list, the current loops cycles all the way to row 40. If you need more rows just increment the 40 to a number bigger than the foreseeable size of the dataset. If that is not sufficient then there are many posts on this forum how to find the number of rows in a table