It gives me an error on end if


Sub b9shop()

Dim theRange As Range
Dim lastrow&, firstRow&, x&
Worksheets("Payroll").Activate
ActiveSheet.UsedRange.Select
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") 0 Then
Worksheets("payroll").Active
With ActiveCell.Cells(x + 2, 1)
FormulaR1C1 = _
"=VLOOKUP(Data!R[-5]C:R[410]C[6],Address!R2C1:R21C4,2)"
End If
Next
End Sub


--
Stacia

Need an End With just before End If
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"stacia" wrote in message
...
It gives me an error on end if


Sub b9shop()

Dim theRange As Range
Dim lastrow&, firstRow&, x&
Worksheets("Payroll").Activate
ActiveSheet.UsedRange.Select
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") 0 Then
Worksheets("payroll").Active
With ActiveCell.Cells(x + 2, 1)
FormulaR1C1 = _
"=VLOOKUP(Data!R[-5]C:R[410]C[6],Address!R2C1:R21C4,2)"
End If
Next
End Sub


--
Stacia

I believe you need to end the With statement first.

"stacia" wrote:

It gives me an error on end if


Sub b9shop()

Dim theRange As Range
Dim lastrow&, firstRow&, x&
Worksheets("Payroll").Activate
ActiveSheet.UsedRange.Select
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") 0 Then
Worksheets("payroll").Active
With ActiveCell.Cells(x + 2, 1)
FormulaR1C1 = _
"=VLOOKUP(Data!R[-5]C:R[410]C[6],Address!R2C1:R21C4,2)"
End If
Next
End Sub


--
Stacia