VBA Find Method problem

Been racking my brain on this one and I don't know why it 
hasn't worked. 

I'm using the Find method in my VBA query to find cells 
that contain the value "Rank", then does an operation on 
the cells below it.  Then it moves to the next page and 
does the same thing.  Unfortunately, if there are two 
cells with the value "Rank" on one page, the code will 
only look at the first one, do the operation, then go to 
the next page.  This is the basic code.  I've tried a For 
Each... Next loop right above the find method, but can't 
seem to get it to work.  I've also tried an If... Then 
statement.  

Do Until ActiveSheet.Name = "GLInputM"
    
    Cells.Find("Rank", , xlValues).Activate
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Range("A1:A36").Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1:A36").Select
    Selection.PasteSpecial Paste:=xlPasteValues, 
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Select
    Application.CutCopyMode = False
    
    If Not ActiveSheet.Next Is Nothing Then
    ActiveSheet.Next.Activate
    End If
    Loop

What I don't understand is why I can't put this:

If Cells.Find("Rank", ActiveCell, xlValues) = True then
Cells.Find("Rank",ActiveCell,xlValues).Activate
ActiveCell.Offset(1,0)...so on and so forth

Right above my "If Not ActiveSheet.Next Is Nothing Then" 
line.

Help is greatly appreciated!!!!
0
anonymous (74719)
8/20/2004 2:55:50 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
913 Views

Similar Articles

[PageSpeed] 35

Marks,

You need to step through to find the cells one by one, and you need to check
that you haven't backtracked back to the first cell.

The sub below will convert the 36 cells below any cell with "Rank" to
values, for all sheets in the activeworkbook.

HTH,
Bernie
MS Excel MVP

Sub ConvertCellsBelowRankOnAllSheets()
Dim c As Range
Dim myFindString As String
Dim firstAddress As String
Dim mySht As Worksheet

myFindString = "Rank"

For Each mySht In ActiveWorkbook.Worksheets
With mySht.Cells

    Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

    If Not c Is Nothing Then
        firstAddress = c.Address
        c.Offset(1, 0).Resize(36, 1).Value = _
        c.Offset(1, 0).Resize(36, 1).Value
    Else
        GoTo NotFound:
    End If

    Set c = .FindNext(c)
    If Not c Is Nothing And c.Address <> firstAddress Then
    Do
        c.Offset(1, 0).Resize(36, 1).Value = _
        c.Offset(1, 0).Resize(36, 1).Value
        Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
NotFound:
Next mySht

End Sub

"mark1" <anonymous@discussions.microsoft.com> wrote in message
news:9c1901c486c5$c87d2320$a501280a@phx.gbl...
> Been racking my brain on this one and I don't know why it
> hasn't worked.
>
> I'm using the Find method in my VBA query to find cells
> that contain the value "Rank", then does an operation on
> the cells below it.  Then it moves to the next page and
> does the same thing.  Unfortunately, if there are two
> cells with the value "Rank" on one page, the code will
> only look at the first one, do the operation, then go to
> the next page.  This is the basic code.  I've tried a For
> Each... Next loop right above the find method, but can't
> seem to get it to work.  I've also tried an If... Then
> statement.
>
> Do Until ActiveSheet.Name = "GLInputM"
>
>     Cells.Find("Rank", , xlValues).Activate
>     ActiveCell.Offset(1, 0).Activate
>     ActiveCell.Range("A1:A36").Select
>     Selection.Copy
>     ActiveCell.Offset(0, 1).Range("A1:A36").Select
>     Selection.PasteSpecial Paste:=xlPasteValues,
> Operation:=xlNone, SkipBlanks _
>         :=False, Transpose:=False
>     ActiveCell.Offset(0, 1).Select
>     Application.CutCopyMode = False
>
>     If Not ActiveSheet.Next Is Nothing Then
>     ActiveSheet.Next.Activate
>     End If
>     Loop
>
> What I don't understand is why I can't put this:
>
> If Cells.Find("Rank", ActiveCell, xlValues) = True then
> Cells.Find("Rank",ActiveCell,xlValues).Activate
> ActiveCell.Offset(1,0)...so on and so forth
>
> Right above my "If Not ActiveSheet.Next Is Nothing Then"
> line.
>
> Help is greatly appreciated!!!!


0
Bernie
8/20/2004 4:38:27 PM
Reply:

Similar Artilces:

VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...

Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...

Find e-mail account info.
I need to know how to get the pop3 and stmp info from both a hotmail and msn account to set up my mail retrevial in outlook. You cannot pop email from hotmail, you need to use HTTPMail. When you setup an account, use the HTTP account type. >-----Original Message----- >I need to know how to get the pop3 and stmp info from both >a hotmail and msn account to set up my mail retrevial in >outlook. >. > ...

Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...

Where do I find the email address?
I'm trying to create an email address J&L@JLRX.COM.AU as an SMTP email address to my test user, but the system tells me that the email address already exists. How do I find which of my users has that email address? Exchange 2003. -- | +-- JDMils | There are loads of ways - I detailed some of them here: http://hellomate.typepad.com/exchange/2003/08/finding_smtp_ad.html -- Neil Hobson Exchange MVP http://www.msexchange.org/Neil_Hobson/ "JDMils" <admin@jdmils.com> wrote in message news:OiXOb9mcGHA.4312@TK2MSFTNGP05.phx.gbl... > I'm trying to create an...

Problem with synchronization of Inbox
Hi. I've recently instlled Office 2003 professional. After install I hooked up with my Exchange 2000 server at work with my domain user account. It started synchronizing the folders as normal. Unfortunately dyring sync my netwok cable was pulled out by accident before the sync was completed. I re-attached the cable but Outlook wouldn't finish sync. The status bar indicates 'Waiting to sychronize folder'. This statement is permanent. I contacted my It professionals at work and they told me to shut down Outlook and delete any local .ost files. I was then told to ...

New G5 tower has problem opening Word 2004
I have a brand new G5 running OSX 10.4.10. I have installed Office 2004, and am having trouble launching Word. After double clicking, it opens a blank window and then freezes as it works it way through the "optomizing font performance" bar at the bottom of the window. <br><br>It starts to give me a warning box for one font after another that says "This font is corrupt and should be removed." I believe, however, that Word itself it installing the fonts in Font Book just before it determines they are corrupt. Wierd. <br><br> It has taken me over 1/2 ho...

VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

can find internet connection
Just finished installing a new cpu (old one died a month ago) and now when starting M07, I get a message "you must be connected to the internet to sign in online." I am definitely connected to the internet. I noticed that Money was able to add a recent update and I've tried reinstalling it. Any thoughts? Thanks. I just insatlled IE 7 and now my Money 2005 won't connect to the internet either. Obviously the IE 7 is blocking the connection. Does anyone know how to work around this problem? "Gus" wrote: > Just finished installing a new cpu (old one died...

Exchange 2000/2003 Routing Problems
I recently added a new Exchange 2003 server to our 1 server Exchange 2000 org. I am able to send mail from the new Exchange 2003 server to the 2000 server. But when I send to the 2003 server the mail does not go delivered. I am assuming this is a routing issue. How can I troubleshoot this? Should each server be in a seperate routing group and use a routing group connector? They are currently i n the same routing group. Thanks, Forest In case anyone has this problem my STMP connector was setup to use a smart relay. I had to check the box that said attempt local delivery before using...

vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

get change operation on coCustomLinks cannot find table
Hello, I have this client that has deployed their server in Singapore but has a branch in Thailand. Since Thailand also needs to use Great Plains, they have installed a GP 8.0 application in a workstation in Thailand and connects to the database deployed in Singapore. But when the staff in thailand logs onto her workstation, she gets the error "get/change first operation on coCustomLinks cannot find table" then all suceeding document processing (i.e. GL entries), go haywire - the next journal entry number doesn't generate. They went through the same installation procedures ...

Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...

DateDiff problem
I have a field that was imported as part of a text file. The field comes in as a string of numbers 20090107 which I have converted to a date using DateSerial function and it now appears in the calculated field StartConvert as 2009-01-07. I want to calculate how many days from the date in StartConvert to today's date which right now is Jan 28 2010. I use the following calculation... TestDate: DateDiff("d",[StartConvert],Date()). I receive the answer 20 when in fact the answer should be 385. What am I doing wrong? Thanks for any help. JoeP Since you only wa...

DevCon
I'm running Win 7 64-bit on an AMD processor. Most times, when I boot, my network adapter fails to initialize properly. I've found if I disable and enable it through the device manager, it will work fine from then on. To automate this, I picked up the WDK and DevCon.exe, and thought I'd set up a batch file to prep the adapter after boot. I can determine status as follows: C:\WinDDK\7600.16385.1\Tools\devcon\amd64>devcon status =net *81411043* PCI\VEN_10DE&DEV_0057&SUBSYS_81411043&REV_A3\3&2411E6FE&1&50 Name: NVIDIA nForce Networking Co...

Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...

How to find the associated FrameWnd from a view ?
In a MDI application, how can I find the associated FrameWnd of a CEditView? The code fragment below works, but there must be a simpler and direct method. Any suggestions? CMainFrame* pFrame = (CMainFrame *)AfxGetApp()->m_pMainWnd; CFrameWnd* pNextWnd=pFrame->GetActiveFrame(); // then check every FrameWnd until a match is found: pNextWnd = (CFrameWnd*) pNextWnd->GetNextWindow(); ... Use GetParentFrame: ASSERT_VALID(pView); CFrameWnd* pFrameWnd = pView->GetParentFrame(); ASSERT_VALID(pFrameWnd); --------- Ajay Kalra ajaykalra@yahoo.com Wow, I am impresse...

vista time problem
hi, since doing a re-install of the vista home premium os on my laptop yesterday i have had to constantly reset the time it goes back 1 hr every hour i have reset the time in the bios ,and made sure the time zone is correct and tried different time servers any help much appreciated -- martyb "marty" <marty@discussions.microsoft.com> wrote in message news:B25E40C0-5DE5-45ED-8A8F-224C3BFBA2B8@microsoft.com... > hi, since doing a re-install of the vista home premium os on my laptop > yesterday i have had to constantly reset the time > it goes back 1 h...

Meeting requests problems
I have this problem that I have not been able to resolve. Currently we are running Exchange 2003 server SP2 with all the updates. When an Outlook 2007 user send an meeting invitation to more than 20 recipients, I get the following NDR and eventually all messages time out. Sending the meeting invitation through Outlook Web Access is always successful. Reporting-MTA: dns;Emailserver.domain.com Final-Recipient: rfc822;BillyBob@company.com Action: delayed Status: 4.4.7 Will-Retry-Until: Thu, 4 Mar 2010 15:08:28 -0500 X-Display-Name: BillyBob@company.com Microsoft KB 938650 ...

What's the best way to find out if user is currently using mailbox ?
on Exch2000Sp3, I go to Systems Manager and I see mydomain\certain user mailbox. Last logoff time = blank. As an example, I compare my logoff information: my Outlook is currently opened but my logoff time = 10/07/04, 9:00AM Are those info reliable indicators of current mailbox activity ? Please advise. If policy allows, examine the sent items folder. -- regards, Michael Abbaticchio MVP for Microsoft Exchange Server http://mvps.org/exchange "Marlon Brown" <marlon_brown@hotmail.com> wrote in message news:%23%23LdPdMrEHA.4008@TK2MSFTNGP14.phx.gbl... > on Exch2000Sp...

Referencing dates against Products, in order to find the Qty. With out using Column referencing
if i have 1 sheet which has the master data for all customers and then i have several workbooks with customer sales Master sheet B2 Sales Date - this row has every day of the year A3 product B3 is sales Qty 1 1 2 55 3 55 Customer sheet has a similar layout but because this is made from a pivot table its only based on sales so when it comes to christmas day when there are no sales i cannot simply do a vlookup using column references as my data will be out of sync How do i reference the product and the date ...

Password-Passport Problems
I am trying to move my money account from a computer that uses Money2002 to a computer that uses Money2001. When I use it on the present computer I sign in using MSN Passport ID. When I try to open it in the new computer that uses 2001 it asks for my password. It will not accept my password and I can't figure out how to use MSN Passport ID to open the data. I'll be so thankful to anyone who can help me.... You cannot open a money 2002 file with Money 2001. The password thing is just Microsoft's way of telling you can't do that. Zach On 8/13/04 2:42 PM, in article 5...

Free power point viewer problem
Running XP, SP3 - downloaded Free Power Point Viewer for 2007. After many trials and errors I can finally get the program to open automatically when I click on a .pps attachment in an email, but then I get multiple error messages telling me the program cannot find a file. Each time I click "OK" on one, another one pops up. The files that it is looking for are, in this order: "Document", "and", "Local", "Temporary", "Internet" and "(name of file)". It seems that the program is unable to find my Temporary Inter...

Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...