how do you concatenate two variables in VBA

[billabong]

Need to concatenate the contents of two cells together, using VBA.
for example:

Cell A1 = HSBC
Cell A2 = LN equity

so that cell A3 would show exactly as follows: HSBC LN equity

I can do this on excel spread sheet as: Cell A3 = A1&A2
(indenting contents of A2 one space) however I need to do this in VBA
format.

The beginning of my program is as follows but I get an error message
in line 2 of program.

If [C1] = "United Kingdom" Then
[A1] & [A2]
.. . . . .
ElseIf [C1] = "France" Then
.. . . . .
Else [C1] = ""
EndIf


need to do this for 15 other countries.

Your help in this would be very much appreciated.

Manuel

 

[J.E. McGimpsey]

One way:

If Range("C1").Value = "United Kingdom" Then
Range("C1").Value = Range("A1").Value & Range("A2").Value
ElseIf Range("C1").Value = "France" Then
...
Else
Range("C1").ClearContents
End If
or, a bit more efficiently:

With Range("C1")
If .Value = "United Kingdom" Then
.Value = Range("A1").Value & Range("A2").Value
ElseIf .Value = "France" Then
...
Else
.ClearContents
End If
End With

For 15 countries, though, I'd use the Select Case structure:

With Range("C1")
Select Case .Value
Case "United Kingdom"
.Value = Range("A1").Value & Range("A2").Value
Case "France"
...
Case ...
...
Case Else
.ClearContents
End Select
End With


Note that the [] notation, while it saves typing, is in most cases
much slower than using the range references.