Just a quick question with regards my earlier post. I have managed to
combine my formula into a macro but am having a problem with the named
range I refer to. I am trying to get the last row (from column A) and
use it as a variable in the named range in column O. However it fails.
If I simply define the range (eg O8:O376) my code works fine. Any
ideas why it fails?

Also I'd like to apply the code only to cells in the renge CommRange
that are empty. That is for each cell, if empty then run the macro on
the cell ortherwise goto the next cell.

My code is below.

Thanks,

Richard


Sub Macro3()

Dim LastRow As Variant
Dim CommRange As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("O8", Range("O" & "LastRow")).Name = "CommRange"

Range("O8").Select
ActiveCell.FormulaR1C1 = _
"=MIN(3000,MROUND(IF(LEFT(RC1,1)=""4"",VLOOKUP(RC1 4,Agents!R1C1:R450C5,2,FALSE)+(VLOOKUP(RC14,Agents !R1C1:R450C5,3,FALSE)*RC6),VLOOKUP(RC14,Agents!R1C 1:R450C5,4,FALSE)+(VLOOKUP(RC14,Agents!R1C1:R450C5 ,5,FALSE)*R8C6)),0.01))"
Range("O8").Select
Selection.AutoFill Destination:=Range("CommRange"),
Type:=xlFillDefault
Range("CommRange").Select
ActiveWindow.SmallScroll Down:=-18
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub