Vba

[MichaelRose94]

Hello,

Does anyone know why the following won't go through in VBA?

Sub ColumnC()

Range("D1").Formula = "=--Text("C1", "00\:00")"

End Sub

Thanks

 

[ranman256]

record a macro
type in your formula
stop recording
look at the code.

 

[MichaelRose94]

record a macro
type in your formula
stop recording
look at the code.

But does the code allow for "" within the other side of the RHS of the line?

I would just like =--Text("C1", "00\:00") to be inserted and in every cell of the column (C2,C3,C4 etc.) but I am unable to do that.

Any sugestions?

 

[MichaelRose94]

found the answer: so it is ActiveCell.FormulaR1C1 = "=--TEXT(RC[-1], ""00\:00"")"

I was wondering instead of Selection.AutoFill Destination:=Range("A1:A1440")

What would be the best way of selecting the last row, as it may not always be row 1440? thanks

 

[ranman256]

you cant. Last row would constantly need to be reviewed, or put in a large # to anticipate the last row.

 

[Joe4]

found the answer: so it is ActiveCell.FormulaR1C1 = "=--TEXT(RC[-1], ""00\:00"")"

I was wondering instead of Selection.AutoFill Destination:=Range("A1:A1440")

What would be the best way of selecting the last row, as it may not always be row 1440? thanks

Since your formula is looking at one column to the left, it is not column A you want to populate, is it? There are no columns to the left of column A.
In your original formula, it looks like it was populating column D.
If you want to autofill column D based on the entries in column C, then you could do something like this:

    Dim lrow As Long
    
'   Find last row with data in column C
    lrow = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Populate column D with formula
    Range("D1:D" & lrow).FormulaR1C1 = "=--TEXT(RC[-1], ""00\:00"")"