CODEWITHSUNDEEP
vbaexcel
user3805867
user3805867

Reputation: 5

Possible Cell referencing error in VBA [R1C1 style]

I'm having a problem when running this macro - it worked previously however now line 6 is giving me an error on a new computer (Also newer version of Excel). Is there an obvious problem here?

Sub CalculateDifferences()

Dim Col As Long

Col = Worksheets("AgedDebtors").Range("Z2").End(xlToLeft).Column

Worksheets("AgedDebtors").Range("Z2").End(xlToLeft).Value = "Difference"
Worksheets("AgedDebtors").Range("H1").Value = Worksheets("PivotTable").Range("Z2").End(xlToLeft).Column - 2
Worksheets("AgedDebtors").Range(Cells(3, Col), Cells(Range("A65535").End(xlUp).Row, Col)).Formula = "=$G3 - VLOOKUP($F3,PivotTable!$A$2:$J$3500, $H$1, FALSE)"
End Sub

Upvotes: 0

Views: 138

Answers (1)

Rory
Rory

Reputation: 34055

You haven't qualified the two Cells (and one extra Range) calls with a worksheet:

Worksheets("AgedDebtors").Range(Worksheets("AgedDebtors").Cells(3, Col), Worksheets("AgedDebtors").Cells(Worksheets("AgedDebtors").Range("A65535").End(xlUp).Row, Col)).Formula = "=$G3 - VLOOKUP($F3,PivotTable!$A$2:$J$3500, $H$1, FALSE)"

but you can tidy it all up with a With...End With block:

Sub CalculateDifferences()

Dim Col As Long
with Worksheets("AgedDebtors")
Col = .Range("Z2").End(xlToLeft).Column

.Range("Z2").End(xlToLeft).Value = "Difference"
.Range("H1").Value = Worksheets("PivotTable").Range("Z2").End(xlToLeft).Column - 2
.Range(.Cells(3, Col), .Cells(.Range("A65535").End(xlUp).Row, Col)).Formula = "=$G3 - VLOOKUP($F3,PivotTable!$A$2:$J$3500, $H$1, FALSE)"
end with
End Sub

Upvotes: 2

Related Questions