Lookup and copy cells if match found

K

Kcope8302

I am having an issue with a Macro. The macro will look at column A between 2
worksheets (PTR and Reference Data). If what is in column A in Reference Data
worksheet matches Column A in PTR it will then copy 4 cells from that
row(L,M,N and O) and paste them in the PTR worksheet starting at column W.

Example
PTR – Before the Macro
A .......... W X Y
Z
CR94875


Reference Data - Data that would need to be copied if a match is found
A ………. L M N O
CR94875 TBD On-Time 5/25/09 Source List


PTR – After a match was found in column A
A .......... W X Y
Z
CR94875 TBD On-Time 5/25/09 Source List
 
P

Patrick Molloy

you say a macro problem without showing the code. thats difficult.

So as an alternative place this code in a standard module:

Option Explicit
Sub CopyData()
Dim cell As Range
Dim rw As Long
For Each cell In Worksheets("PTR").Range("A:A").Cells
If cell <> "" Then
rw = Lookup(cell.Value)
If rw <> 0 Then
Worksheets("PTR").Cells(cell.Row, "L").Resize(, 4).Value = _
Worksheets("Reference").Cells(rw, "L").Resize(, 4).Value
End If
End If
Next
End Sub
Function Lookup(item As String) As Long
On Error Resume Next
Lookup = WorksheetFunction.Match(item, Worksheets("Reference").Range("A:A"),
False)
 
K

Kcope8302

Patrick,

I scrapped every try I had at creating the macro. I did place the one you
provided in and I am getting a 'Compile error: Syntax error' when trying to
run the macro. An arrow then points at the 'Function Lookup(item As String)
As Long' line. Also the line that starts with 'Lookup =
WorksheetFunction.Match' is highlited red.

Can you assist me with this error.

Thanks
 

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