Lookup and copy cells if match found
 

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

Lookup and copy cells if match found
 

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)
On Error GoTo 0
End Function

"Kcope8302" wrote:

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

Lookup and copy cells if match found
 

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


"Patrick Molloy" wrote:

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)
On Error GoTo 0
End Function

"Kcope8302" wrote:

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