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
834 Views

Similar Articles

[PageSpeed] 56

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:

Option Box Run Time Error Cell Locked
I have created option boxes that work great thanks to this forum. M problem is when I enable "sheet protection" and click on the option bo I get a run time error that says "the cell is locked." I have formate the cells in and around the option box as "not locked" and then enabl sheet protection. Is this the result of some property in visual basic? Thanks in advanc -- harkstev ----------------------------------------------------------------------- harksteve's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3048 View this thread: http...

How do I view cell text without it visually hiding other cells?
When I key on text in a cell to edit information in a template sent to me, the cell display pops out visually blocking other cells with information. How can I change the display to allow me to double click the cell and then enter information, without the cell display being so enlarged - using excel 2000. Don't double click, edit the cell in the formula bar. -- _______________________ Naz, London "ldmci" wrote: > When I key on text in a cell to edit information in a template sent to me, > the cell display pops out visually blocking other cells with informatio...

transpose first and last names in a cell?
Greetings, Excel Gods and Godesses, I have a file with names in a column, in the format FIRSTNAM LASTNAME. I would really love to have them in the format, Lastname, Firstname. Is there a fairly painless way to switch/transpose the names, an ideally add a comma after the lastname? Thanks, Confused, Joh -- johng ----------------------------------------------------------------------- johngo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1477 View this thread: http://www.excelforum.com/showthread.php?threadid=26397 gods and godesses were on leave, they are h...

My "Do While...Loop" wont loop and delete the spaces between my digits.
I am trying to eliminate the spaces between digits in Australian Business Numbers which have the format "NN NNN NNN NNN" to produce an eleven digit number with no spaces. I have written a function to do this that appears to work. I now want to call this function from a subroutine to change about 3000 numbers. The subroutine is causing me headaches. Here is my space-eliminating function: Function RemoveSpaces(CellContents As String) As String Do While InStr(1, CellContents, " ") > 0 CellContents = Replace(CellContents, " ", "") Loop Re...

Highlighted Cell #2
Thanks Ragdyer ...

Updatig the Next Consecutive Empty Cell in a Range
I am tracking a bank account balance and would like that whenever a new value is entered in cell c3, it updates the next consecutive empty cell in range c5:c200. Please advise how I can write the formula, function or macro. Thanks Sony You need event code to do what you want. Try this... right click the tab at the bottom of the worksheet where you want this functionality, select "View Code" from the popup menu that appears and copy/paste the following into the code window that appears... Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long ...

Visibility of active cell.
MS should keep the active cell (the one with the focus) visible while zooming/window resizing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=547e20eb-e330-485e-83c6-0a7e5d7ddb5f&dg=microsoft.public....

Repost: Loop through files
I posted this earlier, but it never made it to the screen so I'm reposting - hope it doesn't duplicate the earlier one. I need to loop through all currently open workbooks and display a message box that tells me the file name and whether or not the workbook is visible. Your example code is what I need. Thanks in advance for your assistance. Hi, Sub ShowThem() Dim oBook As Workbook For Each oBook In Workbooks MsgBox "Book " & vbNewLine & oBook.FullName _ & vbNewLine & "is " & _ IIf(oBook.Windows(1).Visible...

select cell based on a number in another cell
This is what I want to do I have a cell A1 with the number 40 In another cell I want to use the number 40 to select a cell, something like: =B40+234 Where 40 in B40 is the number typed in A1. how do i do this please? Try this: =INDEX(B:B,A1)+234 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== <jon.berg@gmail.com> wrote in message news:1133883789.555648.81840@o13g2000cwo.googlegroups.com... This is what I want to do I have a cell A1 with ...

How would I fill blank cells with the data from a previous cell?
Need to fill blank cells in a column with the data from the previous non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it in Excel try edit>fill>series (select one) Lotus's is simpler. >-----Original Message----- >Need to fill blank cells in a column with the data from the previous >non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it >in Excel > > >. > One way, assuming you mean you have data blank data blank or something like that and you want the cell with data to fill in the blanks below ...

Cell Data to Reflect Active Cell
Help - I'm struggling! Is it possible to switch the data in a particular cell to reflect the active cell as and when I move through a range in my spreadsheet? For example: If Cells: A1 = Apples A2 = Pears A3 = Oranges A4 = Lemons and my active cell is A1 then I would like B5 to = Apples. If I move focus so that the Active cell is now A3 I then I would like B5 to = Oranges and so on. Thanks in advance, Peter "Peter Davies" <peter@sedburypark.co.uk> wrote in news:#QJGurkqFHA.620@TK2MSFTNGP15.phx.gbl: > Help - I'm struggling! Is it possible to switch the...

Updating links
Hi, In one of our office spreadsheets, everytime it is opened it asks do you want to update cells from another spreadsheet. I believe when this spreadsheet was set up, a work sheet was copied from another spreadsheet. I thought that all the references to that other spreadsheet had been removed. Is there anyway to identify (other than manually checking each cell) which cell has a formula linked to the old spreadsheet. Thanks in advance, George Download and install Bill Manville's FindLink add-in, which you can find at: http://www.bmsltd.ie/MVP/ In article <bYd1d.27315$Z14.90...

creating 2 lines within one cell
I use a pc, and when I hit ALT Enter, I can get another line within one cell. How do you do that on a Mac? My boss can't seem to make it happen. Karen Hanson, Mohave Community College Try Command + Option + Enter Regards, Peo Sjoblom "Karen Hanson" wrote: > I use a pc, and when I hit ALT Enter, I can get another line within one cell. > How do you do that on a Mac? My boss can't seem to make it happen. Karen > Hanson, Mohave Community College ...

Loop?
I really am having a bad week... I have a workbook with two sheets; 'main' & 'not complete'. On the main sheet I have 10 columns of data, row 2 to 20 (More rows can be added so may be more). What I need to do is: for each row, if column I = 1 and J = "" then I need to copy some of the data from that row (column A,B,F & G) to the sheet called 'not complete'. the cell in column J will then be changed to 1. Repeat for each row making sure the sheet called 'not complete' does not have any blank rows. Hope that makes sense a...

Adding static character to a cell
I want to insert a $ in column of cells. Use currency as the format, but I want the $ to stay in the cell so it can be used also when printed out. I hope that's clear? Thanks Frank Frank, The $ in a currency format should also get printed. It is a view of the data, but consistent on screen and print. -- HTH RP (remove nothere from the email address if mailing direct) "Frank" <stratster68@IHATESPAMworldnet.att.net> wrote in message news:%23lA$94VRFHA.3988@tk2msftngp13.phx.gbl... > I want to insert a $ in column of cells. Use currency as the format, but I >...

How to link cell for value in another cell of pivot table?
In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata formular. And when I drag or copy this cell, again it copies the formular. Can you help how to just get the value, then be able to drag to link next cell and its value? thanks. 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 JamesChoi wrote: > In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata > formular. > And when I drag or copy this cell, again it copies th...

cell to change colour at given date
I have a cell in a spreadsheet into which I type a date. When the pc clock shows that the date in the cell is more that 30 days old I want the cell to turn red. How would I do that. Basic to many of you I am sure, but to someone who doesn't use this side of Excel, virtually impossible ! (I'm using Excel 97 if that makes any difference - but I presume not) Thanks in advance Tobit LOL - My pleasure. Glad you got sorted, and thanks for the feedback. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP...

Loop/Repeat Code
A2007 I need to run code while the record is displayed on a form, and must run this for all records. I have on the Click event of a button on the form: DoCmd.GoToRecord , "", acFirst Call MyFunction It is MyFunction I need help with: DoCmd 'Run some code and queries' DoCmd.GoToRecord , "", acNext Need help here to repeat/loop until the last record. Thanks in advance. ...

line spacing in cell
I would like to type 3 pieces of text, each on a seperate line in one cell. I've looked under cell format Press Alt-Enter where you want to start a new line! Regards, Stefi „dlh” ezt írta: > I would like to type 3 pieces of text, each on a seperate line in one cell. > I've looked under cell format Thanks so much. Works great!! "Stefi" wrote: > Press Alt-Enter where you want to start a new line! > > Regards, > Stefi > > „dlh” ezt írta: > > > I would like to type 3 pieces of text, each on a seperate line in one cell. &...

How to find beta?
Here is the thing, I am a student in Accounting and I am having trouble with beta. I have the stocks history for apple and djia, for about 3.5 years, I have gone thru the rate of returns, now I have to get the Beta for apple for this 36 month area, and I am lost, can anyone help me thru this? Hi Pat, Just check in your add-in (Tools>Add-ins) list that you have got the Analysis Toolpak loaded. If not, just click on the check box then the OK button. A lot more of statistical functions will become available that were not previously. You should now find what you are looking for. Als...

Cell Data
I want to be able to have the same data that is in one cell appear in another cell. For example, I want the data that is in cell B72 to also appear in cell J73. I enter =B72 in cell J73 but nothing changes. The data that is in cell B72 is text that reads 4-1. Please help !!!!!!!!! Clay It should work. Check tools/options /calculation/automatic. >-----Original Message----- >I want to be able to have the same data that is in one >cell appear in another cell. For example, I want the data >that is in cell B72 to also appear in cell J73. I enter >=B72 in cell J73 but n...

use of sum by skiping one or more cells
I want to sum only even cells from A1: A1000 I used =a2+a4+a6+a8+…………..+a1000 Used this formula n cell b1 But now I want to solve this problem by some short formula Help me thank you Hi Faisal, Try this =SUM((A1:A1000)*(MOD(ROW(INDIRECT("1:1000")),2)=0)) it is an array formula, so commit with Ctrl-Shift-Delete -- HTH Bob Phillips "Faisal Yameen" <FaisalYameen@discussions.microsoft.com> wrote in message news:26636591-F460-4713-B1F7-C89D833CF652@microsoft.com... > I want to sum only even cells from A1: A1000 > I used =a2+a4+a6+a8+......+a1000 > ...

find instances & report neighboring values
I am attempting to sub total amounts by month for a fairly large number of items. I would like to have excel find all instances on one date (Oct-05 or Jan-06 for example) and report all of the values in the cell neighboring each (ex, if I am adding the Oct-05 values, I want it to find the Oct-05 in d-4 and d-8 and report the value for e-4 and e-8). Once the values are reported, I need the values added . I don't care to see each value but I do need the total to be seen. =SUMIF(D:D,A1,E:E) where A1 hoilds the date to test for. -- HTH RP (remove nothere from the email address if mailin...

range select a value in a cell?
I have formulas calculating values to produce cell references such as J10, L15 etc the formulas are similar to ="I"&TEXT(Q4, "d") I am creating a macro where I want to select the cells calculated from my formulas... instead of using range("J10:K10").select i want the range to use the specific cell reference calculated in my formula... because the range i want to select changes every time a new record is added. is there a way to do this? any help is much appreciated. thanks b2: a2:a4 c2: =SUM(INDIRECT(B2)) --- HTH, David McRitchie, Microsoft MVP -...

I want to remove numbers but leave text in cells.
Hi, using Excel 2003,I'm pasting information into numerous cells of a worksheet. Each cell is a mixture of text and numbers eg. (123 test example 3210 ), can I remove the numbers and just leave text. regards, I believe that will require VBA. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "D" <derekrobinson05@eircom.net> wrote in message Hi, using Excel 2003,I'm pasting information into numerous cells of a worksheet. Each cell is a mixture of text and numbers eg. (123 test example 3210 ), can I remove the numbers and just...