Concatenate then Fill Down

[FIRSTROUNDKO via OfficeKB.com]

Hi

I have the VBA CODE of 24 lines

Range("A2").Formula = Range("AA2") & " / " & Range("AG2") & " / " & Range
("AI2")
Range("B2").Formula = Range("AA2") & " / " & Range("AG2") & " / " & Range
("AI2")
Range("C2").Formula = Range("AA2") & " / " & Range("AG2") & " / " & Range
("AI2")
,,,,,,
Range("V2").Formula = Range("AA2") & " / " & Range("BQ2") & " / " & Range
("BT2")

I want to fill these 24 columns down to lastrow in the Column AA. I dont
want to use R1C1.

Thanks in Advance

 

[Dave Peterson]

First, you can use .formulaR1C1 and the user will still see the formulas the way
they have that option turned on in excel. Your code won't impact that.

Second, I think you made a mistake with your pattern. You have AG2 in 3 of the
4 sample lines.

Third, I'd populate the cells with a formula and then convert to values.

Dim LastRow as long
with activesheet 'I like qualifying my ranges
lastrow = .cells(.rows.count,"AA").end(xlup).row

with .range("A2:A" & lastrow)
.formula = "=AA2&"" / ""&AG2&"" / ""&AI2
.value = .value 'or do it later
end with

...etc

End With


You could even drop the .value = .value in each range and get them all at once:

with .Range("a2:V" & lastrow)
.value = .value
end with

====
This works just like excel would when you select the range (manually), then type
the formula based on the activecell in that selection, and hit ctrl-enter to
enter all the cells in the selection.

Excel is smart enough to adjust those formulas.

 

[Dave Peterson]

ps. If these were dates, I wouldn't build the string that way, I'd use:

..formula = "=date(aa2,ag2,ai2)"

and format them nicely.

 

[FIRSTROUNDKO via OfficeKB.com]

Thanks this was very helpful

First, you can use .formulaR1C1 and the user will still see the formulas the way
they have that option turned on in excel. Your code won't impact that.

Second, I think you made a mistake with your pattern. You have AG2 in 3 of the
4 sample lines.

Third, I'd populate the cells with a formula and then convert to values.

Dim LastRow as long
with activesheet 'I like qualifying my ranges
lastrow = .cells(.rows.count,"AA").end(xlup).row

with .range("A2:A" & lastrow)
.formula = "=AA2&"" / ""&AG2&"" / ""&AI2
.value = .value 'or do it later
end with

...etc

End With

You could even drop the .value = .value in each range and get them all at once:

with .Range("a2:V" & lastrow)
.value = .value
end with

====
This works just like excel would when you select the range (manually), then type
the formula based on the activecell in that selection, and hit ctrl-enter to
enter all the cells in the selection.

Excel is smart enough to adjust those formulas.

Hi

[quoted text clipped - 18 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201005/1