Compare 2 columns, return a 3rd macro

W

Ward

I'm very new at VBA and am hoping my problem sounds easy to someone!!
1 workbook; 2 tabs---> summary and data.
summary tab has list of claim nums (A) in one col
need to go to data tab and find a matching claim number in col of raw data
(AA)
on data tab, in cell directly to right of the match (AB) i need text data
returned to a new col on summary tab
does this make any sense to anyone?? can anyone please help?



SUMMARY TAB DATA TAB
A B AA AB
1.clm# Color 1.clm# Color
2.000 Yellow 2.222 Red
3.111 Green 3.333 Orange
4.222 Red 4.000 Yellow
5.333 Orange 5.999 Black
6.111 Green
7.909878 Pink
 
J

Joel

Sub MakeSummary()

With Sheets("Summary")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
data = .Range("A" & RowCount)
With Sheets("Data")
Set c = .Columns("AA").Find(what:=data, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If Not c Is Nothing Then
.Range("B" & RowCount) = c.Offset(0, 1)

End If
RowCount = RowCount + 1
Loop
End With

End Sub
 
J

JLGWhiz

This is one way:

Sub cpyStuff()
Dim lr As Long, lr2 As Long, rng As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Summary") '<==check case
Set ws2 = Sheets("Data") '<==check case
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "AA").End(xlUp).Row
Set rng = ws1.Range("A2:A" & lr)
For Each c In rng
For i = 2 To lr2
If c = ws2.Cells(i, "AA").Value Then
ws2.Cells(i, "AB").Copy _
ws1.Cells(c.Row, "b")
End If
Next
Next
End Sub

Be sure to adjust sheet names if needed.
 
J

JLGWhiz

I suggest you use Joels code. It is more efficient and reduces the
probablility of overwriting duplicates.
 
W

Ward via OfficeKB.com

Thank u so much! Can't wait to get to work and try this!! :)
Sub MakeSummary()

With Sheets("Summary")
RowCount = 2
Do While .Range("A" & RowCount) <> ""
data = .Range("A" & RowCount)
With Sheets("Data")
Set c = .Columns("AA").Find(what:=data, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If Not c Is Nothing Then
.Range("B" & RowCount) = c.Offset(0, 1)

End If
RowCount = RowCount + 1
Loop
End With

End Sub
I'm very new at VBA and am hoping my problem sounds easy to someone!!
1 workbook; 2 tabs---> summary and data.
[quoted text clipped - 14 lines]
6.111 Green
7.909878 Pink
 
W

Ward via OfficeKB.com

Thanks for ur help!!!!!!!!! :)
This is one way:

Sub cpyStuff()
Dim lr As Long, lr2 As Long, rng As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Summary") '<==check case
Set ws2 = Sheets("Data") '<==check case
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "AA").End(xlUp).Row
Set rng = ws1.Range("A2:A" & lr)
For Each c In rng
For i = 2 To lr2
If c = ws2.Cells(i, "AA").Value Then
ws2.Cells(i, "AB").Copy _
ws1.Cells(c.Row, "b")
End If
Next
Next
End Sub

Be sure to adjust sheet names if needed.
I'm very new at VBA and am hoping my problem sounds easy to someone!!
1 workbook; 2 tabs---> summary and data.
[quoted text clipped - 15 lines]
7.909878
Pink
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top