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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,030
record a macro
type in your formula
stop recording
look at the code.
 

MichaelRose94

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

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

Well-known Member
Joined
Jun 17, 2014
Messages
2,030
you cant. Last row would constantly need to be reviewed, or put in a large # to anticipate the last row.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
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"")"
 

Forum statistics

Threads
1,148,293
Messages
5,745,921
Members
423,984
Latest member
sayed manzar

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
Top