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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

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.

"FIRSTROUNDKO via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

--

Dave Peterson

Thanks this was very helpful

Dave Peterson wrote:
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.a...mming/201005/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

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" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201005/1

--

Dave Peterson