Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
Perhaps
Code:
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.a...mming/200911/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
On Nov 4, 3:22*pm, "vogtm via OfficeKB.com" <u11518@uwe wrote:
Perhaps Code:
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 talonega149 wrote: 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.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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
On Nov 4, 3:44*pm, talonega149 wrote:
On Nov 4, 3:22*pm, "vogtm via OfficeKB.com" <u11518@uwe wrote: Perhaps Code:
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 talonega149 wrote: 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.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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
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 talonega149 wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200911/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
On Nov 4, 3:56*pm, "vogtm via OfficeKB.com" <u11518@uwe wrote:
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 talonega149 wrote: 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. -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200911/1 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
On Nov 4, 4:33*pm, talonega149 wrote:
On Nov 4, 3:56*pm, "vogtm via OfficeKB.com" <u11518@uwe wrote: 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 talonega149 wrote: 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. -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200911/1 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to find matches to copy values
Works perfectly. Thank you so much!
"vogtm via OfficeKB.com" wrote: Perhaps Code:
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 talonega149 wrote: 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.a...mming/200911/1 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a Range that matches Todays date Q | Excel Programming | |||
Add up Values if Find Matches x 2 | Excel Worksheet Functions | |||
Searching for closest matches using wildcards or range.find | Excel Programming | |||
Find text in a string that matches value(s) in a range | Excel Programming | |||
Find the POSITION IN A RANGE of text in a string that matches value(s) in a range | Excel Programming |