VBA Runtime Error 1004 while assigning formula to a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1249559%22%20slang%3D%22en-US%22%3EVBA%20Runtime%20Error%201004%20while%20assigning%20formula%20to%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249559%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20small%20macro%20to%20apply%20a%20currency%20conversion%20with%20a%20fixed%20factor%20on%20selected%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20ApplyUSD_ConversionRate()%0A'%0A'%20Devides%20through%20USD_ConversionRate%20on%20selected%20cells%0A'%0A%20%20%20%20Dim%20cel%20As%20Range%0A%20%20%20%20Dim%20selectedRange%20As%20Range%0A%0A%20%20%20%20Set%20selectedRange%20%3D%20Application.Selection%0A%0A%20%20%20%20For%20Each%20cel%20In%20selectedRange.Cells%0A%20%20%20%20%20%20%20%20Debug.Print%20cel.Address%2C%20cel.Value%2C%20cel.HasFormula%2C%20cel.Formula%0A%20%20%20%20%20%20%20%20If%20Not%20cel.HasFormula%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20cel.Formula%20%3D%20%22%3D%22%20%26amp%3B%20cel.Value%20%26amp%3B%20%22%2FUSD_ConversionRate%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%26gt%3B%26gt%3B%20VBA%20Runtime%20Error%201004%20%E2%80%9CApplication-defined%20or%20Object-defined%20error%E2%80%9D%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20cel.Formula%20%3D%20%22%3D(%22%20%26amp%3B%20Right(cel.Formula%2C%20Len(cel.Formula)%20-%201)%20%26amp%3B%20%22)%2FUSD_ConversionRate%22%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20cel%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20figured%20out%20that%20I%20get%20a%20Runtime%20Error%201004%20only%20if%20the%20cell%20contains%20decimals.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3C%2FSTRONG%3E%3A%20I%20have%20set%20the%20regional%20settings%20to%20German%20on%20my%20computer%20so%20the%20decimal%20character%20is%20comma!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EExample%3A%20%3C%2FU%3E%3C%2FP%3E%3CP%3EIn%20case%20of%20success%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%24T%243%20%20%20%20%20%20%20%20%20%20%2065850%20%20%20%20%20%20%20%20False%20%20%20%20%20%20%20%20%2065850%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20of%20error%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%24T%242%20%20%20%20%20%20%20%20%20%20%20218924%2C886839899%20%20%20%20%20%20%20%20%20%20%20False%20%20%20%20%20%20%20%20%20218924.886839899%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENotice%20the%20difference%20in%20the%20number%20format%20of%20.Value%20and%20.Formula%20property%20of%20the%20cell.%20I%20suppose%20that%20this%20might%20be%20the%20reason%20of%20the%20Runtime%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBtw%2C%20the%20code%20is%20in%20a%20module%20of%20the%20workbook.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20how%20I%20could%20avoid%20the%20Runtime%20error%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1249559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1249607%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Runtime%20Error%201004%20while%20assigning%20formula%20to%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249607%22%20slang%3D%22en-US%22%3EIn%20this%20case%20you%20could%20use%20cel.FormulaLocal%20instead%20of%20cel.Formula.%20That%20will%20ensure%20Excel%20does%20not%20take%20the%20comma%20in%20your%20decimal%20number%20to%20be%20a%20thousand%20separator%20(VBA%20speaks%20American)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1250056%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Runtime%20Error%201004%20while%20assigning%20formula%20to%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250056%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%2C%20for%20your%20suggestion.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20actually%20resolve%20the%20issue%20by%20using%20.Formula%20instead%20of%20.Value%3A%26nbsp%3B%3C%2FP%3E%3CPRE%3E%20cel.Formula%20%3D%20%22%3D%22%20%26amp%3B%20cel.Formula%20%26amp%3B%20%22%2FUSD_ConversionRate%22%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have created a small macro to apply a currency conversion with a fixed factor on selected cells.

 

 

 

Sub ApplyUSD_ConversionRate()
'
' Devides through USD_ConversionRate on selected cells
'
    Dim cel As Range
    Dim selectedRange As Range

    Set selectedRange = Application.Selection

    For Each cel In selectedRange.Cells
        Debug.Print cel.Address, cel.Value, cel.HasFormula, cel.Formula
        If Not cel.HasFormula Then
            cel.Formula = "=" & cel.Value & "/USD_ConversionRate"
             >> VBA Runtime Error 1004 “Application-defined or Object-defined error”
        Else
            cel.Formula = "=(" & Right(cel.Formula, Len(cel.Formula) - 1) & ")/USD_ConversionRate"
        End If
    Next cel
End Sub

 

 

I figured out that I get a Runtime Error 1004 only if the cell contains decimals. 

 

Note: I have set the regional settings to German on my computer so the decimal character is comma!

 

Example:

In case of success: 

 

 

 

$T$3           65850        False         65850

 

 

In case of error: 

 

 

 

$T$2           218924,886839899           False         218924.886839899

 

 

 

Notice the difference in the number format of .Value and .Formula property of the cell. I suppose that this might be the reason of the Runtime error.

 

Btw, the code is in a module of the workbook.  

 

Any suggestions how I could avoid the Runtime error? 

 

2 Replies
Highlighted
In this case you could use cel.FormulaLocal instead of cel.Formula. That will ensure Excel does not take the comma in your decimal number to be a thousand separator (VBA speaks American)
Highlighted

Thanks, @Jan Karel Pieterse , for your suggestion. 

 

I could actually resolve the issue by using .Formula instead of .Value: 

 cel.Formula = "=" & cel.Formula & "/USD_ConversionRate"