Write properly a formula in Excel VBA

Question

I already asked a question like this before, but seems that the answer given is not working in this case

this is my code:

Worksheets("Sheet").Range(myRange).formula = "=CONCATENATE(CODICI!" & stringCodiceCella & ";" & stringDesignazioneCella & ")"

Note that stringCodiceCella is a String, and stringDesignazioneCella is also a String.

I can't see the error.

Should "CODICI!" be a string? If yes, you should add double quotes around it like "" CODICI!"" — Boketto, Feb 06 '20 at 13:06
@Boketto not it's the name of the Sheet, sorry I didn't write it properly, btw the solution provided by THE BIG BEN is working like a charm. As always he is so fast :) — ChangeWorld, Feb 06 '20 at 13:08

shrivallabha.redij · Accepted Answer · 2020-02-06T15:18:24.243

1

You can use "," in place of ";" as suggested by @BigBen

Worksheets("Sheet").Range(myRange).Formula = "=CONCATENATE(CODICI!" & stringCodiceCella & "," & stringDesignazioneCella & ")"

Or use FormulaLocal

Worksheets("Sheet").Range(myRange).FormulaLocal = "=CONCATENATE(CODICI!" & stringCodiceCella & ";" & stringDesignazioneCella & ")"

Edit: See below code.

Worksheets("Sheet").Range(myRange).Formula = "=CONCATENATE(CODICI!" & stringCodiceCella & ",""-""," & stringDesignazioneCella & ")"

Worksheets("Sheet").Range(myRange).FormulaLocal = "=CONCATENATE(CODICI!" & stringCodiceCella & ";""-"";" & stringDesignazioneCella & ")"

edited Feb 06 '20 at 15:18

answered Feb 06 '20 at 13:07

I'm going to accept the answer but I just want to ask you. If I would like to put a simple " - " in the middle of the two strings so I can have a better view of it, where do i put it? – ChangeWorld Feb 06 '20 at 13:10
@ChangeWorld If you have Office365 or Office2019, then you can swap from `CONCATENATE` to `TEXTJOIN`, which supports delimiters suck as "-" – Chronocidal Feb 06 '20 at 13:23
@ChangeWorld See edit section formula which shall help. – shrivallabha.redij Feb 06 '20 at 13:31
@Chronocidal unfortunately I have office 2016 for now. – ChangeWorld Feb 06 '20 at 13:46
@shrivallabha.redij It's not working, it gives me incorrect type. Why did didn't you use comma instead of semicolon? – ChangeWorld Feb 06 '20 at 13:47
@ChangeWorld refer both formulas. Both should work! – shrivallabha.redij Feb 06 '20 at 15:18

1 Answers1