Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop thru cells to find matching record
I'm having difficulty with the following code.
I think thre are several issues, perhaps the easier one first. When I set the following range, I noticed it didn't start at row 14 but at row 12, not sure why? Set rng = wsSheet.Range("A14:A" & Cells(65536, "A").End(xlUp).Row). I noticed that the message box shows cells I'm not interested in. The more difficult problem is per the following The active workbook, "sheet 1" has a lising of serial numbers, the serial numbers start at row 14, the exact number varies, hence the set rng as per above. These are unique serial numbers (no duplicates) The intent is that the serial number listed in the active workbook (sheet 1) is found on the second workbook 123456.xls sheet "side 1". The 123456.xls sheet "side 1" has been sorted by serial number and a date and time column, as I want to ultimetely select the most recent recording of a serial number (duplicate serial numbers may exist), this puts the most recent at the bottom of each serial number grouping. All I want to accomplish is get the value in the adjacent cell of the matching serial number from the 123456.xls "sheet 1", however it must be the latest record of the serial number we are trying to find. The adjacent value is then placed in the adjacent cell from the active workbook "sheet 1". We then select the next serial number to find from active workbook "sheet 1" and repeat the routine until all serial numbers in active workbook "sheet 1" have been processed. Many thanks burl_h Sub Update() Dim wsDest As Worksheet Dim Dest As Workbook Dim wsSheet As Worksheet Dim tofind As Range Dim rng As Range Dim rng1 As Range Set wsSheet = ActiveWorkbook.Sheets("Sheet1") Set Dest = Workbooks.Open("F:\Test Data\Macro Files\123456.xls") Set wsDest = Dest.Worksheets("Side_1") With Application .ScreenUpdating = False .EnableEvents = False End With Set rng = wsSheet.Range("A14:A" & Cells(65536, "A").End(xlUp).Row) For Each cell In rng Set tofind = cell If (tofind.Value < "") Then With wsDest.Range("B:B") Set rng1 = .Find(what:=tofind, after:=Range("B1"), LookIn:=xlFormulas, _ lookat:=xlPart, searchorder:=xlByRows, Searchdirection:=xlPrevious, _ MatchCase:=False) If Not rng1 Is Nothing Then If rng1.Offset(1, 0) = tofind Then Set rng1 = .FindNext(rng1) Else tofind.Offset(0, 1) = rng1.Offset(0, 2) End If Else MsgBox "nothing found" End If End With End If Next cell With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop to find cells with specified value | Excel Programming | |||
Conditional Format - 2 lists of names to find matching cells. | Excel Worksheet Functions | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
Loop cells - get all rows with matching data - paste into different wb | Excel Programming |