Inserting semicolon between two quotation marks in VBA

Question

I am trying to insert a formula automatically within a certain range but somehow I am getting a runtime error 1004. Can someone please assist ?:

The Original in excel:

=@IF(ISNA(MATCH($B6;StepList;0));"";IF(@INDEX(StepIdTable;MATCH($B6;StepList;0);COLUMN())="";"";INDEX(StepIdTable;MATCH($B6;StepList;0);COLUMN())))

My Code:

Dim sMyString As String
sMyString = Chr(34) & Chr(34) & Chr(59) & Chr(34) & Chr(34)

.Range("C6:C25").Formula = "=@IF(ISNA(MATCH($B6,StepList,0)),"""",IF(@INDEX(StepIdTable,MATCH($B6,StepList,0),COLUMN())=" & sMyString & ",INDEX(StepIdTable,MATCH($B6,StepList,0),COLUMN())))"

The problem is "";"". I have tried everything to make this work

Does anyone have an idea ?

why those '@' in front of your functions ? Never saw that (and I've been a pro trainer on Excel) — iDevlop, Aug 10 '21 at 10:37

Алексей Р · Answer 1 · 2021-08-10T11:12:49.353

It's all about the number of double quotes. Try one of this approaches:

Sub test1()
    sMyString = """"""";"""""""
    
    frm = "=IF(F13=" & sMyString & ",1,0)"  'so
    
    frm = "=IF(F13="""""";"""""",1,0)"      'or so
    
    Debug.Print frm '=IF(F13=""";""",1,0) within the cell
    
    Range("F13").Formula = frm
End Sub

Explanation: since the escape of double quotes is their doubling, each conversion of a string containing double quotes adds or subtracts (depending on the direction of the conversion) these pairs, resembling a cascading effect

Tom Sharpe · Accepted Answer · 2021-08-10T11:40:25.947

Not sure if this is the answer because I'm in a different locale and can't test it directly, but if all your other delimiters are commas, shouldn't the one in sMyString be as well?

sMyString = Chr(34) & Chr(34) & Chr(44) & Chr(34) & Chr(34)

where 44 is the code for a comma.

On the other hand, if they are all supposed to be semicolons, then the formula should have been

Range("C6:C25").Formula = "=@IF(ISNA(MATCH($B6;StepList;0));"""";IF(@INDEX(StepIdTable;MATCH($B6;StepList;0);COLUMN())=" & sMyString & ";INDEX(StepIdTable;MATCH($B6;StepList;0);COLUMN())))"

But when using Formula, you have to enter the formula string using commas as delimiters regardless of locale. However in your case, if you want to standardise on semicolons both in VBA and on your sheet, you could try FormulaLocal:

    Range("C6:C25").FormulaLocal = "=@IF(ISNA(MATCH($B6;StepList;0));"""";IF(@INDEX(StepIdTable;MATCH($B6;StepList;0);COLUMN())=" & sMyString & ";INDEX(StepIdTable;MATCH($B6;StepList;0);COLUMN())))"

see this reference

BTW I find it helpful to put the whole formula string into a debug.print statement to see how it looks.

Thanks tom! Well I need to insert a semicolon since the data im using includes a semi colon. Somehow the FormulaLocal worked like a charm :) Thanks for your help — Engin007, Aug 10 '21 at 13:10

Inserting semicolon between two quotation marks in VBA

2 Answers2