Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Determine A Cell's Format And Type

How do I determine a cell's type (e.g., numeric or string or formula) and if numeric, the format (e.g., currency, general, scientific) including the number of decimal places.

Is there a function that returns the object's properties? Must I use isnumeric, isformula, and so on or is there one function that returns this information?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Merged Cells: Format The Cell So That If There Isn't Two Lines Of Type In The Description It Remains Aligned With The Quantity And Price
I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price

View Replies!   View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:

I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?

View Replies!   View Related
Function For Determine If Cell Has 'mingled Bold Text'
I have the following formula (taken from the web) in my spreadsheet which let me know if a cell have bold text or not. But when the cell has 'mingled' text, i.e. only partly bold, he gave a #VALUE error.

View Replies!   View Related
Vba Give Format To A Cell- Specify A Type For It
Does anyone have any clue of a VBA function that gets a cell and Changes the type of it´s contense?

I have been using the Cstr function as in the next example but I dont get the result I want

hojaRES.Cells(i, 6) = CStr(hojaRES.Cells(i, 6))
If IsNumeric(hojaRES.Cells(i, 6)) Then
MsgBox "nothin changes :("
Exit For
End If
hojaRES.Cells(i, 6) = "0" & "34" & hojaRES.Cells(i, 6)

My problem is that i want to add 034 prefix to a telephone number, and as it treats the data as numeric; number 0 (before34) is deprecated

View Replies!   View Related
Type A Number In A Blank Cell With The General Format
My excel recently the General number format so that when the general number fomat is used and I type a number in a blank cell with the general format, the number is always divided by 10.

E.g., I enter "102", the number is automatically improperly converted to 1.02.
However, if I enter "=102", the number is properly entered as 102.

How can I reset the General format to the original setting without this divide by 100 problem?

View Replies!   View Related
Conditional Format The List So That It Shows The Lowest Percentage In 'green' And The Highest In 'red'
I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

View Replies!   View Related
Conditional Maximum (find The Maximum 'value' For Each Individual 'type')
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'

The 'types' are not necessarily next to each other and the data cannot be sorted to do so.

type value
A 15
B 6
A 21
C 7
B 13

I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.

View Replies!   View Related
Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.

View Replies!   View Related
Determine Data Type Of Array Elements. Numeric Strings
I have an array of variants..

lvarArrSource = Array(12, "=F", 2, 3, 4, 5, 11, 6, 7, 8, 9, 10, "123", "F", "F", "F", "F", 11, "F", "NR", "F")

which i am using to map columns between spreadsheets. The basic numeric entries refer to columns to copy. But I want to make the routine smarter with the strings.

If array(x) = 12 (For example) Then
Do something
End If
If left(array(x),1) = "=" Then
Do something Else
End If
If array(x) Is String literal (e.g "xyz" Or "123") Then
Do a third thing
End If

i cant find a typeof or isstring kind of function. Isnumeric works ok for some values but quoted numbers (eg "123") return true (which isnt what i want). I have tried the left(string,1) = """ but excel seems to hide the quotes.

View Replies!   View Related
Type Mismatch '13' On Passing Function Result To Variable
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.

Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True

End Sub

View Replies!   View Related
Run-time Error '13' - Type Mismatch
I keep getting this run time error when ever i edit cell on the worksheet that contains the following code. The italicized line is the culprit according tp the debug feature:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "I10" And Target.Value "O5:O9" Then
ActiveSheet.Unprotect ("password")
Rows("29:46").EntireRow.Hidden = True
Select Case Target.Value
Case "DEALER1"
Rows("29:32").EntireRow.Hidden = False
Rows("29:39").EntireRow.Hidden = False
Case "LQ"
Rows("29:39").EntireRow.Hidden = False
Case "78620"
Rows("29:46").EntireRow.Hidden = False
Case Else
Rows("29:46").EntireRow.Hidden = True
ActiveSheet.Protect ("password")
End Select
End If
End Sub

View Replies!   View Related
Error '13' Type Mismatch In Function Code
while the code works fine by itself in the sample file, when inserted into my other file which contains another set of algorithms, it generates a run time error '13' type mismatch whenever the 'Hide' button is clicked.

this line of the following function: b = x / tpi

Function range(x)
tpi = 2 * 3.1415927
b = x / tpi
a = tpi * (b - Int(b))
If (a < 0) Then a = tpi + a
range = a
End Function

View Replies!   View Related
Run-time Error '13': Type Mismatch
This code came from the book, “VBA for Excel Made Simple” that I borrowed from the library. When I run the code, I keep getting the error message: Run-time error '13': Type mismatch.

The purpose of this form is to insert an extra row and add the representative name in the Rep Name list.

I have also attached the sample excel file and the explanation that came from the actual book.

Private Sub UserForm_Initialize()
RepBox = ""
SalesBox = "0"
For Each Cell In Range("rep_name")
RepList.AddItem Cell.Value
End Sub

Private Sub AddButton_Click()
With RepBox
If .Value = "" Then
MsgBox "Enter a name for the rep to be added"
End If.............................

View Replies!   View Related
VBA: Format Amts Into Text $ 9.99 Type Format
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.

Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.

I've almost gotten my routine finsihed but this is last remaining obstacle.

View Replies!   View Related
Can't Format A Cell
I don't know what the hell's going on here, but I have:

=CONCATENATE(IF(S11=1,(U13&Q11))) in cell T11,

where the data I want copied "as is" is: =(R12-Q12)/ABS(Q12), in cell Q11,

where the copy condition is =SIGN(Q11), in cell S11

But instead of the "33%" I need copied (concatenated), I get "0.33333333333333", and Excel won't let me format!! Anyone know why? This makes absolutely no sense at all.

View Replies!   View Related
Comparing Cell's Format
I have a quick question: I want to compare two cells' formats with as short programming as possible, so the program runs faster.

I have the current code to compare the formats and give the variable "Result" the value "FormatChange" if the formats are different.

View Replies!   View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.

What I am looking for

1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.

2) I would like this validation/formula to be applied to all the cells in a particular column.

3) Nice-to-have.

If the user enters a lower case letter, then I would like to convert to upper case.

View Replies!   View Related
Conditionallty Format A Cell Based On Another Cell's Value
Is it possible to conditionally format a cell based on the value of another cell?

I am trying to organise historic data of a horse racing system I am working on, and I have a column of finishing positions. Obviously if the position is 1, then the horse was a winner, and if the value is over 1, the horse lost.

I'm not sure where I am going with this at the moment, but if I can conditionally format a cell based on the value of another cell it will give me a starting point.

View Replies!   View Related
Can't Type Into Textbox
I have a text box I placed on top of a label. Why a label, because I am using it to make a border around a couple of textboxes. The label is on top of an image. The textboxes don't take focus so to speak, as I can't select them. Instead the label is selected.

View Replies!   View Related
Conditionally Change Number Format Based On Another Cell's Value
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'

Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.

Column AL's value is based on an IF formula which goes like this:

Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).

I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.

View Replies!   View Related
Format Individual Cells To Type In CAPS
how I can format individual cells to put text in caps. I have found how to format the whole worksheet, but I only want certain cells to do it.

View Replies!   View Related
Time Between Date Format Of Type Yyyymmddhhmmss
I have 2 dates format like 200903021124 and 200903030254. How do I use excel or excel VBA to calculate what is the time that elapses between this 2 date format?

View Replies!   View Related
Format Date: Type Library Is Missing
I have a problem with the format( Date) function.

On the computer I've developed the application everything works but when running the application on another computer I get a compilation error telling that the project or library doesn't exist

This is quite urgent so I really appreciate a quick solution.

On the computer that fails it says that Type Library is missing when looking at accessible references. Can this be something explaining the problem ?

Private Sub Workbook_OpenTest()
Dim varWeekW As String

varWeekW = Format(Date, "YYWW") ' Here it fails

End Sub

View Replies!   View Related
Type Password, Show Letters As Asterix's
I have a password protected form in access (I know this is excel but the code is the same), and when i click on the button and the inputbox opens for my password, i don't want it to display what i am typing, instead i would prefer asterix's?

View Replies!   View Related
Warcraft 'gold' Number Format, Or G00.00.00
I am trying to build some spreadsheets that handle exported data from the 'auction house' of the well known warcraft game.

I have exported data, but currency is exported in a concatinated string such as 463200.

In that example there would be 46 gold, 32 Silver and 00 copper. If the string was 63200 there would be 6 Gold, 32 Silver and 00 copper.

As I have about 18 different columns containing currency data I am trying to get excel to handle this format, however I cant seem to find a number format that will work with what is essentially two seperators in the number. Not only do I wish to display gold silver and copper in different colours, I wish to be able to perform calculations on the values.

Thus 463226 + 378475 should equal 841701.

View Replies!   View Related
Format (highlight) Cells With 'If' Function With 2007
I have a workbook that I am working with and I need to formatting the cells so that they will have multiple formulas and I am not sure how to make it work.

Sheet1 Column A is titled 'accounts'. There are 2700 rows under Column A that are being used. Column A is already formatted with a Vlookup formula so I can't add another formula in these cells.

What I need to do is highlight many ranges of these 'accounts' cells depending on their account numbers. I need to input this formula in a separate location on the spreadsheet. For example; If 'accounts' is >=110031100000 AND <=110031100099 then highlight those accounts in 'RED'. and If 'accounts' is >=1200454000000 AND <=120045400099 then highlight those accounts in 'green' and so on...I am not sure how to word the if statement. The formula must reside in column D or greater and not in A.

Am I going about this the wrong way. What I am trying to do is isolate the cells (accounts) that I have referenced on another worksheet so that I can visually notice any accounts that may have been excluded.

View Replies!   View Related
Restrictions On Drop Down List If Cell Is 'a' Then Other Cell Must Equal 'b'
I have two cells, both of which have drop down lists for options 'a' & 'b'. When one equals 'a' the other should be restricted into only saying 'b' and vice versa. I would also like the user to be reminded that he can only select 'a' once from the two menus. Ideally I would like to do this in a formula rather than VBA?

View Replies!   View Related
Formula That Allows Me To Type In Their Hour Work If It's Greater Than 40 Hours
I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.
We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)

My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?

Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00

If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2

View Replies!   View Related
IF Statement (fill A Cell With Either 'YES' Or 'NO' Depending On The Value Of One Cell)
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far


View Replies!   View Related
If Statement: Return 'V,' If Cell='&'
When I tried using if & or statements I got an error - so I tried this:

=IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","")

I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?

View Replies!   View Related
Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me

Run-time error '1004'

Method 'Range' of object'_Global' failed

From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....

View Replies!   View Related
Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed
I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.

View Replies!   View Related
Run-time Error '1004': Methd 'Range' Of Object '_Global' Failed
I have constructed the following code to set the print area of worksheets that have been selected to print to the range referenced in a worksheet level named range "xPrintArea". This named range is set using the OFFSET function. The procedure also sets the left footer to be a copyright notice that is also contained in a cell referenced by a named range.

Set oPrintArea = Range(sPrintAreaName)

is generating the error "Methd 'Range' of object '_Global' failed".

Note that the line

Set oCopyrightNotice = Range("CopyrightNotice")

does not generate this error. From what I've been able to determine from other research on this forum and others, I believe the problem is that I need to more fully qualify the object which Range(sPrintAreaName) is referencing. I've already tried to use

Set oPrintArea = wkSht.Range(sPrintAreaName).................

View Replies!   View Related
Run-time Error '1004': Method 'Worksheets' Of Object '_Global' Failed
I am not familiar with Excel/VBA and I tried a couple of suggestions with no luck. The excel file contains three forms and three modules and it is intended to calculate and build Electrical panels. I didn't write the code. I am just trying to fix the error. I was trying to attach the .xls file but it is 178 KB. How can I post the whole code.

View Replies!   View Related
Method 'Add' Of Object 'CommandBarControls' Failed (run-time Error '-2147467259')
i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (run-time error '-2147467259'). im not sure why its giving that problem, but im not very fluent in custom toolbars.

'//The following two procedures add a custom menu to the workbook programmatically//'
'//and then delete it//'
Public currentMonth As String
Sub CreateMenu()
Dim mybar As CommandBar
Dim myControl As CommandBarControl
Set mybar = Application.CommandBars.Add( Name:="CustomButtons", _
Position:=msoBarBottom, Temporary:=True)
mybar.Visible = True
Set myControl = mybar.Controls _
.Add(Type:=msoControlButton, ID:=1)
With myControl...........................

View Replies!   View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View Replies!   View Related
"Run-time Error '1004', Method ' Range' Of Object '_Global' Failed"
I keep getting this "Run-time error '1004', Method ' Range' of object '_Global' failed" Here is the code that has the problem:

Option Explicit
Dim i As Long
Dim j As Long
Dim lDup As Long
Dim lRow As Long
Dim NoDupes As Collection
Dim rRng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Swap1 As Variant
Dim Swap2 As Variant
Dim wks As Worksheet

Private Sub UserForm_Initialize()
Call DefaultSet
For lDup = 1 To 3
Call NonDuplicatesList(lDup)
Next lDup
End Sub.....................

It does not even loop once though the original UserForm_Initialize For/Next loop.

View Replies!   View Related
Run Time Error 1004 - Method 'Range' Of 'Object'_Global' Failed
I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.

View Replies!   View Related
VBA Functions: Type # For Date (Type:=?)
1. Is there a VBA Function equivalent to the FIND() function, If so What is it?
2. Let's say Im Putting a Date into a inputbox, what is the type # for date (Type:=?)??

View Replies!   View Related
Type Mismatch Error Message Comparing Value To Cell Error "Type Mismatch"
I'm trying to write a Macro that, in the active sheet (which contains plenty of data), deletes rows according to several criteria, for example:

- if cell(i, ar1(j)) = ERROR, delete row i and shift 1 up
(ar1 is an array of column numbers)

- if cell(i, ar3(j)) = 0, delete row i and shift 1 up
(ar3 is another array of column numbers)


The problem is that, when I run it, I get a "Type Mismatch" error message, and I don't understand why. Here is the

Sub Delete_invalid_rows()
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1, ar2, ar3, ar4 As Variant
Nr = 1379
ar1 = Array(11, 14, 19, 20, 22, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 64, 65, 66, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 104, 106, 107, 109, 112, 116, 126, 127, 128, 129, 131, 133, 134, 135, 136, 137, 138, 139, 140, 142, 143, 145)..............

View Replies!   View Related
Copy 'x' Cell Value Evey Nth Cell Along On Same Row
Have often used this site, but now need an answer to something a little more specific.

I would like to create a forumlae/function that would allow me to copy a cell value 'x' into every 'y' cell along. e.g copy cell x every 3rd cell along (in same row).

The formulae must allow for 'y' to be of any value between 1-5 as this can change.

I have attached a screenshot, as pictures are probably much better than my text above.

View Replies!   View Related
Copy Or Create 'blank Cell' As 'blank'
using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead it possible..

View Replies!   View Related
Format Data From Web So It's Usable
I think I have a similar problem as this thread The answer is probably somewhere in the forum, but I looked around and I can't figure it out on my own.

Basically, I copied some data off a website and now I am trying to organize it so it is usable. Here is my example. I would like to put the data below into usable columns. The only data I need is the hospital name (column A), location (column B), Births (column C), and Beds (column D). This is how the data currently appears in excel:..............

View Replies!   View Related
Determine Cell Value Or N/A
I am working on a spreadsheet where individuals must enter a value between 1 - 4 or n/a. I would like to include a formula for each question where if they enter a value between 1-4, this scores 1 and if they enter n/a this scores 0. Does anyone know a simple formula I could use, I have tried IF formulas but keep getting muddled.

View Replies!   View Related
Not Allow A Word To Be Type Into A Cell
I am trying to figure out how to not allow a word to be type into a cell. I am relatively new to excel and I am working on a spreadsheet where I do not want the word TOMORROW to be allowed in a cell.

View Replies!   View Related
Select Cell With Value I Type
I want a macro to select the cell with i type. Like i type in cust-0001 and it selects the cell which has cust-0001. I then want it to select the cell in column C that is in the same row with that value. E.g. Find cell in column A with value cust-0001 and then select the cell in column B in the same row.

View Replies!   View Related
Run-Time Error '1004' Cannot Open PivotTable Source File 'Consolidated Report'
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous

View Replies!   View Related
Error On Data Transfer Between Sheets (Method 'Range' Of Object '_Global' Failed)
I get an error message on the "Range(lstRow).Select line. (Method 'Range' of Object '_Global' failed). My goal is to transfer a specific range (C24:H24) to the first available row in Data Entry sheet.

View Replies!   View Related
How To Determine What Cell Was Selected
Can anyone help me in finding how to write the command in VBA to determine what Cell was selected in a worksheet? The objective is to know what Cell has been selected by double click event to pop up a userform with a calendar from which the user can select the date. I am looking at making it dynamic so I do not select a particular range.

Can this be achieved? I guess yes, but how?

View Replies!   View Related
Determine If A Cell Is Bold Or Not
I'm trying to create a VBA module that will update data. However, I need it to skip any updates to data that has been set by the user. For simplicity, the user changes will be bold. I've found the info on the site for a function for counting cells in a range that are bold. But really just need to know how to get format of the cell.

View Replies!   View Related
Copyright © 2005-08, All rights reserved