contatenating two cells using VBA (2nd question)


B

billabong

My original question
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.

The answer that was kindly provided for me:
****************************************
One way:


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
*******************************************

Unfortunately I forgot to mention that I needed the concatenated
result reflected in Cell A3. i.e., Cell A3 = HSBC LN equity. What
would I have to add to this code to do that?

Very sorry for not thinking out the question properly the first time.

Manuel
 
Ad

Advertisements

R

Robin Hammond

It's just a case of adding a space in the string

Case "United Kingdom"
.Value = Range("A1").Value & " " & Range("A2").Value
Case "France"

Hope you are enjoying your Bloomberg!

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in


My original question
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.

The answer that was kindly provided for me:
****************************************
One way:


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
*******************************************

Unfortunately I forgot to mention that I needed the concatenated
result reflected in Cell A3. i.e., Cell A3 = HSBC LN equity. What
would I have to add to this code to do that?

Very sorry for not thinking out the question properly the first time.

Manuel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top