Formula doesn't work when run as code in a macro

gers1978 · Sep 30th 2015

I spent a while creating a formula to format some names in column A the way I want them, and it worked fine. The formula is:

=IF(ISERROR(UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1)))),MID(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)+1,1) & " " & LEFT(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)-1),UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1))))

When I enter it, then fill/copy it down my column, it works a treat.
However I then recorded a macro of me doing it, and when I look at the code afterwards, it's changed to:

Code

"=IF(ISERROR(UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1)))),MID(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)+1,1) & "" "" & LEFT(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)-1),UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1))))"

and when I run said macro, the whole column has #VALUE errors.
Any ideas? Thanks!

**S O** · Sep 30th 2015

Re: Formula doesn't work when run as code in a macro

Can you show the whole code?

**rory** · Oct 1st 2015

Re: Formula doesn't work when run as code in a macro

Your formula is using RC as a reference, which refers to the cell containing the formula, hence the errors.

gers1978 · Oct 2nd 2015

Re: Formula doesn't work when run as code in a macro

Quote from S O;757403

Can you show the whole code?

Code

Sub RunAll()
    Pivot
    Names
    CalcData
End Sub
Sub Pivot()
'
' Pivot Macro
'
'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R314C17", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet2!R1C1", TableName:="PivotTable11", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("Staff Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
        "PivotTable11").PivotFields("Hours /Units"), "Sum of Hours /Units", xlSum
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("Company")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
        "PivotTable11").PivotFields("Selling Rate (£)"), "Sum of Selling Rate (£)", _
        xlSum
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("Company")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable11").PivotFields( _
        "Sum of Selling Rate (£)")
        .Caption = "Average of Selling Rate (£)"
        .Function = xlAverage
    End With
End Sub
Sub Names()
'
' Names Macro
'
'
    Range("D1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1)))),MID(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)+1,1) & "" "" & LEFT(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)-1),UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1))))"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D96")
    Range("D1:D96").Select
    Calculate
    Columns("D:D").EntireColumn.AutoFit
    Range("E1").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(VLOOKUP(RC[-4],Sheet1!R1C2:R999C4,2,FALSE) & "" / "" & VLOOKUP(RC[-4],Sheet1!R1C2:R999C4,3,FALSE))"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E96")
    Range("E1:E96").Select
    Calculate
    Columns("E:E").EntireColumn.AutoFit
End Sub
Sub CalcData()
'
' CalcData Macro
'
'
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]*7.5"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:F96")
    Range("F1:F96").Select
    Calculate
    Columns("F:F").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-18
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]"
    Range("G1").Select
    Selection.AutoFill Destination:=Range("G1:G96")
    Range("G1:G96").Select
    Calculate
    Columns("G:G").EntireColumn.AutoFit
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=(RC[-2]/7.5)*RC[-1]"
    Range("H1").Select
    Selection.AutoFill Destination:=Range("H1:H96")
    Range("H1:H96").Select
    Columns("H:H").EntireColumn.AutoFit
    Calculate
    Columns("H:H").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-15
    Range("F:F,H:H").Select
    Range("H1").Activate
    Selection.NumberFormat = "$#,##0.00"
    Columns("G:G").Select
    Selection.NumberFormat = "0.00"
    ActiveWindow.SmallScroll Down:=60
End Sub

Display More

Formula doesn't work when run as code in a macro

Participate now!

Share