0

I am trying to concatenate multiple columns and separate each by a "-". When ever I run the below code, it gives me a type mismatch error. How can I make the types align or at least allow the cells concatenate ?

The concatenate works whenever I remove the hyphen(-) but once I put it or any other value not a column it fails. Please help, I can't seem to clear this error. Can I get help making it concatenate with the (-)? I have tried removing the range. I have tried range.Value instead of range.formula and it still does not work

Range("Table2[New_app]").Formula = "=iferror(vlookup(Input2!$Q2,AppRef,3,false),0)" 'This is a previous line of code that works 

'This is the code that is not working 
Range("Table2[[MIS-MONTH]]").Formula = "=CONCATENATE(Table2[New_Name], " - ", Table2[Month])"

the result should be something like this: 

Column1content-Column2content-Column3content

However I keep getting run time error '13' Type Mismatch

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
chewBuka
  • 1
  • 1
  • 2
    [How do I put double quotes in a string in vba](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba). As is, the code is subtracting one string from another, well trying to. You need to double up the quotes around the hyphen. – BigBen Sep 17 '19 at 18:42

1 Answers1

0

Adding Extra quotes seems to allow this to work based on a quick test I did.

Range("A12").Formula = "=CONCATENATE(A2,"" - "",B2)"

Updated your code to match

Range("Table2[[MIS-MONTH]]").Formula = "=CONCATENATE(Table2[New_Name], "" - "", Table2[Month])"
DougJulian
  • 19
  • 5
  • Yes. Because otherwise it's like trying to do `Range("A12").Formula = "=x" - "x"`. Trying to subtract one string from another will throw a Type Mismatch. – BigBen Sep 17 '19 at 18:56