# Thread: Solved: concatenate cells in IF statements

1. ## Solved: concatenate cells in IF statements

Hi Guys, I have the following code in red that is suppose to be printed on the Cells (i,1) however, im having trouble concatenating all the values on one cell and separating each with the comma. Each of these value should be printed out when it reaches the else statement.

Note: you can ignore the boolean stuff.

[vba]
Sub Match()
Dim i As Long
Dim FirstMatch As Boolean
Dim SecondMatch As Boolean
Dim ThirdMatch As Boolean
Dim FourthMatch As Boolean
Dim FifthMatch As Boolean
Dim SixthMatch As Boolean
Dim Serial As String
Dim HostName As String
Dim Environment As String
Dim Status As String
Dim Manufacturer As String
Dim Model As String
Dim Prnt As String

For i = 2 To 1414
If Cells(i, 2).Text = Cells(i, 3).Text Then 'Cells (row,column)'B = C
FirstMatch = True

Else
FirstMatch = False
Serial = Cells(1, 2).Text 'If ER between B=C print the Header Name in cell(1,2) to Cells(i,1)
End If

If Cells(i, 4).Text = Cells(i, 5).Text Then 'Cells (row,column)'D = E
SecondMatch = True
Else
SecondMatch = False
HostName = Cells(1, 4).Text
End If

If Cells(i, 8).Text = Cells(i, 9).Text Then 'Cells (row,column)'H = I
ThirdMatch = True
Else
ThirdMatch = False
Environment = Cells(1, 8).Text
End If

If Cells(i, 13).Text = Cells(i, 14).Text Then 'Cells (row,column)'M = N
FourthMatch = True
Else
FourthMatch = False
Status = Cells(1, 13).Text
End If

If Cells(i, 19).Text = Cells(i, 20).Text Then 'Cells (row,column)'S = T
FifthMatch = True
Else
FifthMatch = False
Manufacturer = Cells(1, 20).Text
End If

If Cells(i, 21).Text = Cells(i, 22).Text Then 'Cells (row,column)'U = V
SixthMatch = True
Else
SixthMatch = False
Model = Cells(1, 22).Text
End If
Next i
End Sub

[/vba]

2. [vba]

Cells(i, 1).Value = Cells(1, 2).Text & ","

'...

Cells(i, 1).Value = Cells(1, 3).Text & ","

'...

'etc[/vba]

3. Originally Posted by xld
[vba]

Cells(i, 1).Value = Cells(1, 2).Text & ","

'...

Cells(i, 1).Value = Cells(1, 3).Text & ","

'...

'etc[/vba]
Where would i put it? If i put in the Else statement. The next IF statement for the value will overwrite that current cell, wouldnt it?

4. Actually I think it should be

[vba]

Serial = Cells(1, 2).Text
Cells(i, 1).Value = Cells(i, 1).Value & Cells(1, 2).Text & ","

'etc
[/vba]

Probably at the end a

[vba]
Cells(i, 1).Value = left(Cells(i, 1).Value, Len(Cells(i, 1).Value)-1)
[/vba]

to get rid of the last comma

Paul

5. Originally Posted by Paul_Hossler
Actually I think it should be

[vba]

Serial = Cells(1, 2).Text
Cells(i, 1).Value = Cells(i, 1).Value & Cells(1, 2).Text & ","

'etc
[/vba]

Probably at the end a

[vba]
Cells(i, 1).Value = left(Cells(i, 1).Value, Len(Cells(i, 1).Value)-1)
[/vba]

to get rid of the last comma

Paul
Thanks! This was exactly what I was looking for. I was looking for how to get rid of the commas.

@xld. Thanks for your help also.

6. [VBA]Cells(i, 1).Value = Cells(i, 1).Value & Cells(1, 2).Text & " "[/VBA]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•