Adding function in a cell while recording a macro - why not?

[Patrick Niedermeyer]

Why doesn't the function that I entered into a cell return the requested data, specifically if I wrote the function in the cell while recording a macro?

-P.

Sub Auto_Timesheet()
'
' Auto_Timesheet Macro
'
' Keyboard Shortcut: Ctrl+v
'
Columns("F:F").Select
Range("F2").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = "Cost Code Only"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=left(E3,4)"
Range("F3").Select
End Sub

 

[Joe4]

When you record entering a formula, what gets recorded is the formula you entered, not the value that the formula returns.
This is the section here with your formula:

Range("F3").Select
ActiveCell.FormulaR1C1 = "=left(E3,4)"

 

[Fluff]

It either needs to be

ActiveCell.FormulaR1C1 = "=left(rc[-1],4)"

or

ActiveCell.Formula = "=left(E3,4)"

 

[Patrick Niedermeyer]

Fluff,

I understand there is different function to bring those first four characters, but I tried both lines of code, but and it doesn't produce what I was looking for. I've included an example data file that's been greatly simplified. In it you can see that I was trying to copy the left 4 characters from the B column to the C column. When I tried the replacements for my selection code shown above, it doesn't do what I need.

-P.

 

[Joe4]

So, is the issue that it is putting the formula in the cell as Text, and not as a formula so it isn't returning a value?
If it is doing that, it means that column C is formatted as Text. Change the format of column C to General or Number (before entering the formulas), and it will evaluate the formulas correctly.

 

[Fluff]

If the problem is that it's always looking at the same cell, try

Sub Patrick()
   Range("F:F").EntireColumn.Insert
   Range("F2").Value = "Cost Code Only"
   Range("F3:F" & Range("E" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=left(rc[-1],4)"
End Sub

 

[Patrick Niedermeyer]

Thanks, Fluff. I swapped out my code for those three lines and it worked like a gem.

-P.

 

[Fluff]

You're welcome & thanks for the feedback