Amanda,

" 'Concatenate cells A,B and C " - it is a comment - should all be on one
line and I suspect you have it on two (because of the 'wrap-round' when code
is posted). I tried my own code with this situation and got the same error.

So simplly move the line "cells a,b ..." to the end of 'Concatenate ... or
delete it.

HTH

"Amanda" wrote:

Hi Toppers,

I've copied your script to VB & then ran it, (with all fingers crossed). It
has given me an "Invalid use of property" error and highlighted the last
cells word (cells A,B and C) in the sentence " FindMatch = Cells(r, 1) &
Cells(r, 2) & Cells(r, 3) 'Concatenate
cells A,B and C

Have I done something wrong?

Cheers & thanks so much for your help.

Amanda
Johannesburg



"Toppers" wrote:

Amanda,
Give this a try (test data first!). It creates an array
(mArr) of the concatenation of cells A,B and D in sheet2. It loops through
sheet1 and looks for a match against "mArr": if match found, A,B and C are
wriiten to sheet3.


HTH


Sub matchABC()

Dim mArr() As String
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lastrow As Long, r As Long
Dim outrng As Range
Dim FindMatch As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

Set outrng = ws3.Range("a2")

ws2.Activate
With ws2
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim mArr(lastrow - 1) ' Assumes data starts in row 2
For r = 2 To lastrow ' Store concatenation of cells A,B and D on sheet2
mArr(r - 1) = Cells(r, 1) & Cells(r, 2) & Cells(r, 4)
Next r

End With
ws1.Activate
With ws1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
FindMatch = Cells(r, 1) & Cells(r, 2) & Cells(r, 3) 'Concatenate
cells A,B and C
res = Application.Match(FindMatch, mArr, 0) ' Look for match in
sheet2 list
If Not IsError(res) Then
ws1.Cells(r, 1).Resize(1, 3).Copy outrng ' Copy A,B,C to sheet3
Set outrng = outrng.Offset(1, 0)
End If
Next r
End With
End Sub



"Amanda" wrote:

Hi, I need help on this one because I just can't figure out how to do this.
(Excel 2002SP3).

Two sheets in same workbook. Sheet one has data in col A, B & C. Sheet 2
has identical data in col A, B & D. Hundreds of rows on each sheet - but
probably only about 10% exactly match on all three columns per row.
I need (on a seperate sheet in same workbook) to populate rows if sheet 1
A1,B1 & C1 are exactly the same as sheet 2 A1, B1 & D1 - if not exact match
on all 3 cells it must ignore. I'd prefer not to leave lines/rows between
the populated data. Hence I should end up with one sheet that contains cols
A,B & C with however many rows, where the cols are identical on both sheets.
(Be nice to have one sheet with +- 150 entries instead of two sheets with
thousands).

If anyone can help me out, I would really appreciate it. Take care
--
Amanda
Johannesburg