VBA Range.Formula with ampersand and excel formulas

Question

can you please help me with the below issue?

My use case: in the excel worksheet "Sheet_1" I need to reference cells which are located in the "SourceSheet", and often I need to apply formulas to the referenced cells. "Sheet_1" and "SourceSheet" are located in the same workbook. SourceSheet is declared as a variable of type string and, through an input box, the user can inform the real name of SourceSheet. Ultimate objective is to extract essential informations from SourceSheet and display them in Sheet_1 in a more human friendly layout.

Below please find the subroutine I built. While it runs smoothly for the first five Range.Formula lines of code, I get a "Run-time error '1004': Application-defined or object-defined error" on the sixth one where I introduce LEFT(. In a debugging attempt, I tried to change $B$22 with another cell but nothing, it still throws an error. I need to understand where the root cause is because further down in the code I have much more complex formulas which have to work.

Can anyone please help me? Thank you in advance!

Sub ImportFromDownload()
Application.ScreenUpdating = False

'
' ImportFromDownload Macro
'

    Dim SourceSheet As String
    
    'Get from the user the name of the SourceSheet to import from and consider it as type = 2 i.e. text
    
    SourceSheet = Application.InputBox("Enter the name of the sheet to import from (case sensitive)", Type:=2)

    'Enter in the given cell range of Sheet_1 a concatenated string leveraging the name of the sheet entered by the user
    'and the right cell so that it can be interpreted by excel as a reference to the cell
    
    'Risk Identified
        
    Worksheets("Sheet_1").Range("D4:F4").Formula = "=" & SourceSheet & "!" & "$B$1"
    Worksheets("Sheet_1").Range("D6:F6").Formula = "=" & SourceSheet & "!" & "$B$2"
    
    'Risk Description
    
    Worksheets("Sheet_1").Range("D10:F10").Formula = "=" & SourceSheet & "!" & "$B$4"
    Worksheets("Sheet_1").Range("D12:F12").Formula = "=" & SourceSheet & "!" & "$B$5"
    Worksheets("Sheet_1").Range("D14:F14").Formula = "=" & SourceSheet & "!" & "$B$6"
    
    'Likelihood Score
        
    Worksheets("Sheet_1").Range("D18:F18").Formula = "=" & "LEFT(" & SourceSheet & "!" & "$B$22" & ";1)"
    
    'Financial Impact Score
        
    Worksheets("Sheet_1").Range("D20:F20").Formula = "=" & SourceSheet & "!" & "$B$15"
    Worksheets("Sheet_1").Range("D22:F22").Formula = "=" & SourceSheet & "!" & "$B$12"
    Worksheets("Sheet_1").Range("D24:F24").Formula = "=" & SourceSheet & "!" & "$B$13"
    
    'Non-Financial Impact Score
        
    Worksheets("Sheet_1").Range("D26:F26").Formula = "=MAX(" _
    & "IF(" & SourceSheet & "!" & "$B$16=""n.a."";0;LEFT(" & SourceSheet & "!" & "$B$16;1));" _
    & "IF(" & SourceSheet & "!" & "$B$17=""n.a."";0;LEFT(" & SourceSheet & "!" & "$B$17;1));" _
    & "IF(" & SourceSheet & "!" & "$B$18=""n.a."";0;LEFT(" & SourceSheet & "!" & "$B$18;1));" _
    & "IF(" & SourceSheet & "!" & "$B$19=""n.a."";0;LEFT(" & SourceSheet & "!" & "$B$19;1));" _
    & "IF(" & SourceSheet & "!" & "$B$20=""n.a."";0;LEFT(" & SourceSheet & "!" & "$B$20;1)))"
        
    Worksheets("Sheet_1").Range("D28:F28").Formula = "=" & SourceSheet & "!" & "$B$16"
    Worksheets("Sheet_1").Range("D30:F30").Formula = "=" & SourceSheet & "!" & "$B$17"
    Worksheets("Sheet_1").Range("D32:F32").Formula = "=" & SourceSheet & "!" & "$B$18"
    Worksheets("Sheet_1").Range("D34:F34").Formula = "=" & SourceSheet & "!" & "$B$19"
    Worksheets("Sheet_1").Range("D36:F36").Formula = "=" & SourceSheet & "!" & "$B$20"
    
    'Assessment Assumptions
        
    Worksheets("Sheet_1").Range("D40:F40").Formula = "=" & SourceSheet & "!" & "$B$21"
    Worksheets("Sheet_1").Range("D42:F42").Formula = "=" & SourceSheet & "!" & "$B$23"
    Worksheets("Sheet_1").Range("D44:F44").Formula = "=" & SourceSheet & "!" & "$B$24"
    
    'Mitigation Measures
        
    Worksheets("Sheet_1").Range("B50").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$C$39);" - -";" & SourceSheet & "!" & "$C$39)"
    Worksheets("Sheet_1").Range("D50").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$B$39);" - -";" & SourceSheet & "!" & "$B$39)"
    Worksheets("Sheet_1").Range("F50").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$D$39);" - -";" & SourceSheet & "!" & "$D$39)"
    
    Worksheets("Sheet_1").Range("B52").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$C$40);" - -";" & SourceSheet & "!" & "$C$40)"
    Worksheets("Sheet_1").Range("D52").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$B$40);" - -";" & SourceSheet & "!" & "$B$40)"
    Worksheets("Sheet_1").Range("F52").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$D$40);" - -";" & SourceSheet & "!" & "$D$40)"
       
    Worksheets("Sheet_1").Range("B54").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$C$41);" - -";" & SourceSheet & "!" & "$C$41)"
    Worksheets("Sheet_1").Range("D54").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$B$41);" - -";" & SourceSheet & "!" & "$B$41)"
    Worksheets("Sheet_1").Range("F54").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$D$41);" - -";" & SourceSheet & "!" & "$D$41)"
    
    Worksheets("Sheet_1").Range("B56").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$C$42);" - -";" & SourceSheet & "!" & "$C$42)"
    Worksheets("Sheet_1").Range("D56").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$B$42);" - -";" & SourceSheet & "!" & "$B$42)"
    Worksheets("Sheet_1").Range("F56").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$D$42);" - -";" & SourceSheet & "!" & "$D$42)"
    
    Worksheets("Sheet_1").Range("B58").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$C$43);" - -";" & SourceSheet & "!" & "$C$43)"
    Worksheets("Sheet_1").Range("D58").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$B$43);" - -";" & SourceSheet & "!" & "$B$43)"
    Worksheets("Sheet_1").Range("F58").Formula = "=IF(ISBLANK(" & SourceSheet & "!" & "$D$43);" - -";" & SourceSheet & "!" & "$D$43)"
    
    Range("A1").Select

End Sub

When adding a Formula via VBA you need to use the "US-style" separator, so "," and not ";" — Tim Williams, Feb 22 '23 at 20:49
Thank you Tim, this indeed solved the issue! Bad luck I live in Europe and did not know it. I would have expected a little help from VBA such as syntax error "hey use , and not ;" Again thanks a lot — Emanuele Ruggiero, Feb 22 '23 at 20:51
Don't forget you need single quotes around the worksheet name if there's any chance it might contain spaces - never hurts to add them just in case. — Tim Williams, Feb 22 '23 at 20:54

VBA Range.Formula with ampersand and excel formulas

0 Answers0