Results 1 to 2 of 2

Thread: How to write matched values in one cell wihout being overwritten?

  1. #1

    How to write matched values in one cell wihout being overwritten?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    Routine below
    Code:
    Range("M2").Value = i.Value
    overwrites the values on top of each other and only the last value from "A1:F17" is shown in "M2". How do I modify it so it writes all the values in "M2" separated by a dash or space? or maybe show it in column M.
    Thank you again.

    Code:
    Sub Compare2A()     
    Dim LstRw As Long, c As Range, i As Range     
    Application.ScreenUpdating = False     
    LstRw=Cells.Find(What:="*",SearchOrder:=xlRows,                                 SearchDirection:=xlPrevious,                                                     
             LookIn:=xlValues).Row     
              For Each c In Range("A1:F17")         
              For Each i In Range("K1:K" & LstRw)             
          If c.Value = i.Value Then                 
                c.Interior.ColorIndex = 6
                    Range("M2").Value = i.Value             
    End If         
    Next i     
    Next c     
    Application.ScreenUpdating = True 
    End Sub

    Thank you for your help.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,231
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Two options:
    Code:
    Sub Compare2A()
        Dim LstRw As Long, c As Range, i As Range
        Dim sValue As String
        Application.ScreenUpdating = False
        LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        
        For Each c In Range("A1:F17")
            For Each i In Range("K1:K" & LstRw)
                If c.Value = i.Value Then
                    c.Interior.ColorIndex = 6
                    sValue = sValue & i.Value & "-"
                End If
            Next i
        Next c
        sValue = Left(sValue, Len(sValue) - 1)
        Range("M2").Value = sValue
        Application.ScreenUpdating = True
    End Sub
    Sub Compare2B()
        Dim LstRw As Long, c As Range, i As Range
        Dim lrow As Row
        Application.ScreenUpdating = False
        lrow = 2
        LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        
        
        For Each c In Range("A1:F17")
            For Each i In Range("K1:K" & LstRw)
                If c.Value = i.Value Then
                    c.Interior.ColorIndex = 6
                    Range("M" & lrow).Value = i.Value
                    lrow = lrow + 1
                End If
            Next i
        Next c
        Application.ScreenUpdating = True
    End Sub
    The first will put it all in M2 separated by dashes. The second will put the data in M2, M3, M4, etc...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

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