PDA

View Full Version : Compare and Print with VBA



Shaolin
09-14-2007, 06:20 AM
I'm trying to compare the value of the cells in row A with row B. So, for instance, if the values start in Row A, Column 6, then I want it to search in row B, column 6, and row B, column 7.

Let's say cell A6 reads "HELLO NY" and cells B6, B20 and B54 have the same value, then in cell C6, I want it to print out the cell locations "B6, B20, B54"
Also, the same can be said about cell A7, A8 and so on. If cell A7 reads "Bedtime" and cells B7 and B11 also read "Bedtime", then in cell C7 should print out the locations "B7, B11"

Bob Phillips
09-14-2007, 07:05 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sTemp As String
Dim sFirst As String

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 6 To iLastRow
Set cell = Nothing
sTemp = ""
If .Cells(i, "A").Value <> "" Then
Set cell = .Columns(2).Find(.Cells(i, "A").Value, LookIn:=xlValues)
If Not cell Is Nothing Then
sFirst = cell.Address
Do
sTemp = sTemp & cell.Address(False, False) & ","
Set cell = .Columns(2).FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> sFirst
End If
.Cells(i, "C").Value = sTemp
End If
Next i

End With

End Sub

Shaolin
09-17-2007, 01:15 PM
WOW. Thanks a bunch!!!