VBA Expected End of Statement error

[SR1]

Hi,

I am trying to create a userform that looks up an address on Google Maps, or directions between two addresses. I got the above error message when creating this code below:

Private Sub CommandButton1_Click()
Dim r As Integer, r2 As Integer, URL As String
Application.ScreenUpdating = False
TextBox2.Value = ""
On Error GoTo InvalidAddress:
Range("m:m").Select
Selection.Find(What:=TextBox1.Value, LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False).Activate
r = ActiveCell.Row
If TextBox2.Value = "" Then
URL = "https://www.google.co.uk/maps/place/" & Cells(r, 12).Value
ActiveWorkbook.FollowHyperlink Address:=URL, NewWindow:=True
Else
Selection.Find(What:=TextBox2.Value, LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False).Activate
r2 = ActiveCell.Row
URL = "https://www.google.co.uk/maps/dir/" & Cells(r, 12).Value&"/"&cells(r2,12).value
ActiveWorkbook.FollowHyperlink Address:=URL, NewWindow:=True
End If
Exit Sub
InvalidAddress:
Range("a1").Select
MsgBox "We can't find this address, please check your spelling or copy and paste from the Combined Address column", vbExclamation, "Error"
TextBox2.Value = ""

End Sub

The userform at the moment looks like this:

The macro should look for the address you typed from an already existing list (in column M) and open a hyperlink based on the postcode next to that address if a match is found (in column L). The "/" is highlighted (after Cells(r, 12).Value&) when the error comes up, I can't think what is wrong with the code though.

Please help if you can!

 

[Fluff]

It looks like you're missing some spaces, try

    URL = "https://www.google.co.uk/maps/dir/" & Cells(r, 12).Value & "/" & Cells(r2, 12).Value

 

[SR1]

The spaces are missing because Excel won't accept that line of code, otherwise they would be inserted automatically.

The code still doesn't work if I insert the spaces myself.

 

[Fluff]

otherwise they would be inserted automatically.

I wouldn't count on that. Spaces aren't always added automatically.
When I placed your code in a module, that line was highlighted red, once the spaces were added, the text became black & the compiler showed no further errors.
As I have no idea what your textbox values are, or what your data looks like, there's not much more I can do to help.
Can you upload your file to dropbox, or similar & post a link here?

 

[JackDanIce]

Similar to @Fluff's comments, adjusting for the spaces prevents the line being highlighted in red. Checking your code again, I can't see anything inherently wrong aside from those spaces:

Private Sub CommandButton2_Click()

    Dim x       As Long
    Dim y       As Long
    Dim LR      As Long
    Dim strURL  As String
    
    Const GOOGLE_MAPS   As String = "https://www.google.co.uk/maps/"
    
    Application.ScreenUpdating = False
    
    TextBox2.Value = ""
    
    On Error GoTo InvalidAddress:
            
    LR = Cells(Rows.count, 13).End(xlUp).row
    x = Cells(1, 13).Resize(LR).find(what:=TextBox1.Value, LookIn:=xlFormulas, lookat:=xlPart).row
    
    If Len(TextBox2.Value) = 0 Then
        strURL = GOOGLE_MAPS & "place/" & Cells(x, 12).Value
    Else
        y = Cells(1, 13).Resize(LR).find(what:=TextBox2.Value, LookIn:=xlFormulas, lookat:=xlPart).row
        strURL = GOOGLE_MAPS & "dir/" & Cells(x, 12).Value & "/" & Cells(y, 12).Value
    End If
    
    Application.ScreenUpdating = True
    ActiveWorkbook.followhyyperlink Address:=strURL, NewWindow:=True
    Exit Sub
    
InvalidAddress:
    With Application
        .Goto Cells(1, 1), True
        .ScreenUpdating = True
    End With
    MsgBox Replace("We cannot find this address.@1@1Please check your input", "@1", vbCrLf), vbExclamation, "Input Error"


End Sub

 

[Fluff]

@JackDanIce
One minor typo, you've an extra y in hyperlink, should be

ActiveWorkbook.FollowHyperlink Address:=strURL, NewWindow:=True

 

[SR1]

Thanks, the line in my original code is now accepted with all the right spaces inserted. Must have been the "&" immediately following "Value" that threw it, but it's strange, I've never encountered that problem before.

And thanks Jack for taking the trouble to decipher and reconstruct my code, I'll try that if mine still doesn't work!

 

[JackDanIce]

@Fluff Thanks mate, good spot!

 

[Rick Rothstein]

Thanks, the line in my original code is now accepted with all the right spaces inserted. Must have been the "&" immediately following "Value" that threw it, but it's strange, I've never encountered that problem before.

You always need to provide a space between a variable name and the ampersand (&) following it for a line of code involving concatenation. The reason has to do with VB being backward compatible, syntax-wise, with older VB's and even the DOS versions of BASIC that preceded it. In the "old days" (and in today's VBA if you want), you could specify a variable's data type by using suffix characters attached to the end of the variable's name. The ampersand character happens to be one of those suffix characters. Placing an ampersand at the end of a variable's name makes the variable a Long data type... the others being percent sign (%) for Integer, exclamation mark (!) for Single, pound sign (#) for Double, dollar sign ($) for String and, although the data type is "newer", the at sign (@) for Currency. Anyway, VB's problem comes from its considering the second ampersand in this string (no space between it and the variable name in front of it)...

UserName = "Rick"
MsgBox "Thank You "&UserName&" for taking part in this survey"

to be the Long data type suffix character meaning there is no concatenation symbol for the variable and the text following it. I know, it should be able to figure out that UserName contains a String value so that it can't be a Long, but it can't. Anyway, you can see this in action by putting in the ampersand that VB is missing.

UserName = "Rick"
MsgBox "Thank You "&UserName&&" for taking part in this survey"

The above string of text will not raise an error when the above MsgBox statement is typed in; instead, the automatic spacing that VB does will take place around the second ampersand once the statement is committed. Now, of course, you will get a type-declaration error when you attempt to actually run this code because UserName is (presumably) Dim'med as a String and the concatenation of a declared Long variable and a String is improper.

Anyway, that is the 'why' of the error you mentioned.

 

[Fluff]

@Fluff Thanks mate, good spot!

Happened to notice that it hadn't been capitalised, so had a better look.
@Rick Rothstein
Thanks for the explanation. I've always known that the spacings aren't always inserted, but never understood why.