setting an offest based on a cell value

[Perksy_no1]

Hi all,

I've got a macro which loops down column G looking for the word "projected buy". I then offset using a variable (r1) I then need to off set it some more based on the value in the corresponding row in column B, format the cell then carrying on down column G looking for the next "projected buy"

I can most of this code working but I cant get it to offset buy the value in column B. I've highlighted the part of the code im struggling with

any help would be much appreciated, Thanks in advance Mark

Dim r1 As Long
Dim r2 As Long
Dim LastRow As Long
r1 = Sheets("Sheet1").Range("B1").Value
r2 = Sheets("Sheet1").Range("B2").Value
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Range("G6").Select
    Do
        If ActiveCell.Value = "Projected Buy" Then
            Selection.Offset(0, r1 + "RC[-5]").Select
                With Selection.Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                End With
            Selection.Offset(1, r2).Select
        Else
            Selection.Offset(1, 0).Select
        End If
    Loop Until ActiveCell = ""
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Perksy_no1]

sounds like you should use Index and Match rather than Vlookup. Its more flexible in circumstances like this

That would be my choice but its an inhertiated spread sheet which a lot of people use. unfortunatly the cell where you would normaly match isnt the same on all the sheets it looks up from. as this isnt the only formula on the sheet

 

[Jonmo1]

Try

Sub test()
Dim r1 As Long, i As Long, x As Long, LastRow As Long
r1 = Sheets("Sheet1").Range("B1").Value
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 6 To LastRow
    With Range("G" & i)
        If .Value = "Open Orders" Then
            For x = 0 To 28
                .Offset(0, r1 + x).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(R[-1]C2,'latest oo'!R2C1:R257C10," & x + 2 & ",FALSE)),0,VLOOKUP(R[-1]C2,'latest oo'!R2C1:R257C10," & x + 2 & ",FALSE))"
            Next x
        End If
    End With
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Perksy_no1]

love it! thanks very much for this