PDA

View Full Version : Solved: concatenate cells in IF statements



lienlee
07-07-2011, 12:48 PM
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.


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

Bob Phillips
07-07-2011, 03:18 PM
Cells(i, 1).Value = Cells(1, 2).Text & ","

'...

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

'...

'etc

lienlee
07-07-2011, 04:25 PM
Cells(i, 1).Value = Cells(1, 2).Text & ","

'...

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

'...

'etc
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?

Paul_Hossler
07-07-2011, 06:42 PM
Actually I think it should be



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

'etc


Probably at the end a


Cells(i, 1).Value = left(Cells(i, 1).Value, Len(Cells(i, 1).Value)-1)


to get rid of the last comma

Paul

lienlee
07-08-2011, 05:10 AM
Actually I think it should be



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

'etc


Probably at the end a


Cells(i, 1).Value = left(Cells(i, 1).Value, Len(Cells(i, 1).Value)-1)


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.

CatDaddy
07-08-2011, 12:26 PM
Cells(i, 1).Value = Cells(i, 1).Value & Cells(1, 2).Text & " "