Change Number to Text , Case error when cell is String and format is General

Hi All

How to check the cell is text or number ?
When Cell is string, Case Excel close and recoveing.

Sub FormatNum2str()
'~~ 2010/02/18
Dim iRows As Long
Dim iCols As Long
Dim ir, ic As Long
Dim iNum As Long
Dim strVal As String
If Application.Selection Is Nothing Then
    MsgBox "No Open Worksheet", vbCritical
    Exit Sub
End If

iRows = Application.Selection.Rows.Count
iCols = Application.Selection.Columns.Count


For ic = 1 To iCols
    For ir = 1 To iRows
        With Application.Selection.Item(ir, ic)
            If .NumberFormatLocal = "General" Then
                strVal = str(.Value)
                .NumberFormatLocal = "@" '~~ Format as a text
                .Value = strVal
             End If
        End With
    Next ir
Next ic

End Sub
0
moonhk
2/19/2010 3:53:10 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
740 Views

Similar Articles

[PageSpeed] 0

Change following line

strVal = Str(.Value)

to this
strVal = Format(.Value, "0")

You can use any valid number format between the double quotes.

-- 
Regards,

OssieMac


"moonhk" wrote:

> Hi All
> 
> How to check the cell is text or number ?
> When Cell is string, Case Excel close and recoveing.
> 
> Sub FormatNum2str()
> '~~ 2010/02/18
> Dim iRows As Long
> Dim iCols As Long
> Dim ir, ic As Long
> Dim iNum As Long
> Dim strVal As String
> If Application.Selection Is Nothing Then
>     MsgBox "No Open Worksheet", vbCritical
>     Exit Sub
> End If
> 
> iRows = Application.Selection.Rows.Count
> iCols = Application.Selection.Columns.Count
> 
> 
> For ic = 1 To iCols
>     For ir = 1 To iRows
>         With Application.Selection.Item(ir, ic)
>             If .NumberFormatLocal = "General" Then
>                 strVal = str(.Value)
>                 .NumberFormatLocal = "@" '~~ Format as a text
>                 .Value = strVal
>              End If
>         End With
>     Next ir
> Next ic
> 
> End Sub
> .
> 
0
Utf
2/19/2010 4:56:01 AM
On 2=E6=9C=8819=E6=97=A5, =E4=B8=8B=E5=8D=8812=E6=99=8256=E5=88=86, OssieMa=
c <Ossie...@discussions.microsoft.com>
wrote:
> Change following line
>
> strVal =3D Str(.Value)
>
> to this
> strVal =3D Format(.Value, "0")
>
> You can use any valid number format between the double quotes.
>
> --
> Regards,
>
> OssieMac
>
>
>
> "moonhk" wrote:
> > Hi All
>
> > How to check the cell is text or number ?
> > When Cell is string, Case Excel close and recoveing.
>
> > Sub FormatNum2str()
> > '~~ 2010/02/18
> > Dim iRows As Long
> > Dim iCols As Long
> > Dim ir, ic As Long
> > Dim iNum As Long
> > Dim strVal As String
> > If Application.Selection Is Nothing Then
> > =C2=A0 =C2=A0 MsgBox "No Open Worksheet", vbCritical
> > =C2=A0 =C2=A0 Exit Sub
> > End If
>
> > iRows =3D Application.Selection.Rows.Count
> > iCols =3D Application.Selection.Columns.Count
>
> > For ic =3D 1 To iCols
> > =C2=A0 =C2=A0 For ir =3D 1 To iRows
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 With Application.Selection.Item(ir, ic)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 If .NumberFormatLocal =3D "Ge=
neral" Then
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 strVal =3D str(=
..Value)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 .NumberFormatLo=
cal =3D "@" '~~ Format as a text
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 .Value =3D strV=
al
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0End If
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 End With
> > =C2=A0 =C2=A0 Next ir
> > Next ic
>
> > End Sub
> > .- =E9=9A=B1=E8=97=8F=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -
>
> - =E9=A1=AF=E7=A4=BA=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -

Thank It works.


0
moonhk
2/19/2010 6:01:40 AM
Reply:

Similar Artilces:

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

Tell the difference between upper and lower case
I have a column that has letters in it and I am using =COUNTIF(D13:D27,"M") in one cell and =COUNTIF(D13:D27,"m") in another. to keep track of what letters are put in the "D" column. But the both count the upper AND lowercase letters. Is there any way to mak excel tell the difference between upper and lowercase letters? Thanks Ro -- Message posted from http://www.ExcelForum.com RogerDaShrubber wrote... ... >=COUNTIF(D13:D27,"M") in one cell > >and > >=COUNTIF(D13:D27,"m") in another. > >to keep track of what lette...

Trying to find out which numbers occure most often
I�m giving exams to students and want to see if the students are missin the same questions in every class. I have a simple spreadsheet tha has me enter the question number into the column each time a questio is missed. The MODE function can tell me which number is occurrin most often. However, I need to see if there is any other numbers tha are occurring often as well. Is there a command that will allow me to look at the second mos entered number? For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mod would be 3 as that�s the number that repeats the most. However, happens 3 times and that...

how do I automatically update a text box in a chart?
Hi, You can link the textbox to a cell. When you update the cell the textbox should reflect this. Select the textbox border and then in the formula bar enter the complete cell reference, for example, =Sheet1!A1 Cheers Andy Kath wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

How do you change the background color of a picture?
I have a image that I have copy and pasted, however I want to fill behind the image. I know how to fill and all that, my problem is that it recognizes the entire image as a picture, I was wondering if there is a way to change the background while the image lays on top? sureisdifferent wrote: > I have a image that I have copy and pasted, however I want to fill > behind the image. I know how to fill and all that, my problem is that > it recognizes the entire image as a picture, I was wondering if there > is a way to change the background while the image lays on top? =============...

Sort ascending, make changes, restore previous order
I've got an AutoFilter in a spreadsheet. I want to sort ascending, mak some changes to some cells, then restore the previous order. Can thi be done easily or will this require some programming?? Thanks in advane! Matt -- BVHi ----------------------------------------------------------------------- BVHis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=859 View this thread: http://www.excelforum.com/showthread.php?threadid=47508 I'd use a helper column. Put =row() and drag down. Convert it to values (edit|copy, edit|paste special|values) Do all y...

visible cell only
I'd like to use the PERCENTILE function in a list that has been autofiltered and get the results based only on the visible cells. I've used SUBTOTAL in order to get count, average, min and max. But I need to get the .25 and .75 percentile figures for the filtered data (visible cells only). I've scoured these forums. I've scoured the web. I've found some vba code that was supposed to select only visible cells but it doesn't work for me. I posted last week in the programming section of these forums (and again this morning) but got no reply. I figure...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

Error: Invalid byte was found at byte index 63.
Does anyone know what this means: "Invalid byte was found at byte index 63. " If yes, please help. Apogee Apogee wrote: > Does anyone know what this means: > > "Invalid byte was found at byte index 63. " That means exactly what it says: At index 63 XML parser found a byte, which is invalid either according document's encoding or is forbidden in XML documents at all (see list of allowed in XML characters at http://www.w3.org/TR/2000/REC-xml-20001006#charsets) -- Oleg Tkachenko http://www.tkachenko.com/blog Multiconn Technologies, Israel ...

New Disk storage
Hi, I'm planning on adding a new SAN to our Exchange system (Active/Passive 2 node cluster) and this has been connected to the Exchange cluster nodes and is accessible as shared disk storage. But that isn't the question I've got, basically the mail stores are currently located on a Powervault (Drive S:) and I need to present a plan for moving this data (14 databases) onto the SAN storage, I can see two options Option1) Use the ESM to migrate databases to new drive letter 1. Use exmerge to export all email to PST files (and ensure backup) 2. use the ESM utility to change the...

Case Assigned History
Is there any way if a case is assigned to users and back again, that history can be tracked in 1.2? Thanks, ...

move cell contents
Is there a way to move a cell contents to another cell with a formula. ex: if a5="Name" then move g5 to j5? Also, I am using =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list. I have the formula in different place pick random names from different list. This does work, but I have different list with some of the same names and with the random pick I do not want the same name to appear. -- Thanks for any and all help. Davidl Hi David a formula can only affect the cell it is in, it can't move or change another cell for this you need some code ...

XCH Error 3092, OAB replication
I am getting error 3092 for OAB in Exchnage 2003 (migrated from 5.5) "Error 1129 occurred while processing a replication event. Folder: (3-8) NON_IPM_SUBTREE\OFFLINE ADDRESS BOOK\EX:/o" Tried to delete offilne addressbook and recreate but error has not stopped. Any help will be a great help on where to look to get rid of this issue. Thanks ...

Error Generating the Offlice Address Book
I have a mixed site with 3 5.5 server and 4 2003 servers. I installed 2003 SP1 a few weeks back and since then I'm having an issue generating my Offline Address Book. Here the event log messages I'm getting. Event ID 9331: OALGen encountered error 80040107 (internal ID 501023d) accessing the public folder store while generating the offline address list for address list '/'. - Default Offline Address List For more information, click http://www.microsoft.com/contentredirect.asp. Event ID 9335: OALGen encountered error 80040107 while cleaning the offline address list public ...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

Invalid XML error when I open customization setting
I have a problem when I try to open customization setting after I import an entity. The system errored "Invalid XML" "The XML passed to the platform is not well-formed XML". Please recommend how to resolve this problem. Thanks. ...

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

Counting number of X's in a column
I need to sum up the number of "Xs" in a column. I would be extremely grateful if someone could help me with the formula. I cannot seem to get one to work. We are using Excel 2003. Thank you. -- Susie Harris Operations Manager Environmental Education Association of Washington =COUNTIF(A:A,"X") -- HTH RP (remove nothere from the email address if mailing direct) "Susie Harris" <SusieHarris@discussions.microsoft.com> wrote in message news:FFA0FB17-C922-48A8-B3C3-0D487C1E6F6A@microsoft.com... > I need to sum up the number of "Xs" in a col...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

Linker Error after upgrade from VC7.1
Hello all, After upgrading a VC7.1 project to visual studio 2005, it failed to build in the release configuration with the follwoing error : 1>nafxcw.lib(winocc.obj) : error LNK2005: "public: class CWnd * __thiscall CWnd::GetDlgItem(int)const " (?GetDlgItem@CWnd@@QBEPAV1@H@Z) already defined in InstallDlg.obj The debug build works fine. The project uses MFC in a static library. Well, after doing some research, it seems that this one is tied to the fact that in a release configuration, _AFX_ENABLE_INLINES is defined, so inline functions are embedded in the .obj file. Sure there...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

formula based on format
Is there a way to have a formula that is based on a cell colour or based on the way a cell is formated? I need it to count the number of occurrences that this happens. -- Thank you for your time. Windows NT Office 97 Hi you'll need VBA. See for an example: http://cpearson.com/excel/colors.htm "James Kendall" wrote: > Is there a way to have a formula that is based on a cell colour or based on > the way a cell is formated? I need it to count the number of occurrences > that this happens. > -- > Thank you for your time. > Windows NT > Office 97 This ...

Lookup text with multiple search criteria
I know how to look up data and text using vlookup and hlookup, what I' like to know is whether there is a way of looking up data based on mor than 1 search criteria; for example If I wanted to look up an item in a 4 column database, I'd us something like this: =VLOOKUP("Apples",A2:D10,4,false) But that only looks for Apples. Say I wanted to lookup data based on subcategory of Apples, e.g. colour. If it was a number, I could use SUMPRODUCT and (assuming named range were in use) do it like this: =SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total)) But obvio...