VBA Do Until

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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
 
I think JE wanted to come down a row after the assignment:

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

A personal preference:

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
 
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,
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
ps. There's an extra space after Summaries. Sorry.

Dave said:
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 said:
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 said:
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

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,
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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,
Click to expand...
Click to expand...
Click to expand...
 
Dave Peterson said:
I think JE wanted to come down a row after the assignment:
Click to expand...

Yep - once again not pasting the tested version back into the
newsreader. Thanks for the correction.
 
Maybe it's time to share the string in csFormula again.

=====
After you get that formula correct, you may want to just plop that formula into
all the cells in the range in one step:

with activesheet
.Range("A1:A" & .cells(.rows.count,"B").end(xlup).row).formular1c1 _
= csformula
end with

But this won't help until you figure out what's wrong with that formula.

Try typing it in A1 manually.
Then switch to R1C1 reference style.
Then copy the formula from the formulabar and post this in your response.

(remember to switch back to A1 reference style if you
want--tools|Options|general tab))

Brent said:
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 said:
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 said:
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,

:

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

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,
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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,
Click to expand...
Click to expand...
Click to expand...
 
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

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 said:
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 said:
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,

:

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

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,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Named Columns in a formula within a VBA Macro 2
Need formula dates 7
Autofull formual 1
Invalid name error 4
Find and Replace problem 5
Find matches in 2 cols using Collection vs Dictionary 20
unique list 1
Sort Named Range 1

Back
Top