Compare 2 columns, return a 3rd macro

[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

 

[Barb Reinhardt]

Do you need VBA, or can you do it with VLOOKUP?

 

[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

 

[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.

 

[JLGWhiz]

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

 

[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

 

[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