View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
talonega149 talonega149 is offline
external usenet poster
 
Posts: 6
Default 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.