try

Sub test()

Dim c As Variant

For Each c In Worksheets("ReferenceData").Range("A:A")
If c.Value < "" Then
If c.Value = Worksheets("PTR").Range(c.Address) Then

With Worksheets("PTR")
..Range(c.Address).Offset(0, 23) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 12)
..Range(c.Address).Offset(0, 24) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 13)
..Range(c.Address).Offset(0, 25) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 14)
..Range(c.Address).Offset(0, 26) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 15)
End With

End If
End If
Next

End Sub


Beware of text wrap. eg the following is in one line
..Range(c.Address).Offset(0, 23) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 12)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis




"Kcope8302" wrote:

It is stating there is a compile syntax error. All of the lines starting with
'.Range' are in red. To verify I was to take this and put it in VB in a
standard module correct? I understand the basics of what you sent but can not
see why it is having issues. I can send a copy of the report if that is
possible.

Thanks

"Atishoo" wrote:

try this (think I got my offsets right not tested it)
Private Sub CommandButton1_Click()

For Each c In Worksheets("ReferenceData").Range("A:A")
If c.Value < "" Then
If c.Value = Worksheets("PTR").Range(c.Address) Then

With Worksheets("PTR")
.Range(c.Address).Offset(0, 23) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 12)
.Range(c.Address).Offset(0, 24) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 13)
.Range(c.Address).Offset(0, 25) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 14)
.Range(c.Address).Offset(0, 26) =
Worksheets("referencedata").Range(c.Address).Offse t(0, 15)
End With

End If
End If
Next

End Sub

"Kcope8302" wrote:

I am looking for a method to compare column A in to worksheets. I would like
a Macro if possible but any method would work at this point. Once a match is
found it would copy 4 cells from that line into another worksheet.

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(W,X,Y,Z)

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