0

I have formulas that seem to work fine when used in a worksheet, but when I try to use a code to enter the formulas, it was kicking back a compile error. I understand that adding double quotes will fix most of the issues. I am now running into issues with the # and @ symbol. The @ symbol kicks back an "invalid character" error now.

Any help would be much appreciated!

Sub enterformulas()

Worksheets("Cleaning").Range("B2").Formula = "=TRIM(A2)"
Worksheets("Cleaning").Range("C2").Formula = "=IFERROR(LEFT(B2,SEARCH("" "", B2)-1),"")"
Worksheets("Cleaning").Range("D2").Formula = "=IFERROR(TRIM(REPLACE(REPLACE(A2,SEARCH(E2,A2),LEN(E2),""),1,LEN(C2),"")),"")"
Worksheets("Cleaning").Range("E2").Formula = "=IFERROR(RIGHT(B2, LEN(B2) - SEARCH(""#"", SUBSTITUTE(B2,"" "", ""#"", LEN(B2) - LEN(SUBSTITUTE(B2, "" "", ""))))),B2)"
Worksheets("Cleaning").Range("G2").Formula = "=TRIM(F2)"
Worksheets("Cleaning").Range("H2").Formula = "=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(G2,FIND("" "",G2&"" "",FIND("@",G2))-1),"" "",REPT("" "",LEN(G2))),LEN(G2))),"[",""),"]",""),"")"

End Sub

Example Sheet: Highlighted cells include formulas

kcappy
  • 23
  • 5
  • 4
    you have to escape your double quotes in the string variable. See this post https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba – Kevin Jun 09 '21 at 12:26
  • @Kevin Thank you for that. I think that solves the issue for C2 and H2 if I use double quotes "" ""; however, in E2 the "#" is still kicking back a compile error. – kcappy Jun 09 '21 at 12:53
  • Still a bit new here. Not sure why this is closed when only part of the issue was solved. – kcappy Jun 09 '21 at 13:15
  • For E2, you still need to double up some: `""))))),B2)"` should be `""""))))),B2)"` – BigBen Jun 09 '21 at 14:24
  • The question was most likely closed as you said you know how to fix most of the compile errors but you failed to show the code where you had resolved these issues, even after you had edited the question. As for "invalid character" error which I assume is for H2 this should work: `Range("H2").Formula = "=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(G2,FIND("" "",G2&"" "",FIND(""@"",G2))-1),"" "",REPT("" "",LEN(G2))),LEN(G2))),""["",""""),""]"",""""),"""")"` I suggest recording a macro as you enter a complex formula as this will give you the expected results. – 5202456 Jun 09 '21 at 14:25
  • Tip: use `Debug.Print` and inspect the result in the Immediate Window. It should be able to be copied and pasted into the cell... if there's an issue, Excel will complain and hopefully highlight it. – BigBen Jun 09 '21 at 14:30
  • 1
    @5202456 I tried recording a macro while entering the formulas and was able to get what I needed! Thank you so much. Thank you also for helping me learn more about how to work within StackOverflow better. – kcappy Jun 09 '21 at 14:52
  • @BigBen Thank you for that tip too. – kcappy Jun 09 '21 at 14:52

0 Answers0