Consulting

Results 1 to 6 of 6

Thread: Solved: concatenate cells in IF statements

  1. #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. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

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

    '...

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

    '...

    'etc[/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Quote 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. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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. #5
    Quote 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. #6
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    [VBA]Cells(i, 1).Value = Cells(i, 1).Value & Cells(1, 2).Text & " "[/VBA]
    ------------------------------------------------
    Happy Coding my friends

Posting Permissions

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