Entering a formula into a cell generates a user-defined or object-oriented error

Question

I searched the network for a solution and rewrote VBA many times. I ended up manually filling in the string (in bits, because I'm always mistaken in quotation marks):

PKHNTString = "=SUBSTITUTE(UPPER(AF:AF&AD:AD&AE:AE);" 
PKHNTString = PKHNTString + """ "";""" 
PKHNTString = PKHNTString + """)"

The result is a line containing: = SUBSTITUTE (TOP (AF: AF & AD: AD & AE: AE); ";" ") as can be found in local options:: PKHNTString:" = SUBSTITUTE (UPPER (AF: AF & AD: AD & AE: AE); ""; "") ": String

If I insert this exact value into the cell, it works (removes spaces and converts everything to uppercase), however, when I put this formula into the cell through VBA, like this:

NWS.Cells(j, 48).Formula = PKHNTString

I get the error message: Runtime Error '1004' User Defined or Object Error.

Any help would be appreciated!

TIA, Willem

Answer 1

You specified a semicolon (;) as the argument separator in the formula. Through VBA, you always need to use a comma, regardless of the current regional settings. However, .FormulaLocal needs a localized version of the list separator.

, .Formula, .FormulaLocal - ( API calle, , .Formula).

.FormulaLocal, ";" "," ().

Answer 2

PKHNTString (). VBA =SUBSTITUTE(UPPER(AF:AF&AD:AD&AE:AE); , ;""). PKHNTString :

"=SUBSTITUTE(UPPER(AF:AF&AD:AD&AE:AE);"" "";"""")"

" VBA...