Dave,

I just tried this and still errors on line
RB.Offset(0, -1).FormulaR1C1 = csFORMULA

I initially had the code plug the formula in to a cell in Col A then copy
the cell, select entire colA, and paste special as formula, but this filled
entire page way past the end of my data in Col B.

Is there a way to maybe do something similar but only as Col B has data?


"Dave Peterson" wrote:

Maybe:

Const csFORMULA = "=IF(RC[1]<"""","""",ISNUMBER(MATCH(RC[1]," & _
"'[QA_Accounts_Overview2007.xls]LB SUMMARIES '!C1,0)))"

Dim RB As Range
Set RB = Range("B1")
Do Until IsEmpty(RB.Value)
RB.Offset(0, -1).FormulaR1C1 = csFORMULA
Set RB = RB.Offset(1, 0)
Loop
Range("A2").Select



Brent E wrote:

I tried your formula w/ my own slight variation:

Const csFORMULA =
"=IF(RC[1]<"""""""",,ISNUMBER(MATCH(RC[1],'[QA_Accounts_Overview2007.xls]LB
SUMMARIES'!C1,0)))"
Dim RB As Range
Set RB = Range("B1")
Do Until IsEmpty(RB.Value)
RB.Offset(0, -1).Formula = csFORMULA
Loop
Range("A2").Select

I am getting an error on this line:
RB.Offset(0, -1).Formula = csFORMULA

Please advise.

Thanks,

"JE McGimpsey" wrote:

One way:

Const csFORMULA = "<your formula here"
Dim rB As Range
Set rB = Range("B1")
Do Until IsEmpty(rB.Value)
rB.Offset(0, -1).Formula = csFORMULA
Loop

In article ,
Brent E wrote:

Good morning,

I need a VBA module to do the following:

For each cell in ColB that is not blank or null,
paste formula in ColA corresponding cell.

So something like
Do Until
ColB cell is blank

Paste formula in colA cell

End.

Please assist.

Thanks Much,


--

Dave Peterson