0

I have a below formula which works in excel, in a cell.

="D"&AC2 & REPT(" ",12 - LEN(AC2))&REPT(" ", 20)&T2&REPT(" ", 3 - LEN(T2))&AD2&REPT(" ", 2 - LEN(AD2))&REPT(" ",12)

I tried to translate this to VBA, For letter D and all the whitespaces " " I used escape characters (i thought, they are escape characters) """" """" but I couldn't get it to work.

Here's the formula I tried in VBA

"= """"D""""&AC2 & REPT("""" """" ,12 - LEN(AC2))&REPT("""" """", 20)&T2&REPT("""" """" , 3 - LEN(T2))&AD2&REPT("""" """" , 2 - LEN(AD2))&REPT("""" """" ,12)"

Any help is appreciated. Thanks

Johnny Abreu
  • 375
  • 1
  • 16
Mr.Riply
  • 825
  • 1
  • 12
  • 34
  • 2
    You got a bit quote happy there: `"=""D""&AC2 & REPT("" "",12 - LEN(AC2))&REPT("" "", 20)&T2&REPT("" "", 3 - LEN(T2))&AD2&REPT("" "", 2 - LEN(AD2))&REPT("" "",12)"` – Rory Nov 06 '19 at 11:55
  • Possible duplicate of [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) – Geert Bellekens Nov 06 '19 at 11:56

2 Answers2

1

In VBA you'll need quotation marks around a quotation mark from a formula " -> """

So, a whitespace would be "" "", and a quoted string ""D""

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
1

Fairly simpler method of implementing it in VBA is as below.

Step 1: Copy formula as it is in VBE Window

="D"&AC2 & REPT(" ",12 - LEN(AC2))&REPT(" ", 20)&T2&REPT(" ", 3 - LEN(T2))&AD2&REPT(" ", 2 - LEN(AD2))&REPT(" ",12)

Step 2: Highlight formula text and then for selected text, do FIND and REPLACE

FIND: "

REPLACE:""

=""D""&AC2 & REPT("" "",12 - LEN(AC2))&REPT("" "", 20)&T2&REPT("" "", 3 - LEN(T2))&AD2&REPT("" "", 2 - LEN(AD2))&REPT("" "",12)

Step 3: Wrap the outcome with a pair of quotes.

"=""D""&AC2 & REPT("" "",12 - LEN(AC2))&REPT("" "", 20)&T2&REPT("" "", 3 - LEN(T2))&AD2&REPT("" "", 2 - LEN(AD2))&REPT("" "",12)"

Note: If you are having ";" as argument separator then you need look at FormulaLocal and if you choose to use Formula then ";" shall be replaced by ",".

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27