Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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.
    VB:
    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:
    VB:
    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.)
    VB:
    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 06: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 05: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
    VB:
    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.
    VB:
    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 01: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.
    VB:
    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 02: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, 15:20
  2. Replies: 5
    Last Post: December 11th, 2007, 03:38
  3. Concatenate Cell Values & Display On Userform
    By John C H in forum EXCEL HELP
    Replies: 7
    Last Post: September 24th, 2007, 16:48
  4. Concatenate Values Per Conditions
    By Michael Avidan in forum EXCEL HELP
    Replies: 11
    Last Post: August 23rd, 2007, 16:56
  5. Concatenate cell calues based on other cell values
    By braveheart in forum EXCEL HELP
    Replies: 2
    Last Post: April 1st, 2005, 20: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