Type mismatch in VBA FormulaR1C1 that removes values past "-" sign

Question

I have a simple macro that moves down to the last filled cell, then moves to the right and is supposed to put the following formula in there: "=LEFT(RC[-2], SEARCH(" - ",RC[-2])-1)", that takes the value from two cells to the left and removes all characters after the "-".

Range("B1").End(xlDown).Select
Selection.Offset(0, 1).Select

Selection.FormulaR1C1 = "=LEFT(RC[-2], SEARCH(" - ",RC[-2])-1)"

The problem is that when I run the code I get Type Mismatch error. The cell that the code goes into is blank before the code runs.

Double the quotes in the string: `"=LEFT(RC[-2], SEARCH("" - "",RC[-2])-1)"` — Rory, Mar 17 '22 at 12:21

score 0 · Accepted Answer · answered Mar 17 '22 at 12:55

VBA uses double quotes (") to demark string literals. For example:

const saying = "you look nice today"

The problem arises when you want to have a double quote inside a string literal as follows:

const saying = "you look "nice" today"

In this case, the VBA interpreter understants the value of the saying constant to be "you look " as if you had written:

const saying = "you look "

But then it gets to the word 'nice' and it is confused because it is not syntactically valid. There are a couple of ways to get a double quote into a string literal in VBA. The first way is to use string concatenation and the chr() function. The chr() function returns a character based on it numeric value from the ASCII table. Every character has a numeric value that underlies it. "A" is number 65, "B" is number 66 and so on, The ASCII number the double quote character is 34. So a statement of:

debug.print chr(65) & chr(66) & chr(34)

would display the following

AB"

So you could wirte:

debug.print "you look " & chr(34) & "nice" & chr(34) & " today"

and it would display:

you look "nice" today

But I think you will agree with me that this approach seems very onerous, so there is a shortcut when trying to put a double quote into a string literal. It seems a bit strange, but you need to put two double quotes instead of one. So the following code:

debug.print "you look ""nice"" today"

will print:

you look "nice" today

I hope this explanation helps.

Type mismatch in VBA FormulaR1C1 that removes values past "-" sign

1 Answers1