Need help on loop

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
I am finding a word "Start" using the following code
Code:
Cells.Find(What:="Start", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
Once the word is found, it does some works then again it find the next word "Start" and some more work.

I want to keep this in loop till the time the LAST word "Start" is found.

How to keep this in loop?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
Try this
Code:
Cells.Find(What:="Start", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False).Activate

firstfind = ActiveCell.Address
    Do While lastfind <> firstfind
        Cells.FindNext(After:=ActiveCell).Activate
        lastfind = ActiveCell.Address
    Loop
 

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
This will give me the address $B$6... I think I have not explained my problem properly.

Lets say I need a boolean variable which will return either true or false.
If excel is able to find the word "Start", the variable should hold the value true and if unable to find then "False"

How to do this?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What do you want to do with the cell once it finds the value, and also do you really need to search the whole spreadsheet, or is there a specific range you want to look for it at? But there is no need to do all that Activating.

What hsk is doing is finding the first instance and recording that address in a variable, and then repeating the FindNext as long as it doesn't equal the first address. But I would do this with a range object which will give you a nice way to get out if it can't find it.

What is the boolean variable for?
 

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
I have "Start" in cell B6, another instance of "Start" in cell B9, another one in B12 ..... so on the last instance is unknown.

I want to go to the cell B6 automatically, perform some action and then go to B9 again some action.... and so on till the last instance of the word "Start"

I hope this is better
 

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
What action?
I already wrote a piece of code that will do some task.
And do you only want to look for "Start" in Column B?
Yes.

The piece of code that I wrote is working fine but the sub ends after its first search for the word "Start" I want to continue doing the same task again and again till the last instance of "Start" is found.

Thanx
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you don't get an answer to your original post, just reply to that post, and put something like BUMP in your reply instead of starting a new post.
 

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
I did reply but no response again. Now I am not asking for a solution to that entire problem. I have thought of a logic to solve that problem myself. However, there is one small module that I am stuck at. If I get answer to the question posted in this post, I will get help to solve the original problem posted last week.
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

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