Restart a search at point of last find

ElBombay

Board Regular
Joined
Aug 3, 2005
Messages
185
HI,

I have a check-clearing routine that reads the bank's list of cleared checks and then searchs a sequential list of check numbers from the GL to mark them "cleared". I would like to start the next search in the loop at the point of the last "found" check, rather than starting at the top of the O/S check list each loop.

I tried saving Activecell.Address to a variable, then calling Range(mVar).Activate at the top of the loop. This give an 'object variable' error. What am I doing wrong?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
What am I doing wrong?

Well, for one thing, yer not givin' us enough info to help you. Post the relevant parts of your code. (And please use the code tags when posting code.)
 

ElBombay

Board Regular
Joined
Aug 3, 2005
Messages
185
O.K., I'll show you but I don't know what 'code tags' are. Please correct or mark up my reply and I'll rewrite this code with the tags

Version that Works:

[Get the Chk# and Amt cleared from Bank state]
[Go to GL record of chks written]
Range("A9").Activate
bFound = 0
DO WHILE Activecell.Value <> "EOF"

IF cClrChk = Activecell.Value THEN

[Mark Chk as cleared]
[Save the "cleared Amount" fo comparison to GL]
bFound = 1
EXIT DO

ELSE

Activecell.Offset(1,0).Activate

END IF

[Return to Bank State]
[Compare cleared amount for any variance]
[ Read mVars for next search]

When I tried to mark my "found point" I changed the code to this, and got the object variable error:

[Get the Chk# and Amt cleared from Bank state]
[Go to GL record of chks written]
cStartPoint = "A9" ' CHANGE
bFound = 0
DO WHILE Activecell.Value <> "EOF"

Range(cStartPoint).Activate 'CHANGE
IF cClrChk = Activecell.Value THEN

[Mark Chk as cleared]
[Save the "cleared Amount" fo comparison to GL]
bFound = 1
cStartPoint = Activecell.Address 'CHANGE
EXIT DO

ELSE

Activecell.Offset(1,0).Activate

END IF

[Return to Bank State]
[Compare cleared amount for any variance]
[ Read mVars for next search]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
What's all the stuff in the square brackets []?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
If you use the postreply button at the bottom of a thread instead of just using the "Quick Reply" box you will go to a page that has more editing tools including code tag buttons. If you click the "edit" button on your post, you should also go to this screen where you can then highlight the code and click the "code" button and it will enclose it in tags. Once you've seen what the buttons do, you can see that it's a simple thing to also just type them in manually. It makes reading people's code much simpler.
 

ElBombay

Board Regular
Joined
Aug 3, 2005
Messages
185
Greg,

Thanks. You're right re the readability. And now there's no need to confuse Norie (and probably many others) with the comments or pseudo-code that I enclosed in square brackets. Those brackets brackets made sense to me. Honest :oops:

This code works:
Code:
    Range("B2").Activate
    Do While ActiveCell.Value <> "EOF"
        ' Read mVars
        fChkAmt = ActiveCell.Offset(0, 1).Value
        cClrdChk = Str(ActiveCell.Value)
        Do While Left(cClrdChk, 1) = " "
            cClrdChk = Mid(cClrdChk, 2)
            
        Loop
        
        
        ' Go to O/S check sheet
        Sheets("All Checks").Activate
        Range("A9").Activate
        bFound = 0
        ' Read entire list; EXIT when found
        Do While ActiveCell.Value <> "EOF"
            If cClrdChk = ActiveCell.Value Then
                ActiveCell.Offset(0, 3).Value = "C"
                ActiveCell.Offset(0, 10).Value = fChkAmt
                cBookAmt = ActiveCell.Offset(0, 5).Value
                bFound = 1
                Exit Do
                
            Else
                ActiveCell.Offset(1, 0).Activate
            
            End If
        Loop
        
        ' Process next check
        Sheets("Citi Chks").Activate
        If bFound = 0 Then
            ActiveCell.Offset(0, 3).Value = "Not Found!"
            
        Else
            'Put cBookAmt in Col D
            ActiveCell.Offset(0, 3).Value = cBookAmt
        
        End If
        ActiveCell.Offset(1, 0).Activate
    
    Loop

The changes (and error) would be in this section

Code:
        ' Go to O/S check sheet
        Sheets("All Checks").Activate

        cStart = "A9"                                                  'CHANGE
        bFound = 0
        ' Read entire list; EXIT when found
        Do While ActiveCell.Value <> "EOF"
            Range(cStart).Activate                                ' CHANGE
            If cClrdChk = ActiveCell.Value Then
                ActiveCell.Offset(0, 3).Value = "C"
                ActiveCell.Offset(0, 10).Value = fChkAmt
                cBookAmt = ActiveCell.Offset(0, 5).Value
                bFound = 1
                cStart = Activecell.Address                      ' CHANGE
                Exit Do
                
            Else
                ActiveCell.Offset(1, 0).Activate
            
            End If
        Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,133,652
Messages
5,660,138
Members
418,552
Latest member
Idiocracy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top