Excel set a range's value equal to a function

Wed, 04 Mar 2009 00:30:23 Z

Ok I'm pretty certain this should be really easy but I can't seem to figure it out or find any articles on it.

I would like to have a line of VBA code that will set the values a range of cells to a certain function.

Easy enough just

Sheets("Quote_Cover").Range("B18:B1000").Value = "=Row()"

However what I want is to be able to include an If statement that references another cell in the same row so for example I want

B18=If(A18="","",Row())
B19=If(A19="","",Row())
B20=If(A20="","",Row())
. . .

I know of two ways to do this but both run very slow I could put the formula somewhere and use VBA to copy and paste it or I could use a for each loop like this

For Each linerow In Sheets("Quote_Cover").Range("B18:B1000")
Sheets("Quote_Cover").Cells(linerow.Row, "B") = "=IF(A" & linerow.Row & "="""","""",Row())"
Next linerow

But again both are extremely slow for this many cells so I would much rather have something of the form

Sheets("Quote_Cover").Range("B18:B1000").Value = "=IF(A" & Row() & "="""","""",Row())"