Cell.Find in VBA

Hi,
I have the following VBA Macro: 
Set FoundCell = .Cells.Find(What:="199", _
After:=.Cells(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

I don't how to write a VBA macro that would cells.find all 5-digit numbers. 
Here's an example: 
Column A
199
199
75781
75899
199
80012
Thanks,


0
Jeff1 (635)
12/2/2004 3:39:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
427 Views

Similar Articles

[PageSpeed] 7

Hi
I think you have to loop through all cells and check the length/value
of each cell

--
Regards
Frank Kabel
Frankfurt, Germany

"Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag
news:D3CEA329-2A66-4101-BBE7-54C2F86B88D4@microsoft.com...
> Hi,
> I have the following VBA Macro:
> Set FoundCell = .Cells.Find(What:="199", _
> After:=.Cells(1), LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False)
>
> I don't how to write a VBA macro that would cells.find all 5-digit
numbers.
> Here's an example:
> Column A
> 199
> 199
> 75781
> 75899
> 199
> 80012
> Thanks,
>
>

0
frank.kabel (11126)
12/2/2004 4:31:24 PM
I'm not sure I know how to do that. 
Regards,


"Frank Kabel" wrote:

> Hi
> I think you have to loop through all cells and check the length/value
> of each cell
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag
> news:D3CEA329-2A66-4101-BBE7-54C2F86B88D4@microsoft.com...
> > Hi,
> > I have the following VBA Macro:
> > Set FoundCell = .Cells.Find(What:="199", _
> > After:=.Cells(1), LookIn:=xlValues, _
> > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > SearchDirection:=xlPrevious, _
> > MatchCase:=False)
> >
> > I don't how to write a VBA macro that would cells.find all 5-digit
> numbers.
> > Here's an example:
> > Column A
> > 199
> > 199
> > 75781
> > 75899
> > 199
> > 80012
> > Thanks,
> >
> >
> 
> 
0
Jeff1 (635)
12/2/2004 5:29:03 PM
Hi Jeff

Check all cells in the sheet is not so fast

If your data is in one column you can use this

Add a column in your sheet and use this formula in the first cell and copy down
=LEN(A1)

Use Data>AutoFilter now to filter on 5

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Jeff" <Jeff@discussions.microsoft.com> wrote in message news:B035037F-F15B-4844-B41E-8EC73BBAF53E@microsoft.com...
> I'm not sure I know how to do that.
> Regards,
>
>
> "Frank Kabel" wrote:
>
>> Hi
>> I think you have to loop through all cells and check the length/value
>> of each cell
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:D3CEA329-2A66-4101-BBE7-54C2F86B88D4@microsoft.com...
>> > Hi,
>> > I have the following VBA Macro:
>> > Set FoundCell = .Cells.Find(What:="199", _
>> > After:=.Cells(1), LookIn:=xlValues, _
>> > LookAt:=xlWhole, SearchOrder:=xlByRows, _
>> > SearchDirection:=xlPrevious, _
>> > MatchCase:=False)
>> >
>> > I don't how to write a VBA macro that would cells.find all 5-digit
>> numbers.
>> > Here's an example:
>> > Column A
>> > 199
>> > 199
>> > 75781
>> > 75899
>> > 199
>> > 80012
>> > Thanks,
>> >
>> >
>>
>> 


0
rondebruin (3790)
12/2/2004 7:30:41 PM
Reply:

Similar Artilces:

protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this? Hi select the cells for which you want to allow entries. After this goto 'Format - cells - Protection&...

In creating a spreadsheet how can I lock a formula cell only?
I am trying to create a spreadsheet in Excell and want to lock the formula cells to they cannot be typed over accidently destroying the function?? Bob By default all cells are locked when the sheet is protected. Hit CRTL + A(twice if xl2003) then Format>Cells>Protection. Uncheck "locked" and OK. Select the cells you wish to lock and Format>Cells>Protection. Check "locked" and OK. Now Tools>Protection>Protect Sheet. This is mandatory!! You can set a password to unprotect. These can easily be broken in Excel but will keep your formulas from being ...

Adding a control button to insert a date in the active cell.
Hello, I'm trying to make a control button that will populate a static date (Ctrl +) in the active cell that the person has chosen. Any input is greatly appreciated. Mike Sub NOWDATE() ActiveCell.Value = Format(Date, "dd-mmm-yy") End Sub Gord Dibben Excel MVP On Tue, 15 Feb 2005 09:41:08 -0800, "Mike" <Mike@discussions.microsoft.com> wrote: >Hello, >I'm trying to make a control button that will populate a static date (Ctrl >+) in the active cell that the person has chosen. >Any input is greatly appreciated. Try This. Sub Time_Stamp()...

find which column has the maximum value
I have to check each row for the maximum value in that row. But instead of writing the maximum value of that row, I have to write the column number of that maximum value. The very first row of my dataset goes from 1 to 100, indicating 100 columns, and is there only to number the columns. So, the column number has to be picked from that row. For instance, in row 20, the maximum value is 10, and it is at column 56. How can I as output of a formula (or conditional formatting) get as answer 56? ta Try this =MATCH(MAX(20:20),20:20,0) -- HTH Bob Phillips (remove nothere from email addr...

Formulas In Cell Comments
Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman Not sure about VBA code but something like this might - Sub test() Dim sFml As String Dim cm As Comment sFml = "A1*4+A2" Set cm = Range("A1").Comment If cm Is Nothing Then Set cm = Range("A1").AddComment End If cm.Text sFml Range("A1").Value = 20 Range("A2").Value = 10 Range("C3").Formula = "=foo()" Application.CalculateFull End Sub Func...

changing cell reference via another cell
I have a spreadsheet with a tab (called calc) containing columns of calculations. The next tab (called D) contains a diagram which pulls numbers off of column D of the calc tab. I copy tab D to a new tab (by right clicking on the tab and selecting copy) and I rename the new tab as E. I want the cells in tab E to reference column E in the calc tab. Currently, I do a Find/Replace, changing 'calc'!D to 'calc'!E. Is there a way I can automate this so I don't have to do the Find/Replace. I was hoping I could just type E in a cell on tab E and it would be set ...

Go to a cell automatically
How can I make the active cell the cell after the statement: "End("B1").xlDown.offset(1,0)" to start a paste operation. Thanks in advance. try range("b1").end(xldown).offset(1).paste "Mercury" <pulgaron@bellsouth.net> wrote in message news:QWlfb.8794$9a7.7822@bignews6.bellsouth.net... > How can I make the active cell the cell after the statement: > "End("B1").xlDown.offset(1,0)" to start a paste operation. > > > Thanks in advance. > > > Try this Range("d1").Copy Range("b1")....

custom cells format
I want to format a cell so i can transform 1256453 in 125.56. How can i format a cell so i can view first number as 125.56? Thanks for your help. You have a couple of replies to your other posts. puiuluipui wrote: > > I want to format a cell so i can transform 1256453 in 125.56. How can i > format a cell so i can view first number as 125.56? > Thanks for your help. -- Dave Peterson Go to Format -->Cell -->Number-->Custom There you type 0.00, You get the results as desire Sanjeev Agarwal "puiuluipui" wrote: > I want to format a cell so i can tra...

Automatically Fill Empty Cells in a Column
Is it possible to automatically fill empty cells in a column with the data in the cell above it? Hi Select the column / range. Then Edit / Go to / Special / Blanks. This will select all of the blank cells in the range. Type = and hit the up arrow and then type Ctrl Enter. To fix these values use Copy then Paste Special / Values. -- Andy. "Deignan" <deignan@ncsievents.com> wrote in message news:D2D1B200-92F6-44C9-A929-E9C66888C671@microsoft.com... > Is it possible to automatically fill empty cells in a column with the data in the cell above it? ...

where do i find my 25 charactor numbers to register.
Where do i find the 25 charactor number to register my micrisoft. It was installed on the puter when i purchased it. But i have no idea. Thanks Look in the box for a manual that might have a sticker on it with the #. There might also be a sticker on the back of the computer case (where all the cords plug in). "Beverly" <Beverly@discussions.microsoft.com> wrote in message news:1BB2E440-3E28-4110-B988-B1CA8FA23683@microsoft.com... > Where do i find the 25 charactor number to register my micrisoft. It was > installed on the puter when i purchased it. But i have no id...

Display cells with data validation
Using 2003 - Is there a way of displaying cells that contains data validation restrictions? I've received a spreadsheet from someone with lots of restrictions on it and want to see which cells are affected. Thnks Anita "Anita" <Anita@discussions.microsoft.com> wrote in message news:19A5FB74-EF19-40C3-9632-EA0FB7F9D8B0@microsoft.com... > Using 2003 - Is there a way of displaying cells that contains data > validation > restrictions? I've received a spreadsheet from someone with lots of > restrictions on it and want to see which cells are affected. >...

Fill a spreadsheet automatically after one cell is input
Okay, I know how a normal autofill works. However, how do I do it in this case? Spreadsheet has formulae. User makes a selection and VLookup instantly populates the rest of the table with information. So how do I instantly populate said spreadsheet where it autofills the row once it is selected. An IsBlank() formula works in theory, but then I have to autofill the first 500 rows or so. I want something that just dynamically moves to fill the row as the user gets to it. Is it possible? For what you are stating I think that you are looking at some way or adding the extra data once the us...

finding days in Excel #2
Thanks - they both work really well - cheers Andy B. Bob Phillips Wrote: > or even > > =TEXT(TODAY(),"dddd") > > or > > =TEXT(A1,"dddd") > > -- > > HTH > > RP > > "icestationzbra" <icestationzbra.1dc7un@excelforum-nospam.com> wrot > in > message news:icestationzbra.1dc7un@excelforum-nospam.com... > > > > i hope you meant a worksheet function which would return the weekda > for > > the date entered: > > > > =TEXT(WEEKDAY(TODAY()),"dddd") > > > >...

Run a macro when cell value changes
I have a macro that I would like to run, onlly when a particular cel value reaches a pre-defined value. I really do not know how to do this - I have looked on help features but to no avail. any ideas -- Message posted from http://www.ExcelForum.com right click sheet tab>view code>copy/paste this>modify to suit>save Now when cell c1 calculates to >32 your macro will fire Private Sub Worksheet_Calculate() If Range("c1") > 32 Then call yourmacro ' MsgBox "Hi" End Sub -- Don Guillett SalesAid Software donaldb@281.com "Emea training >&quo...

Default View Advanced Find
When I click Advanced Find, it defaults to Contacts. What are the steps for having it default to Accounts which is what my salesforce primarily searches on? Thank you. The default is hardcoded to Contacts within Microsoft.Crm.Application.Pages.Search.dll A work around would be to modifiy the window.onload() funciton in ....\crm\Search\search.aspx Just add the line: objSelectControl.setValue("1,account"); Or one of the following other value's: 1,account 134,activity 112,incident 123,competitor 2,contact 1010,contract 1090,invoice 4,lead 3,opportunity 1088,salesorder 1024...

find default "look in"
Is there a way, I can make Excel default the "look in" to 'values' when I open the Find dialog box, rather than formulas ?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ It looks like excel tries to help by remembering the last thing you used. So maybe you could have a dummy workbook that you put in your XLStart folder that does a Find with the settings you like: Option Explicit Sub auto_open() Worksheets("sheet1").Cells.Find...

How To Find and Delete Duplicate Files? http://DuplicateFilesDeleter.com
How To Find and Delete Duplicate Files? http://DuplicateFilesDeleter.com http://DuplicateFilesDeleter.com is an innovative tool that can recognize duplicate audio files even if they are stored in different file formats and not marked with ID3 tags. It will find fast all similar or exact duplicate audio files in a folder and its sub folders. Unlike common duplicate file finders it will actually "listen" to your music and can recognize a song even if it is saved in different file formats. Supports MP3, MP2, MP1, MPA, WAV, OGG, AIFF, AAC, MP4, FLAC, AC3, WavPack (WV), Musepack (MPC) an...

Find 2 highest point to form resistance line
Dear expert, Have left a post for finding 2 lowest point for form a support line. Now, would like to find 2 highest point to form a resistance line. Data is from an array for formula. In this case, should choose 91.27 and 87.96 to form resistance line. Although 89.95 is higher than 87.96, line is forward plotting. Is it possible? If the highest in last row, can show "No line". Thanks 52.35 26-Jan-10 59.87 18-Feb-10 89.95 26-Feb-10 85.41 05-Mar-10 91.27 10-Mar-10 87.96 23-Mar-10 36.69 29-Mar-10 I put your data in A1:B5 In E1 I find the maximum with =MAX(...

excel 2000 downloadable template for amortizations. can't find
A year ago I found a place to download an amortization template in MS Excel 2000. Monthly and Bi-monthly. I believe it was an article written by a user who was just passing along the information. I can't find it now. I lost everything on my computer to a virus and really need this back. Any help will be appreciated. Thanks Hi Pyro Princess, Try: http://tinyurl.com/a64k6 --- Regards, Norman "pyro princess" <pyroprincess@discussions.microsoft.com> wrote in message news:D97F95BB-3BDF-4C16-8BAC-67E36C65C3A5@microsoft.com... >A year ago I found a place to do...

VBA copy xls to ppt
This may be basic but from an excel spreadsheet I want to automate the copy/paste of charts to a ppt deck. Altogether there are around 300 charts to be crammed into 50 slides. Any suggestions on tutorial/VBA coding would be appreciated! ...

How can I make the graph omit blank cells in the data set?
Hi, I've been workin' on the problem below that I've written a few days ago but got no answers. It's easy to select spesific cells to another column for ex: cells belong to type A then I draw the graph but a new problem occured, there are empth cells and mygraph counts them next question: how can I omit the blank cells from my data set while graphing? Thx a lot :) Emre UNAL http://www.geocities.com/dusemre Main Problem: Hi, I have a mixed data set as exampled below, Type Value A 14 B 0.156 C 1.65 A 18 D 4...

Learning VBA
Can I get some suggestions of a book or website that is good for learning VBA at a beginner's level for use in Access? I am planning on buying Paul McFedries' VBA book, because I love his book on Access Reports, but first wanted to see if anyone here has some suggestions. Thanks! Beginning Access XXXX VBA, Smith and Sussman, Wrox Press Steve santus@penn.com "Andeva" <Andeva@discussions.microsoft.com> wrote in message news:BBB89BB1-BD9B-4358-9EAA-9A30B3FC4A5D@microsoft.com... > Can I get some suggestions of a book or website that is good for le...

Impoting photos7 in Excel VBA
Hello, I almost completed my application, and I needto import some photos in the document. It is about 20 photos, and I mamaged routine for selecting and placing phots in the documnet. Problem is now that excel file is too big, when photos are of 2-3 MB each, excel file becom 20 MB an more. That case all starts to work very slowly, and colaps. If there is solution, to have some VB command in to the code to reduce size of the photo during importnitng. It is posible to compress photo in the Exce, but don't know how to make same thing automated in the VB code. Much appreciate if any...

SEARCH, FIND, LEFT...??
x-no-archive: yes Help required. I have comma delimited strings of various lengths. I wish to extract the first 3 fields as a comma delimited string. Since the fields are of different lengths, I've hit a brick wall. Example 107,165,72,9,30,10,4_________________ Result => 107,165,72 1,87,4,40,12,20,75,40,39,9,82,24_____ Result => 1,87,4 5____________________________________ Result =>5 120__________________________________ Result =>120 12,327_______________________________ Result =>12,327 mussels______________________________ Result =>mussels 2,263,106,82,19______________...

Valid for advanced find...?!?!
Hi, I just realized that some of the attributes are “valid for advanced find” and some are not. So why is for example “Business Type” (Attribute “businesstypecode”) not “valid for advanced find”? I think it is necessary to find/filter your customer base by Business Type. Please help! Thanks, Nikolas If you look at the metadata browser http://{yourCRMserver}/sdk/list.aspx you can find which attributes are and are not valid for Advanced Find. account/businesstypecode is indeed invalid for Advanced Find. Why is it like this? Presumably the answer is "Too hard", "Insu...