Vba

MichaelRose94

New Member
Joined
Feb 15, 2018
Messages
5
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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?
 
Upvote 0
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
 
Upvote 0
you cant. Last row would constantly need to be reviewed, or put in a large # to anticipate the last row.
 
Upvote 0
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:
Code:
    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"")"
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top