How to identify a cell [format|category|number|scientific] for sea

I currently have a project in which I need to identify a cell if it is 
formatted with scientific and to ignore the data value. Similar to:

If objExcel.Cells(intRow,Column).xxxxx = scientific Then
Else intRow = intRow + 1

Any help would be gretly appreciated

Office --> Excel 2k3 using *.vbs

0
Utf
11/18/2009 10:29:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1122 Views

Similar Articles

[PageSpeed] 31

If I record a macro while I format a cell as Scientific with two decimal places,
I'd get this numberformat:
0.00E+00

And this recorded code.
Selection.NumberFormat = "0.00E+00"

So I could use:

if objExcel.cells(introw,intColumn).numberformat = "0.00E+00" then
   'do nothing
else
   introw = introw + 1
end if

But I'm not sure if that matches what you need to check for, either.  There are
lots of variations of Scientific formatting.

(I wouldn't use a variable named Column in my code, either.)



delisle_d wrote:
> 
> I currently have a project in which I need to identify a cell if it is
> formatted with scientific and to ignore the data value. Similar to:
> 
> If objExcel.Cells(intRow,Column).xxxxx = scientific Then
> Else intRow = intRow + 1
> 
> Any help would be gretly appreciated
> 
> Office --> Excel 2k3 using *.vbs

-- 

Dave Peterson
0
Dave
11/18/2009 10:35:58 PM
Bonsour=AE Dave Peterson  avec ferveur  ;o))) vous nous disiez :=20

> If I record a macro while I format a cell as Scientific with two
> decimal places, I'd get this numberformat:
> 0.00E+00
>=20
> And this recorded code.
> Selection.NumberFormat =3D "0.00E+00"
>=20
> So I could use:
>=20
> if objExcel.cells(introw,intColumn).numberformat =3D "0.00E+00" then
>   'do nothing
> else
>   introw =3D introw + 1
> end if
>=20
> But I'm not sure if that matches what you need to check for, either.=20
> There are lots of variations of Scientific formatting.

;o)))
if objExcel.cells(introw,intColumn).numberformat  Like ("*E+*") then


0
Modeste
11/19/2009 12:32:15 PM
That may work or it may not.

It still depends on what the OP wants.



Modeste wrote:
> 
> Bonsour´┐Ż Dave Peterson  avec ferveur  ;o))) vous nous disiez :
> 
> > If I record a macro while I format a cell as Scientific with two
> > decimal places, I'd get this numberformat:
> > 0.00E+00
> >
> > And this recorded code.
> > Selection.NumberFormat = "0.00E+00"
> >
> > So I could use:
> >
> > if objExcel.cells(introw,intColumn).numberformat = "0.00E+00" then
> >   'do nothing
> > else
> >   introw = introw + 1
> > end if
> >
> > But I'm not sure if that matches what you need to check for, either.
> > There are lots of variations of Scientific formatting.
> 
> ;o)))
> if objExcel.cells(introw,intColumn).numberformat  Like ("*E+*") then

-- 

Dave Peterson
0
Dave
11/19/2009 1:27:22 PM
Reply:

Similar Artilces:

Map characters to their relative positions in a cell
I've been using an Excel 2003 macro to loop through text cells in a spreadsheet that is sent to me from another office. My macro loops until column H (formatted as text) is empty; that is ="" (equal to a null string). It's been worked just fine until recently when the macro seems to end prematurely. It returns a normal end but clearly there are additional rows yet to be processed. The row that causes the macro to finish prematurely always contains many clearly visible character comments, sometimes 5-6,000 characters in length. My hunch is that one or more...

IDD identifier range
Is there a predefined range of dialog resource identifiers or can one use any value from 0 to 65535? If a more specific range exists, is it a MFC implied restriction? Thanks! -- Alex >Is there a predefined range of dialog resource identifiers or can one >use any value from 0 to 65535? > >If a more specific range exists, is it a MFC implied restriction? It's an MFC restriction, see "TN020: ID Naming and Numbering Conventions" in your MSDN documentation. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq >It's an MFC restriction, see "TN020: ID Naming ...

How to copy a cell to another sheet having more than 255 characte.
It is copying only first 255 character.. How to resolve this problem to copy entire cell content ...

Sort by Categories in Task
Is there a way to sort the Task list by Categories. When we click on the column for Categories it says that you can't but is there a work around? I know that the Group By feature works, but the user would like to sort. Thanks ...

Sheet name from cell
Hi, Is it possible to assign the sheet a name which is entered in a cell. I know how to do this with VBA but would like to know if this is possibile with the help of formula. Row: A1 has name (Bill). I want that second sheet in the workbook should have the name entered in row A1. thanks, navin It can't be done without VBA. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "navin" <navin.narayana@gmail.com> wrote in message news:1170285069.554922.159530@l53g2000cwa.googlegroups.com....

Query Formula
Can you help with this formula to enter in MS query? Using ODBC t connect to data source but need query data to be restricted to dat range shown in two cells given on worksheet. Eg. A1 = 01/04/04 and A2 20/04/04 Query should only return records with dates 01-20/04/04 Many thank -- Message posted from http://www.ExcelForum.com ...

Condition Format #1
I have enclosed a chart with this explanation below. I am needing automate this chart according to the following rules. See chart also, if necessary. Thank you. First Whenever, a 3 digit numeric combination is entered into B, C and D cells and if each of the 3 digits are different (ie.3,7,2), then find the 3 cells on the same row between O and BG that have the corresponding boxed the 2 digit pairs. (O=01, P=02, Q=03, R=04, S=05, T=06, U=07, V=08, W=09, X=12, Y=13, Z=14, AA=15, AB=16, AC=17, AD=18, AE=19, AF=23, AG=24, AH=25, AI=26, AJ=27, AK=28, AL=29, AM=34, AN=35, AO=36, AP=37, AQ=38, AR=39...

Cells to columns
Hi, Easy I know, but i just can't get. I have a list of names, A1:A100, and i want these to run across the top of my sheet as column titles. i.e. A1:CV1 can any one help. Thanks Craig Select A1:A100 Edit|Copy Select B1 (can't use A1) Edit|paste special|transpose Delete column A if you don't need it. craiglittleperth@aol.com wrote: > > Hi, > > Easy I know, but i just can't get. > > I have a list of names, A1:A100, and i want these to run across the > top of my sheet as column titles. i.e. A1:CV1 > > can any one help. > > Thanks > ...

I want to copy a cell (A) to another cell. But, only if I peg (A)
(A) is not a valid cell reference. What does "peg (A)" mean. Use the large piece of white space in the body part of your message to fill in some descriptions please. Gord Dibben MS Excel MVP On Thu, 1 May 2008 14:10:11 -0700, Rv <Rv@discussions.microsoft.com> wrote: ...

How to count Number of cells holding a particular value.
HI All, In my table there are 3 columns. Each cell in the last column holds either 1 or 0. I want to get the sum of the cells of the last column on top of the column name. I used the sum function to do this and works fine. But my requirement is to get the sum of the cells in the last column when i Filter using 1st and 2nd columns. e.g. Filter using the first column would give 10 rows from 30 rows. I need the sum of the cells of the third row only for those 10 rows. How can I achieve this using functions? Thank You in advance. Gihan. Use SUBTOTAL(9, range) -- ____________________________...

What formula will take a name in one cell (last name,first name) .
I have a name in a cell, last name first then a comma then first name, and I want to seperate them into two cells. I know there is a formula to do that, but I don't know what it is, can anyone help me? Use the menu option Data:Test To Columns to parse the one field into two fields Good Luck Stewart "jobby" wrote: > I have a name in a cell, last name first then a comma then first name, and I > want to seperate them into two cells. I know there is a formula to do that, > but I don't know what it is, can anyone help me? You could use Data > Text to colum...

Calculation to replace the cell contents
Sorry to ask such basic questions but I only use Excel once a year for end of term marks. I have a column with results /82 eg; 56, 27, 49 (all out of 82) I want to convert the mark to /20 (out of 20) and for the results to replace the marks out of /82. So the formula should be mark/82 x 20 = mark out of 20 Could sm tell me apply this formula so that Excel will recalculate the column for me? Thanks in advance Hi Dave: You want to scale numbers in place: 1. in an un-used cell enter =20/82 2. copy this cell 3. select all the cells in the column you wish to re-scale 4. pull-down: ...

split color in cell...
A user just asked me if she could put a diagonal line in a cell, and then color the upper block one color, and the lower block another. The diagonal line is easy enough... format-cells-border and add the line. is it possible to split the background color in a cell??? (not that i've heard of, but sometimes things that I haven't heard of are possible) Hi AFAIK not possible interesting user request you get :-) -- Regards Frank Kabel Frankfurt, Germany mark wrote: > A user just asked me if she could put a diagonal line in a > cell, and then color the upper block one color,...

Pasting conditional cell formatting onto other cells
Suppose you have a matrix of cells, say in C11:D12 for simplicity, upon which you have imposed a conditional formatting scheme, so that, for example, cell C11 is shaded yellow and D12 is shaded cyan. Also suppose that the matrix in C11:D12 was derived from a matrix in A1:B2. Is there a simple way to impose the shading in the C11:D12 matrix onto the matrix in A1:B2, even though the conditional formatting imposed on C11:D12 is not applicable to A1:B2? Example: Here is C11:D12: 0.76 0.98 0.99 0.22 We impose on this matrix the conditional formatting scheme that all values between 0.50 an...

Maximum number of pcs connected to access
Hello everyone! I need to know hoy many simultaneous pc's connected to an access client/server MDB can it be? Can access 2003 handle 50+ connections? How about access 2007? Thanx a lot! Jorge Novoa I've had 53 connections to a well-designed Access 2000 format Jet database. Most were actively editing the 105+ MB database. It also depends upon record structure and quality of the network. I know of a database in Texas with 75 concurrent users. Officially Access/Jet can handle 255 users, but I believe that's pushing it. If you original spec call for 50 + users, you might con...

Format Cells?
Hello, Not sure if this is the easiest way to do this but I am wanting to create a series of cells A1 10.0.0 A2 10.0.1 A3 10.0.2 Etc. A14 10.0.15 A15 10.1.0 A16 10.1.1 A17 10.1.2 Etc. Is there an easy method of doing this perhaps with a Format function or will I have to create a sum to work out the last cell plus .0.1? Any help is appreciated. Andrew. Give this a try in any starting cell, eg in A1: =10&"."&INT((ROWS($1:1)-1)/16)&"."&MOD(ROWS($1:1)-1,16) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "@Homeonthe...

Identifying the Active Fill Color
I have a routine that adds a shape to a selected cell and I would like to have the color of that shape be the last selected "fill" color (or the default fill color if no change made since Excel was started). How can I determine the active fill color from the "Fill Color" toolbar using VBA? Seems like there should be a way to get that color value. Thanks... Steve No one having answered this so far I thought I'd have a go.. I couldn't find anything in the object model to look at for this, but that's because I don't have an extensive excel object model to loo...

cells that have dates
I have a spreadsheet with dates in several columns I need to know if a date in column H is past the date in column D and if it is, format the date is column H to color RED Let's say you have a date in H1 and you want to check it against the date in D1. 1. Select H1 2. Go to Format Menu, Conditional Formatting 3. Select "Formula Is" and type =$H$1>$D$1 4. Hit "Format" button, Patterns tab and choose an appropriate color 5. Hit OK twice Now whenever you enter a date in H1 that is later than the date in D1, H1 will turn whatever color you specified. HTH, JP On No...

Testing cell value for greater than 0
Hi, The value in a cell is derived from a formula and can be one of the following: NA error Number Null I need TRUE to be returned when the cell value is greater than 0. This test fails as an error is returned when the cell value is an error How do I fix this? Thanks in advance for all the help. Regards, Raj "Raj" <rspai9@gmail.com> wrote: > I need TRUE to be returned when the cell value > is greater than 0. This test fails as an error > is returned when the cell value is an error How > do I fix this? =IF(ISNUMBER(A1), A1>0, FALSE) ...

Transfering information to the next free cell in a column
I'm trying to set up a worksheets to track the spending of two people by amount and category. Since it's the shared expenses of two people I'm trying to keep track of who each purchase was made by (so a person 1 total, person 2 total). QUESTION Is there a way for an expense that is in a certain category to be transfered to the next available cell in a column? BACKGROUND I set it up by making one sheet for input and one to display the information. The input having columns for person/category/amount and the display showing columns for categories and each person's total...

Locking cells
Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" <Duplatt@discussions.microsoft.com> wrote in message news:03AE438C-0099-4313-9638-C3BE4A98C488@microsoft.com... > Is it possible to lock and unlock cells on one sheet based on certain > criteria in a certain cell on another sheet ? I would like to try, with your assistance. My goal is to Lock cells $B...

use VLookup to copy more than 1 cell
I have been using VLookup in Excel 2003 for some time but I would like to expand the VLookup results. Instead of just copying one cell to paste as the result of the formula, can I have VLookup copy and paste a series of cells from same row of the found item? Thanks and have a great day!!! :) You can change the result of the VLOOKUP function by changing the 3rd arguement in the function, which determines which column from the table to return. =VLOOKUP(Find_this,In_this_table,#_of_column_to_return,Closest_Match?) If you want to copy and paste this and have it adapt/change, u...

Cell flashing
Does anyone know how to format a cell so it will flash or blink ? It's possible to do so with code but its a bad idea, you'll find such (bad) code by searching Google. There is no built in function like Word in Excel and if you do use such code the results are usually jumpy and erratic, plus the processor is unavailable to do anything else until the code is stopped from running, ie the machine is virtually locked up, Regards, Alan. "Skioregon" <Skioregon@discussions.microsoft.com> wrote in message news:AD31EA15-BC24-4074-85DA-D826B871EE55@microsoft.com... > D...

Report: Account Numbers and Phone Numbers?
My wife had a brief scare this morning; she thought she lost her wallet which had all her credit cards, etc. She soon found it but that got us to talking about the need to keep a readily-accessible list of credit card account and phone numbers in case they should indeed ever get lost. Since account numbers and contact info are already contained in Money, I looked for a report or a screen that lists this information. However I could not find one. Does such a single-screen report exist, or does no other option exist in Money for finding this information but to click into each account one at a...

Go to cell #
Hello all, Is there a way to force a worksheet to always open in cell A1 regardless of what cell was active when the worksheet was saved. Sometimes different people save this worksheet with the cursor resting in different places, so it's annoying when it opens in some odd blank area. Any help on this is greatly appreciated. Thanks, -S Hi, You need to write a macro basically Private Sub Workbook_Open() Sheets("Sheet1").Activate Range("A1") End Sub This needs to be put in the ThisWorkbook object of the VBA Editor for your file. If this helps, pl...