Hi,
Routine belowoverwrites 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.Code:Range("M2").Value = i.Value
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.
Two options:
The first will put it all in M2 separated by dashes. The second will put the data in M2, M3, M4, etc...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
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
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.
Bookmarks