Declaring a Variable

[lsteffen]

Good afternoon,

How do I make B1 in the following code be a variable so that when it fills down, it will change to B2, B3, B4 etc?

Range("G1").Value = Application.Rept(" ", NumSpaces) & [B1].Value
Worksheets("Data").Range("G1:G" & LastRow).FillDown

Thank you,

Liz

 

[texasalynn]

try this

Range("G1").Formula = "=REPT("" ""," & NumSpaces & ")&RC[-5]"
Worksheets("Data").Range("G1:G" & LastRow).FillDown

 

[lsteffen]

That worked nicely, I tried to use it again in this case too, but what am I missing?

Range("G1").Select
ActiveCell.Formula = "=LEFT(RC[-3]&REPT(' ',10),10)"
Worksheets("Data").Range("G1:G" & LastRow).FillDown
Columns("G:G").Select

 

[lsteffen]

I figured it out, here is what I updated it to:

Range("G1").Formula = "=LEFT(RC[-3]& REPT("" "",10),10)"
Worksheets("Data").Range("G1:G" & LastRow).FillDown

Thank you much!
Liz

 

[jeffreybrown]

Hi Liz,

Just as an option you could also put all that on one line...!

Worksheets("Data").Range("G1:G" & lastrow).Formula = _
"=LEFT(RC[-3]& REPT("" "",10),10)"

 

[lsteffen]

Thank you, that works goreat as well!