Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to fill a variable number of cells 1

Status
Not open for further replies.

henio

MIS
Jun 25, 2003
60
GB
Hi all,

I recorded a macro for a simple task. I create a formula to format a cell based on the values of three others. I then copy it down to the end of the range.

The recorder gave me:

Sub Macro2()

'
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=TEXT(RC[-3],""0000"")&TEXT(RC[-2],""0000"")&TEXT(RC[-1],""0000"")"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D5")
End Sub

However, there may be any number of rows - the range in the recorded macro is fixed. How can I modify this to allow for a variable number of rows?

Thanks,
Henio
 
Hi henio,

Assuming that you want to fill column D down from d2 as far as you have data in column A, you can do this ..

Code:
Range([A2],[A2].End(xldown)).offset(0,3).FormulaR1C1 =  "=TEXT(RC[-3],""0000"")&TEXT(RC[-2],""0000"")&TEXT(RC[-1],""0000"")"

Enjoy,
Tony
 
Tony,

thanks for this. Sorry if my question was not clear enough, but you were spot on - definitely star quality!

Henio
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top