Code for Constant Row Look Up

[Carl Clements]

Hi,

I need some VBA code that puts in formulas to pull data in various columns within row 2 of another worksheet.

The formulas need to be entered below some other data, which is populated from another prior macro.

The below code works well but as the prior macro can populate a different number of rows on different days, the Row count then also changes and doesn't always refer to row 2 of 'HLDRT before' tab.

ActiveCell.FormulaR1C1 = _
"=IF('HLDRT before'!R[-22]C[-18] = ""A17"",RIGHT('HLDRT before'!R-20C[14],3)&'HLDRT before'!R-20C[9]&'HLDRT before'!R-20]C[-13],"""")"

Is there a way I can tell the above code to always use row 2 from the 'HLDRT before' sheet, while keeping the column number lookups the same?

I think there is a way to do it by removing the [] signs but I can't get it to work. I also then need the macro to copy these down so I'm not sure if using $ will cause problems?

Thanks,
Carl

 

[Andrew Poulsom]

Do you mean?

ActiveCell.FormulaR1C1 = _
"=IF('HLDRT before'!R2C[-18] = ""A17"",RIGHT('HLDRT before'!R2C[14],3)&'HLDRT before'!R2C[9]&'HLDRT before'!R2C[-13],"""")"

 

[Carl Clements]

Thanks. It works although it puts $ after the column letter, eg B$2, which means I can't copy the formula down to then look at B3 etc. Is there a way around that?

 

[Andrew Poulsom]

You would have to calculate the offset between the active cell's row and row 2 and assign it to a variable. Then you can use that variable in your formula. Example:

Sub Test()
    Dim ROffset As Long
    With ActiveCell
        ROffset = 2 - .Row
        .FormulaR1C1 = _
            "=IF('HLDRT before'!R[" & ROffset & "]C[-18] = ""A17"",RIGHT('HLDRT before'!R[" & ROffset & "]C[14],3)&'HLDRT before'!R[" & ROffset & "]C[9]&'HLDRT before'!R[" & ROffset & "]C[-13],"""")"
    End With
End Sub

 

[Carl Clements]

Brilliant, that worked perfectly. Many thanks.