0

I'm trying to add a formula to a cell that combines text from a cell in the Excel worksheet and different strings that are created in my VBA code. The formula that I need is rather long so I broke it down to the most simple version to understand what I'm doing wrong. I looked for similar examples online but I just can't get it right.

When the string comprises of only numbers or when I replace the string by an integer, the formula works, but when I use a "real" string, it puts @ in front of the string. When I use .formula2, the @ disappears but I still don't get what I need. Can someone point me in the right direction?

Dim xCode As String
xCode = "123"
Range("S4").Formula = "=R4 & " & xCode

enter image description here

Dim xCode As String
xCode = "ABC"
Range("S4").Formula = "=R4 & " & xCode

enter image description here

Dim xCode As String
xCode = "ABC"
Range("S4").Formula2 = "=R4 & " & xCode

enter image description here

10a
  • 429
  • 1
  • 5
  • 21
  • 1
    1. Please never show us code in pictures! It is easier and necessary to post them as editable test. 2. You should escape double quotes. If you want writhing `=R4&"ABC"`, you should use `xCode = """ABAC"""`. You should test it in VBE (`Debug.print "R4&""ABC"""` will return wha tyou need)... – FaneDuru Mar 06 '23 at 09:55
  • I'll put the code directly in my question but since the code is so short, I thought it was not an issue. – 10a Mar 06 '23 at 09:57
  • What to write it, looking to your question **instead of copying** it? My piece of advice was to help you receiving comments, answers... We are not just bored waiting to write looking to something. You must try staying in our shoes and looking to your question from that direction... – FaneDuru Mar 06 '23 at 09:59
  • I'm not sure that I understand what you're saying. I split the pictures and the code, I hope it is more readable this way. – 10a Mar 06 '23 at 10:02
  • 1
    Did you read the second part of my first comment? – FaneDuru Mar 06 '23 at 10:04
  • Thank you for answering so quickly! However, the first version of your answer wasn't nearly as friendly and explanatory as the edited version. I understand that you must come across many of these (frustrating) mistakes but "advising" someone in a friendly way won't hurt a soul. – 10a Mar 06 '23 at 10:31
  • I cannot understand if that part of the comment helped... Did you know now how to write formulas in VBA and more important, how to test that what you tried is OK? – FaneDuru Mar 06 '23 at 10:39
  • A combination of your comment and the answer to the similar question helped solving the issue. Thank you! – 10a Mar 06 '23 at 11:00

0 Answers0