Solved: Macro won't run

[Hoopsah]

Hi (again!!!)

Anyone know why this macro won't run?

Sub Update_Columns()
'
' Update_Columns Macro
' Macro recorded 18/11/2009 by Gerry McNally
'
'
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
Range("H3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'I:\Customer Connections\Finance\Income\Sundry Invoices\[New Connection Invoices - Master.xls]Sheet1'!R3C5:R65536C8,4,FALSE)"
Range("H3").AutoFill .Range("H3").Resize(LastRow)
Range("I3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,""Paid"",""Not Paid"")"
Range("I3").AutoFill .Range("I3").Resize(LastRow)
Range("I3").Select
End With
End Sub

Again, after the VLOOKUP the cell isreferenced RC[-3] - this points to cell E3

the macro stops at the line
Range("H3").AutoFill .Range("H3").Resize(LastRow)

Thanks for any help

Hoopsah

[mdmackillop]

What value do you have for LastRow?

[Bob Phillips]

Does this work for you



Sub Update_Columns()
'
' Update_Columns Macro
' Macro recorded 18/11/2009 by Gerry McNally
'
'
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Range("H3").FormulaR1C1 = "=VLOOKUP(RC[-3],'I:\Customer Connections\Finance\Income\Sundry Invoices\[New Connection Invoices - Master.xls]Sheet1'!R3C5:R65536C8,4,FALSE)"
.Range("H3").AutoFill .Range("H3").Resize(LastRow)
.Range("I3").FormulaR1C1 = "=IF(RC[-2]=0,""Paid"",""Not Paid"")"
.Range("I3").AutoFill .Range("I3").Resize(LastRow)
End With
End Sub

[bryVA]

You have to have some values in Column A. It has to be able to count at least one cell in column A to get a number to place in LastRow. If you have values on column A it will work or if you change the code to count the column you need it to count.

Hope this helps,

-B

[Hoopsah]

Hi Bob

nope! still doesn't work and stops at line:

.Range("H3").AutoFill .Range("H3").Resize(LastRow)

[Hoopsah]

Hi

following on from bryVA has written - I changed the A to a C (Column C always has data in it) and re-ran the macro and it works fine now.

Thanks for all your help guys

Gerry