Range to find matches to copy values

[talonega149]

I have been working on this for a while and getting nowhere. I have a
spreadsheet with 2 worksheets. Worksheet 'rd' has raw data and
'overview' is being used to summarize the results. I have set a range
on 'rd' [ Set r = .Range(.Range("N1"), .Range("N1").End(xlDown)) ] and
want to use each cell in that range to try and find a match in column
B on the 'overview' spreadsheet [ sh.Range("B7:B150") ].

Once a match is found, it would copy the value of the cell two columns
to the right on 'rd' and copy it in the first open cell on the
matching row on the 'overview' worksheet starting at column "K".

For example, name ABCD is found on the 'overview' range on row 12. I
want to copy the value two columns to the right of ABCD on 'rd' and
paste it into the first open cell on row 12 on the 'overview' sheet.

Any assistance would be greatly appreciated. I have code fragments
that work but nothing that accomplishes everything above.

 

[vogtm via OfficeKB.com]

Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
LR = .Range("N" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("N" & i)
X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(12, Columns.Count).End(xlToLeft).Offset(, 1)
End With
Next i
End With
End Sub

I have been working on this for a while and getting nowhere. I have a
spreadsheet with 2 worksheets. Worksheet 'rd' has raw data and
'overview' is being used to summarize the results. I have set a range
on 'rd' [ Set r = .Range(.Range("N1"), .Range("N1").End(xlDown)) ] and
want to use each cell in that range to try and find a match in column
B on the 'overview' spreadsheet [ sh.Range("B7:B150") ].

Once a match is found, it would copy the value of the cell two columns
to the right on 'rd' and copy it in the first open cell on the
matching row on the 'overview' worksheet starting at column "K".

For example, name ABCD is found on the 'overview' range on row 12. I
want to copy the value two columns to the right of ABCD on 'rd' and
paste it into the first open cell on row 12 on the 'overview' sheet.

Any assistance would be greatly appreciated. I have code fragments
that work but nothing that accomplishes everything above.

 

[talonega149]

Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
    LR = .Range("N" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("N" & i)
            X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
            If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(12, Columns.Count).End(xlToLeft).Offset(, 1)
        End With
    Next i
End With
End Sub

I have been working on this for a while and getting nowhere.  I have a
spreadsheet with 2 worksheets.  Worksheet 'rd' has raw data and
'overview' is being used to summarize the results.  I have set a range
on 'rd' [ Set r = .Range(.Range("N1"), .Range("N1").End(xlDown)) ] and
want to use each cell in that range to try and find a match in column
B on the 'overview' spreadsheet [ sh.Range("B7:B150") ].

Once a match is found, it would copy the value of the cell two columns
to the right on 'rd' and copy it in the first open cell on the
matching row on the 'overview' worksheet starting at column "K".

For example, name ABCD is found on the 'overview' range on row 12.  I
want to copy the value two columns to the right of ABCD on 'rd' and
paste it into the first open cell on row 12 on the 'overview' sheet.

Any assistance would be greatly appreciated.  I have code fragments
that work but nothing that accomplishes everything above.

Almost. No errors, but its confirming the name match and then
populating the value in row 12 of the overview sheet. It appears to
not be populating the value into the correct row which has the
matching name on the overview tab.

 

[talonega149]

Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
    LR = .Range("N" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("N" & i)
            X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
            If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(12, Columns.Count).End(xlToLeft).Offset(, 1)
        End With
    Next i
End With
End Sub

I have been working on this for a while and getting nowhere.  I havea
spreadsheet with 2 worksheets.  Worksheet 'rd' has raw data and
'overview' is being used to summarize the results.  I have set a range
on 'rd' [ Set r = .Range(.Range("N1"), .Range("N1").End(xlDown)) ] and
want to use each cell in that range to try and find a match in column
B on the 'overview' spreadsheet [ sh.Range("B7:B150") ].
Once a match is found, it would copy the value of the cell two columns
to the right on 'rd' and copy it in the first open cell on the
matching row on the 'overview' worksheet starting at column "K".
For example, name ABCD is found on the 'overview' range on row 12.  I
want to copy the value two columns to the right of ABCD on 'rd' and
paste it into the first open cell on row 12 on the 'overview' sheet.
Any assistance would be greatly appreciated.  I have code fragments
that work but nothing that accomplishes everything above.

Almost.  No errors, but its confirming the name match and then
populating the value in row 12 of the overview sheet.  It appears to
not be populating the value into the correct row which has the
matching name on the overview tab.

I just realized my example was confusing. The line on the 'overview'
sheet that matched was on row 12. This will change as I have names to
be matched "B7:B150" on the sheet. The value will need to go on the
line that matches the name and not all on line 12 like it currently is
doing. I hope this makes sense. Thank you

 

[vogtm via OfficeKB.com]

I don't think that your original question was well-worded. Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
LR = .Range("N" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("N" & i)
X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(X, Columns.Count).End(xlToLeft).Offset(, 1)
End With
Next i
End With
End Sub

Perhaps

[quoted text clipped - 35 lines]
Almost. No errors, but its confirming the name match and then
populating the value in row 12 of the overview sheet. It appears to
not be populating the value into the correct row which has the
matching name on the overview tab.

 

[talonega149]

I don't think that your original question was well-worded. Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
    LR = .Range("N" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("N" & i)
            X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
            If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(X, Columns.Count).End(xlToLeft).Offset(, 1)
        End With
    Next i
End With
End Sub

[quoted text clipped - 35 lines]

Almost.  No errors, but its confirming the name match and then
populating the value in row 12 of the overview sheet.  It appears to
not be populating the value into the correct row which has the
matching name on the overview tab.

Much better. I am sorry for the wording. I did make the change from
'12' to 'X' as it is the defined variable for the row and populating
on multiple lines is now working. Here is something that is weird.
Everything is off by 6 rows. Results are 6 rows higher on each and
every row than it should be. I do not see where this offset would
exist? Do you?

 

[talonega149]

I don't think that your original question was well-worded. Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
    LR = .Range("N" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("N" & i)
            X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
            If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(X, Columns.Count).End(xlToLeft).Offset(, 1)
        End With
    Next i
End With
End Sub

Perhaps
[quoted text clipped - 35 lines]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200911/1
Almost.  No errors, but its confirming the name match and then
populating the value in row 12 of the overview sheet.  It appears to
not be populating the value into the correct row which has the
matching name on the overview tab.

Much better.  I am sorry for the wording.  I did make the change from
'12' to 'X' as it is the defined variable for the row and populating
on multiple lines is now working.  Here is something that is weird.
Everything is off by 6 rows.  Results are 6 rows higher on each and
every row than it should be.  I do not see where this offset would
exist?  Do you?

I got it. Its working perfectly!!! Thank you so much.

 

[Dwayne Pelletier]

Works perfectly. Thank you so much!

Perhaps

Sub test()
Dim LR As Long, i As Long, X As Variant
With Sheets("rd")
LR = .Range("N" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("N" & i)
X = Application.Match(.Value, Sheets("overview").Range("B7:B150"),
0)
If IsNumeric(X) Then .Offset(, 2).Copy Destination:=Sheets
("overview").Cells(12, Columns.Count).End(xlToLeft).Offset(, 1)
End With
Next i
End With
End Sub

I have been working on this for a while and getting nowhere. I have a
spreadsheet with 2 worksheets. Worksheet 'rd' has raw data and
'overview' is being used to summarize the results. I have set a range
on 'rd' [ Set r = .Range(.Range("N1"), .Range("N1").End(xlDown)) ] and
want to use each cell in that range to try and find a match in column
B on the 'overview' spreadsheet [ sh.Range("B7:B150") ].

Once a match is found, it would copy the value of the cell two columns
to the right on 'rd' and copy it in the first open cell on the
matching row on the 'overview' worksheet starting at column "K".

For example, name ABCD is found on the 'overview' range on row 12. I
want to copy the value two columns to the right of ABCD on 'rd' and
paste it into the first open cell on row 12 on the 'overview' sheet.

Any assistance would be greatly appreciated. I have code fragments
that work but nothing that accomplishes everything above.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200911/1

.