Macro, autofill formula to end of column

  • Thread starter Thread starter Monica
  • Start date Start date
M

Monica

I'm writing a macro that will insert a column, calculate a
formula and then fill the appropriate formula to the end
of the column. My problem is the column lentgh varies.
So I need to be able to reference a variable range. This
is what I have tried.

CNT = Range("B1", Range("B1").End(xlDown)).Count
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "run"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]<R[-1]C[1]+3,R[-1]C,RC
[1])"
Range("A3").Select
Selection.NumberFormat = "m/d/yy h:mm"
Selection.AutoFill Destination:=Range("A3:A249"),
Type:=xlFillDefault
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

I have changed the range in this code ...

Selection.AutoFill Destination:=Range("A3:A249"),
Type:=xlFillDefault

to
Range("A3:A[cnt]")
and
RC:R[cnt]C
with no luck.

The column length is going to vary from 100 to 5000. I
don't want to set it to A5000 because the data is analyzed
further.

Thanks,
Monica
 
Monica,

Try amending the code to read :-


CNT = Range("B1", Range("B1").End(xlDown)).Count
Debug.Print CNT

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "run"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]<R[-1]C[1]+3,R[-1]C,RC[1])"
Range("A3").Select
Selection.NumberFormat = "m/d/yy h:mm"
Selection.AutoFill Destination:=Range("A3", "a" & CNT)
Type:=xlFillDefault
Range("A3", "A" & CNT).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:=False, Transpose:=False
 
Thanks worked like a charm!!!
-----Original Message-----
Monica

Change
Range("A3:A[cnt]")
too range(cells(3,1),cells(cnt,1)

Regards
Peter
-----Original Message-----
I'm writing a macro that will insert a column, calculate a
formula and then fill the appropriate formula to the end
of the column. My problem is the column lentgh varies.
So I need to be able to reference a variable range. This
is what I have tried.

CNT = Range("B1", Range("B1").End(xlDown)).Count
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "run"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]<R[-1]C[1]+3,R[-1]C,RC
[1])"
Range("A3").Select
Selection.NumberFormat = "m/d/yy h:mm"
Selection.AutoFill Destination:=Range("A3:A249"),
Type:=xlFillDefault
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

I have changed the range in this code ...

Selection.AutoFill Destination:=Range("A3:A249"),
Type:=xlFillDefault

to
Range("A3:A[cnt]")
and
RC:R[cnt]C
with no luck.

The column length is going to vary from 100 to 5000. I
don't want to set it to A5000 because the data is analyzed
further.

Thanks,
Monica


.
Click to expand...
.
Click to expand...
 
Back
Top