Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: concatenate two cell values with VBA

  1. #1
    Join Date
    9th September 2003
    Location
    bavaria, germany
    Posts
    101

    concatenate two cell values with VBA

    Hi,
    how can I concatenate the values of two (or eventually more, i.e. a Range) of Excel cells using VBA, also using a criteria, described below?
    The background criteria for how many cell values in a row should be concatenated and exported in a new column, is, if a cell in a column has a background colouring.

    Hope that I explained the situation convenient enough, any questions, please ask.

    Cheers,
    Juergen

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: concatenate two cell values with VBA

    Here's one example. Not sure if it's anything like waht you are after.
    Code:
    Sub ColourName()
    Dim c As Range, ColourName As String, ConCat As String
    For Each c In Range("A1:A3")
        Select Case c.Interior.ColorIndex
            Case 4:     ColourName = "Light Green"
            Case 5:     ColourName = "Blue"
            Case 7:     ColourName = "Pink"
            Case 10:    ColourName = "Green"
            Case 40:    ColourName = "Tan"
        End Select
        c.Offset(0, 1) = ColourName
        If c.Interior.ColorIndex > 0 Then Range("D2") = Range("D2") & ColourName
    Next c
    End Sub
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  3. #3
    Join Date
    9th September 2003
    Location
    bavaria, germany
    Posts
    101

    Re: concatenate two cell values with VBA

    Hi,
    I enclosed an example Workbook.
    There, I have several rows, each with a header.

    In column M (comment type), I want to extract the background colour name, if a cell in a row is coloured.
    A UDF which can display the background colour name of a cell can be found within the following thread:
    Code:
    http://www.ozgrid.com/VBA/ReturnCellColor.htm
    In column N (comment text), I want to display all cell values, which have been coloured with a background. Sometimes there are more cells in a row, then all the coloured cell values should be concatedated.
    E.g. for row 15, there should be the concatenated values of all yellow cells standing in the column N, best separated with some kind of character:
    'nu_DMP1_E0048_Scont.oaT_oDMP1_gamma isoform2_3241_sheep_ZDY_15-May-98_Contig'

    The value 'yellow' should be displayed in column M, for row 15.

    Another example, row 10: there should be displayed 'yellow' in column M (comment type), and '1058' in column N (comment text).


    cheers
    Juergen
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: concatenate two cell values with VBA

    Try the following (in the attached). It also puts the colorindex number in column L since your table did not include all of the colors used. (The code assumes that only one background color is used in a given row. (Note I had to remove a few of your data rows to keep the file small enough to post.)
    Code:
    Sub Fill_L_M_N()
        Dim c As Range, col As Integer, ColourName As String, ConCat As String, ColorNum As Integer
        For Each c In Range(Cells(5, 1), Cells(Rows.Count, 1).End(xlUp))
            ConCat = ""
            ColourName = ""
            For col = 0 To 11
                If c.Offset(0, col).Interior.ColorIndex > 0 Then
                    ColorNum = c.Offset(0, col).Interior.ColorIndex
                    Select Case c.Offset(0, col).Interior.ColorIndex
                    Case 4:     ColourName = "Light Green"
                    Case 5:     ColourName = "Blue"
                    Case 6:     ColourName = "Yellow"
                    Case 7:     ColourName = "Pink"
                    Case 8:     ColourName = "Medium Blue"
                    Case 10:    ColourName = "Green"
                    Case 40:    ColourName = "Tan"
                    End Select
                    If Len(c.Offset(0, col)) > 0 Then
                        If Len(ConCat) > 0 Then ConCat = ConCat & "_" & c.Offset(0, col)
                        If Len(ConCat) < 1 Then ConCat = c.Offset(0, col)
                    End If
                End If
            Next col
            c.Offset(0, 12) = ColourName
            c.Offset(0, 13) = ConCat
            c.Offset(0, 11) = ColorNum
        Next c
    Columns("L:N").Columns.AutoFit
    Cells(1, 1).Select
    End Sub
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  5. #5
    Join Date
    9th September 2003
    Location
    bavaria, germany
    Posts
    101

    Re: concatenate two cell values with VBA

    Hi Thomas,
    thanks for your code, it works fine.
    only one background color is used in a given row
    I found that in the next worksheet there are several (maximum three) background colours being used in a given row.

    I modified your code, adding colours.
    Is it possible to modify your code to look for more than one colour in each row, and write two new columns, containing comment type, i.e. the colour name and comment text, i.e. the (concatenated) cell values? So, including the colour index colum, there would be nine newly created columns, for each of the maximum of three possible comments.

    Cheers
    Juergen
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by juergenkemeter; December 19th, 2005 at 07:27. Reason: Adding modified code to example workbook

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: concatenate two cell values with VBA

    Quote Originally Posted by juergenkemeter
    Is it possible to modify your code to look for more than one colour in each row, and write two new columns, containing comment type, i.e. the colour name and comment text, i.e. the (concatenated) cell values? So, including the colour index colum, there would be nine newly created columns, for each of the maximum of three possible comments.
    Sorry for the delay. Wasn't able to spend time online yesterday.
    What you ask should be very doable. I'll try to post some code later today after I get some time to work on it.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  7. #7
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: concatenate two cell values with VBA

    Today is turning out to be busier than I had expected. It may be tomorrow (USAA time) before I can get time to play.

    But I also realized I need to ask one question. Should the comment field differ for each color? That is, before I thought we were concatenating all cells in the regardless of the individual cell's background color. For the multicolor situation did you want just the cells that match in color in a given color's comment field? If not, does the comment field really need to be repeated for each color? If the comment should be color specific, what happens (with regard to the comments) with cells that have no color background?
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  8. #8
    Join Date
    9th September 2003
    Location
    bavaria, germany
    Posts
    101

    Re: concatenate two cell values with VBA

    Hi,
    thx for your help so far.
    Perhaps it is possible to have a variable for checking if a colour has already been used?

    The comment type and comment text columns should differ for each colour.
    Yes, it would be good if only the cells that match in colour would be concatenated in value, and colour name into the two new columns.
    The worksheet has maximum FOUR different background colours in use.

    JŁrgen
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by juergenkemeter; December 20th, 2005 at 06:39. Reason: enclosed example

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: concatenate two cell values with VBA

    See if the attached does what you need. It uses the following code.
    Remove the
    Code:
    Range(xy.Offset(0, 12), xy.Offset(0, 14)).Interior.ColorIndex = CI1
    lines from the "Secect case Cnt" portionof the code if you do not want the output cells color coded to match the source cells.
    Code:
    Sub ParseColors()
        Dim xy As Range, col As Integer, Cnt As Integer
        Dim CI1 As Integer, CI2 As Integer, CI3 As Integer, CI4 As Integer
        Dim ColourName As String
        
        Range(Cells(2, 14), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 25)).Clear  'Clear columns N:Y of previous entries
        For Each xy In Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp)) 'select data origin rows
            Cnt = 1
            CI1 = 0
            CI2 = 0
            CI3 = 0
            CI4 = 0
            For col = 0 To 11 'number of data origin rows, here: cols B to M (offsets from column 2 of 0 to 11)
                If xy.Offset(0, col).Interior.ColorIndex > 0 Then
                    Select Case xy.Offset(0, col).Interior.ColorIndex
                        Case CI1
                            xy.Offset(0, 14) = xy.Offset(0, 14) & "_" & xy.Offset(0, col)
                        Case CI2
                            xy.Offset(0, 17) = xy.Offset(0, 17) & "_" & xy.Offset(0, col)
                        Case CI3
                            xy.Offset(0, 20) = xy.Offset(0, 20) & "_" & xy.Offset(0, col)
                        Case CI4
                            xy.Offset(0, 23) = xy.Offset(0, 23) & "_" & xy.Offset(0, col)
                        Case Else
                            Select Case xy.Offset(0, col).Interior.ColorIndex
                                Case 1:     ColourName = "Black"
                                Case 2:     ColourName = "White"
                                Case 3:     ColourName = "Red"
                                Case 4:     ColourName = "Bright Green"
                                Case 5:     ColourName = "Blue"
                                Case 6:     ColourName = "Yellow"
                                Case 7:     ColourName = "Pink"
                                Case 8:     ColourName = "Turqoise"
                                Case 9:     ColourName = "Dark Red"
                                Case 10:    ColourName = "Green"
                                Case 11:    ColourName = "Dark Blue"
                                Case 12:    ColourName = "Dark Yellow"
                                Case 13:    ColourName = "Violet"
                                Case 14:    ColourName = "Teal"
                                Case 15:    ColourName = "Gray-25%"
                                Case 16:    ColourName = "Gray-50%"
                                Case 33:    ColourName = "Sky Blue"
                                Case 34:    ColourName = "Light Turqoise"
                                Case 35:    ColourName = "Light Green"
                                Case 36:    ColourName = "Light Yellow"
                                Case 37:    ColourName = "Pale Blue"
                                Case 38:    ColourName = "Rose"
                                Case 39:    ColourName = "Lavendar"
                                Case 40:    ColourName = "Tan"
                                Case 41:    ColourName = "Light Blue"
                                Case 42:    ColourName = "Aqua"
                                Case 42:    ColourName = "Aqua"
                                Case 43:    ColourName = "Lime"
                                Case 44:    ColourName = "Gold"
                                Case 45:    ColourName = "Light Orange"
                                Case 46:    ColourName = "Orange"
                                Case 47:    ColourName = "Blue-Gray"
                                Case 48:    ColourName = "Gray-40%"
                                Case 49:    ColourName = "Dark Teal"
                                Case 50:    ColourName = "Sea Green"
                                Case 51:    ColourName = "Dark Green"
                                Case 52:    ColourName = "Olive Green"
                                Case 53:    ColourName = "Brown"
                                Case 54:    ColourName = "Plum"
                                Case 55:    ColourName = "Indigo"
                                Case 55:    ColourName = "Tan"
                                Case 56:    ColourName = "Gray-80%"
                                Case Else:  ColourName = "No name Listed"
                            End Select
                            Select Case Cnt
                                Case 1
                                    CI1 = xy.Offset(0, col).Interior.ColorIndex
                                    xy.Offset(0, 12) = CI1
                                    xy.Offset(0, 13) = ColourName
                                    xy.Offset(0, 14) = xy.Offset(0, col).Value
                                    Range(xy.Offset(0, 12), xy.Offset(0, 14)).Interior.ColorIndex = CI1
                                Case 2
                                    CI2 = xy.Offset(0, col).Interior.ColorIndex
                                    xy.Offset(0, 15) = CI2
                                    xy.Offset(0, 16) = ColourName
                                    xy.Offset(0, 17) = xy.Offset(0, col).Value
                                    Range(xy.Offset(0, 15), xy.Offset(0, 17)).Interior.ColorIndex = CI2
                                Case 3
                                    CI3 = xy.Offset(0, col).Interior.ColorIndex
                                    xy.Offset(0, 18) = CI3
                                    xy.Offset(0, 19) = ColourName
                                    xy.Offset(0, 20) = xy.Offset(0, col).Value
                                    Range(xy.Offset(0, 18), xy.Offset(0, 20)).Interior.ColorIndex = CI3
                                Case 4
                                    CI4 = xy.Offset(0, col).Interior.ColorIndex
                                    xy.Offset(0, 21) = CI4
                                    xy.Offset(0, 22) = ColourName
                                    xy.Offset(0, 23) = xy.Offset(0, col).Value
                                    Range(xy.Offset(0, 21), xy.Offset(0, 23)).Interior.ColorIndex = CI4
                            End Select
                            Cnt = Cnt + 1
                    End Select
                End If
            Next col
        Next xy
           
        Columns("N:Y").Columns.AutoFit 'Format column widths
        Cells(1, 1).Select
    End Sub
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by thomach; December 21st, 2005 at 02:07. Reason: close code tag
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  10. #10
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: concatenate two cell values with VBA

    P.S. I forgot to mention that in the code above I alphabetized your ColorIndex Select Case entries. Noted one duplicate that I deleted, and also that you have two conflicting entries for ColorIndex 55.

    Add a new (blank) sheet to your workbook and run the following macro while on a blank sheet to get your full colorindex map.
    Code:
    Sub ShowColorIndex()
    Dim r As Integer
    If WorksheetFunction.CountA(Range("A1:B64")) > 1 Then
        MsgBox "Change to blank sheet ... Running this macro will overwrite data in column A and/or B."
        Exit Sub
    End If
    For r = 1 To 56
        Cells(r, 1) = r
        Cells(r, 2).Interior.ColorIndex = r
    Next r
    End Sub
    Last edited by thomach; December 21st, 2005 at 03:01. Reason: typo
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Concatenate Cells Based On Other Cell Values
    By soundx in forum EXCEL HELP
    Replies: 1
    Last Post: August 24th, 2008, 16:20
  2. Replies: 5
    Last Post: December 11th, 2007, 04:38
  3. Concatenate Cell Values & Display On Userform
    By John C H in forum EXCEL HELP
    Replies: 7
    Last Post: September 24th, 2007, 17:48
  4. Concatenate Values Per Conditions
    By Michael Avidan in forum EXCEL HELP
    Replies: 11
    Last Post: August 23rd, 2007, 17:56
  5. Concatenate cell calues based on other cell values
    By braveheart in forum EXCEL HELP
    Replies: 2
    Last Post: April 1st, 2005, 21:23

Bookmarks

Posting Permissions

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