CODEWITHSUNDEEP
excelconcatenationvba
waterwalk77
waterwalk77

Reputation: 48

VB syntax error for concatenate and evaluate

I am trying to write a VB script that concatenates data together into a url string and then I want it to copy down into all the rows for that column. I've got the fill down code working okay but when I try to add the concatenate I keep getting a syntax error so I'm not sure what I'm doing wrong. I have tried two versions and they both give me syntax errors on the final line of script (right side):

Script Version 1: 

Sub SetSurveyLink()

' SetSurveyLink Macro

Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C3:C" & lngLastRow).Value = EVALUATE("https://domainname.com/survey/?PartName=" & 'Client List'!B1 & "&ClientID="&B2)
End Sub

Script Version 2: 

Sub SetSurveyLink()

' SetSurveyLink Macro

Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C3:C" & lngLastRow).Value = CONCATENATE("https://domainname.com/survey/?PartName=",'Client List'!B1,"&ClientID=",B2)

End Sub

The 'concatenate' string gives me the correct value when used in a cell (i.e. not as part of a script) but I just can't get it to work in the script. See anything that I'm missing in my syntax?

THANK YOU!

Upvotes: 0

Views: 263

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Any double-quote marks within a string need to be escaped to be double double-quote marks so, if

"https://domainname.com/survey/?PartName=" & 'Client List'!B1 & "&ClientID="&B2

worked within Excel, it becomes

""https://domainname.com/survey/?PartName="" & 'Client List'!B1 & ""&ClientID=""&B2

and then you need to enclose that within double-quote marks to make it a string literal within VBA, i.e.

"""https://domainname.com/survey/?PartName="" & 'Client List'!B1 & ""&ClientID=""&B2"

Upvotes: 1

Related Questions