Hallo all!
I'm getting an error 'Runtime Error (91): Object variable or With block variable not set' when running the below code. I'm obviously doing something wrong when I try to set the values for the 'range' variables at the top.
When I remove the 'As Range' for the 4 range variables, it makes it all the way to the rCell.Offset(0, 1).Formula line, where I then get an error 'Runtime Error (424): Object Required'.
I can't work out where I've ****** up.
To set it in context, the procedure is supposed to look at column B, where there is a list of entries starting at B9, determine the last non-blank cell address, and therefore the entire range of the list in column B, and for each non-blank cell in column B, add the lookup formula in column C.
Can anyone help?
Much appreciated once again,
Mr. I R Dunce :stare:
Sub AddFormula()
'Adds lookup formula to show number of times each card was found in file
Dim rColumn As Range
Dim rCell As Range
Dim rLastCell As Range
Dim rFirstLastCellRange As Range
rLastCell = ThisWorkbook.Sheets("Summary").Range("B65000").End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
'MsgBox rLastCell when 'As Range' deleted, returns B12
rFirstLastCellRange = "B9:" & rLastCell
'MsgBox rFirstLastCellRange when 'As Range' deleted, returns B9:B12
rColumn = ThisWorkbook.Sheets("Summary").Range(rFirstLastCellRange)
For Each rCell In rColumn
rCell.Offset(0, 1).Formula = "=vlookup(rCell,NoPayCardFile_050712!$A$2:E200,5)"
Next rCell
End Sub