0

Following is the vba code, (just inserted formula in vba)

Sub xyz()
Range("H2").Formula = "=TEXTJOIN(";",TRUE,FILTERXML("<t><s>"& SUBSTITUTE(SUBSTITUTE(A2,"/","</s><s>"),";","</s><s>")&"</s></t>","//s[not(contains(., '.'))]"))"
Range("H2", "A" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

But i get this error: highlighting ";" next to textjoin.

enter image description here

Please help to correct this code.

Ken White
  • 123,280
  • 14
  • 225
  • 444
Lote_marsk_chozey
  • 19
  • 6
  • May i know which comma you are talking about? – Lote_marsk_chozey Jul 11 '22 at 03:42
  • Get it all working on one line first - it's much easier. – braX Jul 11 '22 at 03:43
  • I have made it in one line, but still it gives the same error – Lote_marsk_chozey Jul 11 '22 at 03:47
  • 2
    You need to escape your double quotes inside your formula – dbmitch Jul 11 '22 at 03:53
  • You can't embed double quotes inside double quotes. Doing so means that your code is seen as `"=TEXTJOIN(";`, which is clearly incomplete and leaves a hanging semicolon. You need to escape the embedded double quotes. – Ken White Jul 11 '22 at 03:58
  • does that mean I can use single quote instead of double quotes? – Lote_marsk_chozey Jul 11 '22 at 04:09
  • Double quotes in strings should be doubled up `a=“I said “”Hello”” to my friend”` – Tim Williams Jul 11 '22 at 04:51
  • `Range("H2").Formula = "=TEXTJOIN("";"",TRUE, FILTERXML(""""&SUBSTITUTE(SUBSTITUTE(A2,""/"",""""),"";"","""")&"""",""//s[not(contains(., '.'))]""))"` I have corrected this, it has no errors ,but does not provide the expected output – Lote_marsk_chozey Jul 11 '22 at 04:54
  • 2
    @Lote_marsk_chozey re _but does not provide the expected output_. That would be a new question. This one has been answered (escape your quotes) – chris neilsen Jul 11 '22 at 05:00
  • Problem could be due to your explicit referencing finding the last used row. Who knows. I agree; question is answered. Open a new question with more detailed information if you wondering about the unexpected output. – JvdV Jul 11 '22 at 06:45

0 Answers0