Brent

What is in the cells in column B that the code ignores as blank?

Maybe an extraneous space or a formula that returns "" which is not blank,
just looks like it.


Gord Dibben MS Excel MVP

On Wed, 2 May 2007 12:15:02 -0700, Brent E
wrote:

OK, I was able to get the formula to work now, but for some reason is still
pasting the formula in ColA past the end of my data in Col B.

Any thoughts?

By the way thank u both for all your assistance.

"Brent E" wrote:

Dave, I just tried your second formula too, but am getting an error on
rB.Offset(0, -1).Formula = csFORMULA

Any ideas?

This is code I used:

Const csFORMULA = "<your formula here"
Dim rB As Range
Set rB = Range("B1")
Do
if isempty(rb.value) then
exit do
end if
rB.Offset(0, -1).Formula = csFORMULA
Set rB = rB.Offset(1, 0)
Loop



"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,