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

Similar Articles

[PageSpeed] 32

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:

create a macro that finds certain words in doc then pste
i am looking to create a macro that finds certain words in a word doc then cuts and pastes them into an ecxell spreadsheet. What have you come up with so far? ted wrote: >i am looking to create a macro that finds certain words in a word doc then >cuts and pastes them into an ecxell spreadsheet. -- Gerry Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/201003/1 ...

formatting numbers in cells
Hi. I tried to format a group of horizontal cells by going to format celss, then entering the following: number, 2 decimal places, $ sign and then (2,184.10). I clicked on okay. What I ended up with was the correct formatting in the last cell, and the rest of them have #### in the cells. Can someone tell me why this happened and how to correct it? Thanks! one reason for #### is that the cell isn't wide enough for the format specified. Try widening the columns. In article <2ee201c40087$e561bfb0$a601280a@phx.gbl>, "Mary Jo" <anonymous@discussions.microsoft.com&g...

Filling cells in between pairs of numbers
Hello all, I am trying to find an easy way to fill in cells in between pairs of numbers each of which is exactly the average of the pair's difference averaged by the number of these cells greater than the previous. So I have 1 1 2 3 4 8 5 6 7 8 20 9 10 12 13 14 15 45 I want: 1 1 2 3.333333333 3 5.666666667 4 8 5 11 6 14 7 17 8 20 9 23.57142857 10 27.14285714 11 30.71428571 12 34.28571429 13 37.85714286 14 41.42857143 15 45 I know I can do a Fill, Series, but then even with a macro that does a "control shift up fill series enter",...

Outlook 2003 / Exchange Find issue with Shared Calendar
Hello, Here is the set-up and the issue: There is an Exchange 2003 server (with RPCoHTTP). There are 3 users accessing the Exchange server with Outlook 2003 on Windows XP SP2. One user has a calendar, which is shared, and owned by the two other users (The users do _not_ want to use a public folder calendar, since some PDA software they use cannot sync with a public folder, don't ask). Since the calendar is owned by everyone, everyone can make changes. Issue: If the user whose calendar is shared tries searching his calendar for, say, all entries with 'xyz' in name, he sees all of...

Merge Cells not available
I am attempting to merge cells in a spreadsheet, but the Option to merge both on the toolbar and in 'Format'-'Cell' are greyed out. My worksheet is not protected. Every Worksheet in the document acts the same. If the workbook is shared (Tools, Share Workbook) merging cells is not allowed. -- Jim Rech Excel MVP "Flash24" <Flash24@discussions.microsoft.com> wrote in message news:BB8D4738-3CD6-4575-B44E-02FA32D88B9B@microsoft.com... |I am attempting to merge cells in a spreadsheet, but the Option to merge both | on the toolbar and in 'Format'-'...

VBA code for Chart
Hello everybody How do I get the VBA code on the Chart once the chart is generated (manually)? I would like to automate this process of obtaining the charts for several worksheets at once Thank you i suggest you click <record macro> and then start creating a chart on some experimental data and finally quit recording macro. clikc tools, macro macro and highlight the particualr macro-name and click edit you see the code statemnts you only need to modify to make it general. <homescu@cs.ucsb.edu> wrote in message news:1132149187.465431.106250@g49g2000cwa.googlegroups.com... > Hel...

Define a name in VBA
Hi All, I need a VBA code to select a non-active sheet(TEST) and then detect non-blank area to define a name(GI) to it. Thanks in advance Bijan HI. Try : Sub test() Dim LCol As Integer, RCol As Integer, TRow As Long, BRow As Long Sheets("TEST").Select LCol = Cells.Find("*").Column RCol = Cells.Find("*", [IV1], , , xlByColumns, xlPrevious).Column TRow = Cells.Find("*").Row BRow = Cells.Find("*", [A65000], , , xlByRows, xlPrevious).Row ActiveWorkbook.Names.Add "GI", _ RefersTo:=ActiveSheet.Range(Cells(TRow, LCol), Cells(BRow, RC...

lock first row and first column WITHOUT selecting Cell "B2" first?
Hi, I'm new to this group. From a VB6-Program I create a new workbook with 1 worksheet. After writing the title row I want to set the first column and the first row non-scrollable. This is my actual code in the VB6 program: ws.Range("B2").Select ActiveWindow.FreezePanes = True I tested it, it works. My Question: is there another way to do this WITHOUT selecting the cell first? Doing it with an non-active window? I've searched to no avail, but probably haven't asked the right questions or used the correct terms. (English isn't my first lang...

I need to split a cell into 4 cells and keep in the same column
Data | Text to Column ? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "dc" <dc@discussions.microsoft.com> wrote in message news:251EEB9F-0B6B-439B-B81E-BEECDA1D5B75@microsoft.com... > With the amount of detail provided............... How about a guess? You have 4 words in A1 and want them placed in A:A4 Select A1:A4 and Edit>Fill>Justify Gord Dibben MS Excel MVP On Tue, 19 Sep 2006 13:02:02 -0700, dc <dc@discussions.microsoft.com> wrote: Not much to go on. You say "split" so I presume that you want only a p...

Sort Treats Empty Cells As Largest Value??
I have several projects that sort data from greatest to least (descending). That is, nothing is considered greater than a million. Of about 4000 rows between a third and a half are blank (no count). I need the maximum values/count at the top of the list, but Excel puts these empty cells at the top of the list. As a result I have to do a lot of moving of large groups of data around to put the empty/blank cells at the bottom of these lists -- a real time consuming pain. Is there a way to have Excel treat empty/blank cells as having lower values than cells with values? I assume I could f...

Count if cell contains a comma
I want to count the cells which contain a comma. Is it possible to do something like: =COUNTIF(B2:B53,FIND(",",B2:B53)>0) -- Thanks, Fred Please reply to newsgroup, not e-mail =COUNTIF(B2:B300,",") Try this out ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements On Sun, 7 Dec 2003 20:02:29 -0600, "Fred Smith" <fredsmith99@yahoo.com>...

Need VBA assistance for Excel Open Dialogue box
Good morning. I need to figure out code that will simply change current directory to a set directory and then show Excel's "Open" Diaglogue box. I am aware that I can use an open command to open a certain file, but I don't want to do that. I want a dialogue box and not a file. Any suggestions? Thanks Cordially, Perhaps: ChDir "c:\test\" Application.Dialogs(xlDialogOpen).Show -- Jim Rech Excel MVP "Brent E" <BrentE@discussions.microsoft.com> wrote in message news:BE9B00E4-2FB8-4BCB-80D9-A082208E0A77@microsoft.com... | Good morning. | ...

HIdden Cells?
I would like to be able to hide columns automatically in a spreadsheet if no data was entered in the cells for that column without having to highlight the column and choosing hide. Eg: The spreadsheet has column headings, but for printing purposes I would like to hide that column if no data has been entered under any number of headings. Is there anyway this can be done automatically? Thanks for your help I wouldn't do it automatically when printing, but I would use a macro that I could run on demand: Option Explicit Sub hideCols() Dim iCol As Long With ActiveSheet ...

Exact number of digits in a cell
Sorry to post simple question in this forum but I need to use a function key to check if the correct number of digits are in a cell. I presume it would work such as 6 digits entered True, 4 or 7 False. I have tried a number of books but am struggling a little. Help very much appreciated. Roger You can use the len function to return the # of characters in a cell. i.e. =if(len(a1)<>6,"You entered the wrong # of characters","You entered the right # of characters") ----does the following if a1 does not equal 6, says you entered the wrong # otherwise says you en...

excel locks up after selecting a cell
I have a laptop...so the problem is not a scrolling mouse wheel.....when I select a cell in the workbook it will not let it go....as I move the mouse around it keeps selecting all the sells as if i have the mouse held down....do not appear to have a key stuck either......however...when the cell is selected...(before i click the cursor in a cell) i can move around to different cells with the arrow keys and it only selects one cell at a time...like it is suppose to...as soon as I click with the mouse the whole spreadsheet continues to be selected wherever i move the mouse...even if i mov...

Continuall Add ing values in a cell
Hi Everyone- I suspect that this question has been asked before but it is difficult to know how to find the correct thread. That being said, here it goes. I would like to make a cell continually add values entered into them. For example, I have a cell that has a value of $12.37 and I have to add to it a new value as I encounter it, lets say $8.73. As it stands know, I pull out my trusty calculator andsum up the two values and key in the sum into the field. I have a pile of recites that I need to add as they come up. Any help is greatly appreciated. Manuel A. Ayala CAD Concepts...

Save as CSV file leaves CELLS content between quotation marks
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Oke I have something really weird, <br><br>Normally when you &quot;save as&quot; and choose CSV or Txt, each cell is divided by a comma (period) but the contents of a Cell is not between quotation marks. (windows) <br><br>Now on mac: When I choose UFT-16, TXT windows format, CSV windows format or Mac format. All these options leave a file where my cell contents are between quotation marks. I do not want that!!! How can I get rid of them....? <br><br>gr <br><br...

Where to find AdventureWorks database samples
Does anybody know where I can find the sample Adventureworks database? I need the most recent one for sql server 2008 sp1. Try this link http://www.codeplex.com/MSFTDBProdSamples Dave Ballantyne http://sqlblogcasts.com/blogs/sqlandthelike/ Andy B. wrote: > Does anybody know where I can find the sample Adventureworks database? I > need the most recent one for sql server 2008 sp1. > > Just what I needed. "Dave Ballantyne" <symlink@no5p323mmer> wrote in message news:%23EzXe6bxKHA.3560@TK2MSFTNGP02.phx.gbl... > Try this link > &g...

Finding a DL's home server
I'm trying to write a VBS that will add mailboxes to Distribution Lists, based on the CSV file from a Directory Export. I'm having troubles because in order to ADD a mailbox, you need to use the DL's home server in the LDAP ADsPath. This is not required if you only want to READ a property of the DL. For example, Let's say that the DL, 'DL-Office', is on Server1 which is in another Site. Say Server2 is local to me. If I just want to know how many members there are in 'DL- Office' I can do the following: strDL="LDAP://Server2/cn=DL-Office,cn=DLs,ou=Ci...

Access 2003: Finding a parent subform control
Hi, Let's say I have a form (F1) that has a subform control (SFC), which in turn displays some other form (F2). The question is: if I have a variable referring to an instance of F2 (i.t. an instance of the subform), how exactly can I obtain a reference to the instance of the subform control that "hosts" the given instance of F2? I thought it would be easy, like using Parent property for the subform. But the Parent property contains a reference to the parent form, not to the parent subform control. Does anybody know how to do what I want? Thank you, Yarik. "Yarik" &l...

Find and Replace against set of rules in 2nd table/worksheet
Looking for a tool, or code that does the following. Edit [partial cell data] in Sheet1, if [partial cell data] meets criteria in Sheet2, COL1. Sheet2, COL2 has the REPLACE WITH information. Presently there are 59 criteria on Sheet 2, and I have to perform this against 19 sites. So doing this manual via Find and Replace takes forever. VBA code, or something similar would be great. (Wanted to write an Update Query Access, but Access sent me here.) Thanks in Advance TBL1 COL1 Mari Johnson Jim Evans Charles Stanley Jerry Straight TBL2 COL1 COL2 Mari Mary Straight Strait See,...

Best way to find the last value in a variable-length table?
I have a table containing a series of readings, something like this: A B # Volume 15 1 1,000 ml 16 2 915 ml 17 3 830 ml 18 4 745 ml 19 5 660 ml ... I need a way to find the last row of the table, which could have anywhere from 2 to 20 rows. hi, ! if column A has only numbers - a formula outside column A =match(9e307,a:a) - a formula outisde "the range" =match(9e307,a1:a20) hth, hector. __ OP __ > I have a table containing a series of readings, something like this: > A B > # Volume > 15 1 1,000 ml &...

VBA macros
I have a project that has 6 macros defined with only 1 sheet which is named PTS08. I would like to run one of the macros. They all open text files. Let's suppose one of them is ADPS08.TXT. I want this file to go into a sheet called ADP. The sheet doesn't exist. How do I do this? TIA. PF. -- Posted via a free Usenet account from http://www.teranews.com Start here http://www.rondebruin.nl/txtcsv.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pierre Fichaud" <prf51@sympatico.ca> wrote in message news:47dffa5e$0$26109$88260bb3@free.teranew...

Free 3-days Modifier with VBA workshop online
Hey Guys, Learn From the Best! Mariano Gomez [MVP] is preparing for a free 3-days Modifier with VBA workshop online, believe me it will be very much helpful for all, check the link below: http://dynamicsgpblogster.blogspot.com/2009/02/this-week-free-visual-basic-for.html Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ ...

using named cells across worksheets
I've named various cells after producing a multi-worksheet model. The named cells can be applied in the worksheet they are in but I can't see how to apply them across other worksheets. At the moment, the only option I can think of is manually updating every formula in all the connected worksheets! Any ideas. Nick You defined the names so that the names included the worksheet name, too? Sheet1!test sheet2!test .... Sheetx!test Then you can just use them by making sure you include the complete name: =sum(sheet1!test,sheet2!test,sheetn!test) Is that what you meant??? Nick Malon...