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
0
burl_h
4/26/2010 1:25:31 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
925 Views

Similar Articles

[PageSpeed] 41

I have not tested your code but try the following alterations. I think your 
Set rng should work but try the following which is my preference for the 
code. (Note a space and underscore at the end of a line is a line break in an 
otherwise single line of code.)

With wsSheet
  Set rng = .Range(.Cells(14, "A"), _
      .Cells(.Rows.Count, "A").End(xlUp))
End With

In your find, need to specify the worksheet for the After parameter; 
otherwise VBA assumes B1 is referring to whatever is the active sheet.

After:=wsDest.Range("B1")

Is parameter Lookat:=xlPart correct? Should it not be xlWhole? 

Also there is no need to reassign cell to another variable. See example 
below where you can use cell.value without reassigning.

For Each cell In rng
  If (cell.Value <> "") Then
    With wsDest.Range("B:B")
      Set rng1 = .Find(what:=cell.Value, _
        after:=wsDest.Range("B1"), _
        LookIn:=xlFormulas, _
        lookat:=xlWhole, _
        searchorder:=xlByRows, _
        Searchdirection:=xlPrevious, _
        MatchCase:=False)
        
        
        If Not rng1 Is Nothing Then
          If rng1.Offset(1, 0) = cell.Value Then
              Set rng1 = .FindNext(rng1)
              Else
              cell.Offset(0, 1) = rng1.Offset(0, 2)
          End If
        Else
         'Better to specify what was not found.
          MsgBox "nothing found for " & cell.Value
          'MsgBox "nothing found"
        End If
    End With
  End If
Next cell

-- 
Regards,

OssieMac


"burl_h" wrote:

> 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
> .
> 
0
Utf
4/26/2010 3:20:01 AM
OssieMac,

Your solution worked great, thank so much for the assistance.

What would the novice programmers like I do without this kind of help,
again many thanks

burl_h
0
burl_h
4/29/2010 2:24:48 AM
Reply:

Similar Artilces:

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

Hiding records on reports by checkbox on form
Hi, I'm in the proccess of buliding a new database. Its purpose is to store various organisation contact details and then to produce reports based on this that can be printed onto sticky labels. This much or basically finished. However, we naturally don't want to print the whole database every time, so I'm using a checkbox on the interface form that will filter the report so that only checked records are prepared for printing. Can anyone tell what code I need to use to do this, and where? I've tried various methods, but nothing seems to work. I'm using accesss 2003 by the w...

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...

Record is locked (deadlock was detected)
when trying to run a CM Transaction I get "A get/change next operation on table 'CM_Transaction' deadlock was detected. when I click on more details i am given the following. [microsoft][ODBC SQL Server Driver][SQL Server] Transaction (process ID 77) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Return the transaction. currently I have ensured that no one is logged in. I view the odb.dex_lock table in the tempDB and cleared it. At this point I can run a reconciliation with no errors where I ...

Cells not sorting right with other worksheets
I have some problems!!! First I have multiple worksheets in a workbook. And in one column that has some names. and the columns next to them has data which is yes/no go/ngo for each person. If I add a person it will update itself in each worksheet. But I need to sort it. So I sort it and it sorts fine in the worksheet I'm working in. But with the other worksheets it sorts it but the data for each person doesn't stay with the person... I hope I explained this problem to where someone can understand. PLEASE HELP!!! How do you have the referencews set between each worksheet? &qu...

Exch 5.5 SP4
Hi One of my user sent an email to the client yesterday, Since then this user has been receiving return receipt every two minutes. Client has been also receiving the email in every 3 minutes. I have rebooted my server, Recreated the email address. Rest the IMS queue. Following is the email which my user are receving. Can any one please help. Thanks in advance -----Original Message----- From: Mail Delivery Subsystem [mailto:MAILER-DAEMON@nl.sbc.com] Sent: Saturday, February 03, 2007 11:01 AM To: Valerie@.com Subject: Return receipt The original message was received at Sat, 3 Feb 2007 ...

How to find cells with links to other workbooks?
I have a spreadsheet which shows under the edit menu links to other workbooks. I have tried to find where the links are without success. Is there a easy wy of finding such links? Hi you may try http://oaltd.co.uk/MVP/Default.htm and search for Findlink.zip (http://oaltd.co.uk/DLCount/DLCount.asp?file=FindLink.zip) "Bathonian" wrote: > I have a spreadsheet which shows under the edit menu links to other > workbooks. I have tried to find where the links are without success. Is there > a easy wy of finding such links? ...

Summing cells in pivot tables
I have a problem summing cells in a pivot table. When I double-click on a cell that I want to add, the formula from that cell gets added. Eventually I run out of characters. Is there a way to switch how the cell gets picked up. I can manually enter the cell addresses that I am trying to sum but that takes forever. Thanks, Ted There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html Ted wrote: > I have a problem summing cells in a pivot table. When I double-cli...

Conditional format if cell=0 then font colour same as background .
I have linked a cell from another sheet. If the original cell is blank, 0 shows in the linked cell (text would be typed in source cell). To handle this, I conditionally formatted the linked cell, so if it equals zero, the font colour is the same as the background colour of the cell so you can't see the zero But the zero still prints even though it is not showing prior to printing Try the formula =IF(Sheet1!F14="","",Sheet1!F14) in the linked cell. HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Paligap" <Paligap@discu...

Refresh pivot chart when info in a cell changes
To all, I am looking for help with a Macro. What I ultimately want to do is refresh a pivot chart when the user makes a selection from a list (in a data validation cell). For example, I am an end user that selects my territory from a "drop down". The pivot chart will update to the information that is directly related to my territory. Thanks in advance for your help. Excel 2007, PivotTable With Table dependent drop-downs instead of data validation drop-downs. With non-event-driven macros. http://www.mediafire.com/file/ygzjqyi2j1x/03_09_10.xlsm ...

Display value of cell
Using Excel 2000 In a cell the formula shows instead of the value/result, how do I correct this? I want to see the formula. Reply to kdfoxca@yahoo.com Thanks. Are all formulas cells show the formulas or just one? Maybe you have a space before the = (Excel think it is text now) Or your cell is format as text -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kenya" <anonymous@discussions.microsoft.com> wrote in message news:050b01c3942b$e28b7240$a301280a@phx.gbl... > Using Excel 2000 > In a cell the formula shows instead of the value/resul...

Cell Reference #3
In a workbook, is it possible to know all the cells where a particular cell is referenced in a calculation. Specifically, if I make a change in one cell, what are all the cells that will be affected. ...

Maximum number of characters in a cell
Is there a way to format a cell so that it can contain more than 255 characters? Bill Hi Bill, > Is there a way to format a cell so that it can contain more than 255 > characters? A cell can contain about 32000 characters, but Excel will display only about a 1000 of them (depending on font and fontsize). Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com If you would add some forced (manual) line breaks (<Alt> <Enter>) in your text at opportune locations, you will find that you can *display* much more text. -- HTH, RD ----------------------------------...

merge cells with a twist
A B C joe @ domain.com trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and that result is joe@domain.com Thank you. =A1&B1&C1 -- Regards, Peo Sjoblom (No private emails please) "jd" <jdumont@@novuscom.net> wrote in message news:uaAWelbgFHA.3436@tk2msftngp13.phx.gbl... >A B C > joe @ domain.com > > trying to merge cells in a row so that column a(joe) b(@) c(domain.com) > and > that result is joe@domain.com > Thank you. > > =hyper...

Deleting a word from a Cell
Hi Can anyone help, i am trying to deletel the last word from a Cell My cell currently read: - M04274059519 Total and I need a formula that will delete the "total" word Would really appreciate some help linexe On Jan 21, 7:27=A0am, Linexe <l.clark...@hotmail.co.uk> wrote: > Hi > > Can anyone help, i am trying to deletel the last word from a Cell > > My cell currently read: - =A0M04274059519 Total > > and I need a formula that will delete the "total" word > > Would really appreciate some help > > linexe C...

referring cell in macro function
If a create a macro intended to be used as a function in a worksheet is there a property of the referring cell? I want to know what cell the formula is in. Dim callCell As String callCell = Application.Caller.Address Returns the cell address of the cell where the UDF is used. "zxcv" <zxcvnosend@yahoo.com> wrote in message news:aef3cafd-4c52-40a6-8348-ebf957b3abad@a21g2000yqn.googlegroups.com... > If a create a macro intended to be used as a function in a worksheet > is there a property of the referring cell? > > I want to know what cell the form...

time entery to the cell
any one can help me how to make the cell automatically take from the time of the computer... because i am using the timesheet tha can download from office online but the problem isour staff are key-in the correct time for there time in or time out... what i want is no need for them to enter the time, they just click the time in or time cell the time of the computer will be recorded to the cell.... Thanks a lot to all the expert that very helpfull..... jeff Hi see: http://www.mcgimpsey.com/excel/timestamp.html for entering a timestamp automatically then a cell changes -- Regards Frank ...

How do identify a blank cell in a formula
Example: IF(s69=blank,"Void".... What I want to say is if a cell, (s69in this example) is blank, enter the word "Void" but the above way don't work - I don't know how to enter that in this IF formula. Barb, here is one way =IF(ISBLANK(S69),"Void","") -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Barb123" <Barb123@discussions.microsoft.com> wrote in message news:47...

Making cell change colour
Hi all I want to make a cell change colour 'on click'. Is there any way that this is possible? Help would be appreciated, Many thank darren --- Message posted from http://www.ExcelForum.com/ Hi this would require VBA / using an event procedure like Selection_change. Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany > Hi all > > I want to make a cell change colour 'on click'. > > Is there any way that this is possible? > > Help would be appreciated, > > Many thank > > darren > > > --- > Message posted f...

Using a value from a cell inside a formula!
Hi! I've got two cells that contain the start and end row of a matrix in sheet. I want to use these row numbers in a =COUNT.IF formula to count th number of specified instances in this matrix. But my problem is this; how can I use the values in the two cell inside the COUNT.IF formula?? I should look something like this; =COUNT.IF(F"cell1":F"cell2";"=argument") As you can see, the column (F) is specified in the formula but the ro number needs to be fetched from cell1 and cell2. Can anyone help me with the correct syntax? Thanks! -M -- marsupilam --...

Merged cells not copied properly
With some frequency, we have noted that cells are unmerged when copied. Generally speaking, we are copying a row and pasting it or inserting it in a new location. However, the clone is not always a clone, because the merged cells in the original are often unmerged either into smaller merged groups or into individual cells. Anyone have any idea what is going on here? ...

nested find in if problem
for excel 2003, the formula =IF(FIND("s",A2:A14),B2,IF(FIND("s",A2:A14),C2,0)) will fail ( show #VALUE as result) when s is not present but "r" is. what can one do about that? Try this: =IF(COUNTIF(A2:A14,"S"),B2,IF(COUNTIF(A2:A14,"R"),C2,0)) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "gs" <gs@dontMail.telus> wrote in message news:uH0U8kNFIHA.748@TK2MSFTNGP04.phx.gbl... > for excel 2003, the formula =IF(FIND("s",A2:A14),B2,IF(FIND(&q...

Mail Merge from Outlook Skips Records or doesn't merge at all
Outlook 2002 with Windows XP I set up a mail merge from Outlook with several contacts. The document seems fine, I can see all the recipients when I "view merged data" and in the "mail merge recipients" but when I complete the merge into a new document only 2 records are merged. When I switched to labels I received an error message "data records were empty or no record matched query". I've set the merge up "saving the contact data file to another document" and not saving it, neither worked. ...

Only allowing certain inputs into cells
I have a whole list of cells that formulas calculate off of that are based on 3 entrys into the cells. Either a -, 0, or 1. Is there a way to set the cell so that only one of those 3 characters can be entered? and if so, how do I do that? Thanks greatly. Hi David Check out Data>Validation in the menubar Choose list and type a,0,1 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "David C. Glasgo" <anonymous@discussions.microsoft.com> wrote in message news:020801c39a59$0bf46a00$a301280a@phx.gbl... > I have a whole list of cells that for...