Free Microsoft Excel 2013 Quick Reference

Save number format in a variable

Hi,

I have a function that takes the monthly number in a cell, and converts it in annual value. Then it copies again that value in the cell. But there's a side effect. The cell itself looses its original number format. For exemple, it's no longer a currency format. My question is how can I save just before doing the operation the number format in a variable and then use this variable to aply this format to the cell again? Is it possible?

I'll copy too my function code here.

By the way, I tried this : Selection.NumberFormat = xlNumber and strangely, when it put the value in the cell again, it displays "-4145". However, if you look inside the cell, the true value is the correct one. Weird...

Thx!

Werner


	VB:
	
 Math_Mensuel() 
     
    Dim DblNombre As Double 
    Dim StrNombre As String 
    StrNombre = "" 
    Dim ActiveRange As Range 
     
    Set ActiveRange = ActiveCell 
     
     'We put data in the inputbox.
    StrNombre = CDbl(Application.InputBox(Prompt:="Entrez le nombre mensuel  convertir en annuel:", Type:=1)) 
     
    StrNombre = StrNombre * 12 
     
     
    ActiveCell.Select 
    ActiveCell = StrNombre 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The following 4 procedures can NOT return my wanted number formatting in a PivotTable.
This problem have been confusing me for a long time.
My friends, Who can tell me WHY?
By the way, My Excel version is 2003.


	VB:
	
 asNormal() 
     '普通显示
     
    On Error Goto Not_In_PivotTable 
    Set pvtTable = ActiveCell.PivotTable 
     
    With pvtTable.PivotFields(ActiveCell.PivotField.Name) 
        .Calculation = xlNormal 
        .NumberFormat = "G/通用格式" 
    End With 
    Exit Sub 
     
Not_In_PivotTable: 
    MsgBox "The chosen cell is not in a PivotTable." 
     
End Sub 
 
Sub asPercentOfRow() 
     '占同行数据总和的百分比
     
    On Error Goto Not_In_PivotTable 
    Set pvtTable = ActiveCell.PivotTable 
     
    With pvtTable.PivotFields(ActiveCell.PivotField.Name) 
        .Calculation = xlPercentOfRow 
        .NumberFormat = "0.00%" 
    End With 
    Exit Sub 
     
Not_In_PivotTable: 
    MsgBox "The chosen cell is not in a PivotTable." 
     
End Sub 
 
Sub asPercentOfColumn() 
     '占同列数据总和的百分比
     
    On Error Goto Not_In_PivotTable 
    Set pvtTable = ActiveCell.PivotTable 
     
    With pvtTable.PivotFields(ActiveCell.PivotField.Name) 
        .Calculation = xlPercentOfColumn 
        .NumberFormat = "0.00%" 
    End With 
    Exit Sub 
     
Not_In_PivotTable: 
    MsgBox "The chosen cell is not in a PivotTable." 
     
End Sub 
 
Sub asPercentOfTotal() 
     '占数据总和的百分比
     
    On Error Goto Not_In_PivotTable 
    Set pvtTable = ActiveCell.PivotTable 
     
    With pvtTable.PivotFields(ActiveCell.PivotField.Name) 
        .Calculation = xlPercentOfTotal 
        .NumberFormat = "0.00%" 
    End With 
    Exit Sub 
     
Not_In_PivotTable: 
    MsgBox "The chosen cell is not in a PivotTable." 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi ng

I have a cell with different formatting - and need to save the value and
formatting in a variable. For example both Bold and Italic and different
size inside the same cell - how can I save that in a varable

Thanks in advance
JJ

Hi All

Might be a simple query, but I'm trying to change the number format of a field in an excel pivot but the option to change number formats in the 'Value Field Settings' box is not appearing.

I am using the number as a 'Column Label' and it is coming up as a number not %. Can I not change this to a % because I'm not using it's not a 'value field'?

Has anyone seen something similar before?

I perform conditional formatting on a number of columns - can I store the lowest value in each column (that I am highlighting) in a variable?

I can have a seperate value for each column or a value that adds up as it goes through each column, I don't mind.

Range("E4:E37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("F4:F37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        Range("G4:G37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("H4:H37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        Range("I4:I37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        Range("J4:J37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
    
        Range("K4:K37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        Range("L4:L37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("M4:M37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("N4:N37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("O4:O37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("P4:P37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        Range("Q4:Q37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("R4:R37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
        Range("S4:S37").Select
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Bottom
        .Rank = 1
        .Percent = False
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


Is there a way to change the format of a value stored in a variable without inputing it onto a worksheet first? (i.e. if I have a number stored in a variable, and I want to format it to have commas and dollar signs) Any help would be greatly appreciated!

Hi

I want to achieve a number format with a decimal number in the cell
and behind this number you can find the Unit of Measurement of th
product.

For example: I have a double-variable with content: 21.234,23 tha
represents the amount of liters sold of that product and
string-variable with content L that represents the Unit o
Measurement of the sold product. Different UOMs could appear such a
P (pieces), L (liters) or M2 (square meters).

The two variables together should mean that I sold 21.234 liters of th
product.

When I look at the cell I should see: 21.234 L , but when I click o
the cell it should just appear as a number in order to be able to mak
calculation.

I have tried this in Exel with Format Cells, tab Number, choic
Custom and that works out fine.

But I should achieve the same result by using VBA-code because I hav
thousands of lines to process in that way. I have been trying my as
off with the bracket-things and could find it.

Thank you in advance.

Boerke.

p.s. my apologies if my English is miserable

--
Message posted from http://www.ExcelForum.com

How do i define a number format in excel for Indian Rupees. I didnt find it
in the drop down menu list in the numbers format.

I have a cell containing:

="Number of combinations = "&COUNTA(C2:C1500)*COUNTA(D2:D1500)

This number easily exceeds 1 million which makes it diffcult to read with all the digits in one long row, so I would like to format it with thousand-separators.
I could make the calculation in another cell e.g. C25 and then write:

="Number of combinations = "&C25

and then format C25 to thousandseparators, but that does not change the appearance in the cell where I combine it with the text.

Is it possible to change the number format in such a case?

NSV

I'm trying to find a way to determine if a number in a cell is formatted in a certain way or not.

I'm trying to find out if the number is in a 00000.00 format (for example 12345.67) or not.

I'm thinking that there must be some way to either check to see if the number is in that format, or check to see if the third character starting from the right is a ".", or checking to see if the number contains a "." at all. But I don't know how to do any of those things.

Any help?

I am having an issue with number format in my VBA code. The entire column of column D is format as ###.0000 (4 decimals places). There are certains cells which require zero decimal places. I am using the following bit of code, but it is not working correctly. It is still formatting the cell as 4 decimal places.
where "tradeTotals" is a defined variable

I have created a Pivot Chart from Excel data (Excel 2000) and I need to
figure out how to change the category axis number formatting. Currently, the
dates in the chart are showing up in long form (01/01/2005) and I would like
to change this to something shorter like 1/01. I changed the formatting of
the data in the original raw data and then in the Pivot Table, but neither of
these seemed to change the Pivot Chart formatting. When I right click on the
category axis, I do not get the option the change the number formatting (I do
for the value axis however).

Hi, can anyone tell me if it is possible to set the number format within a FormulaR1C1 command? I am trying to make my code a little more beautiful and avoid the use of Select as far as possible.

I'd like the number format to be NumberFormat = "###0.00" for the following calculation, but can't work out where to put it.

Thanks very much for any help.

	VB:
	
ActiveCell.Offset((i - 1), 2).Resize(1, cNum).FormulaR1C1 = _ 
"=IF(N(R[" & B & "]C),((R[" & A & "]C/R[" & B & "]C)*100),0)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, I am new to using Excel and i am a little confused about the custom number format.

Could someone explain this format in a bit more detail for me please.

_-* #,##0_-;[red]-* #,##0_-;_-*"-"??_-;_-@_-

I don't understand why the characters I've coloured pink are there, they totally confuse me.

I do understand that #,##0 is a positive value and that the 0 indicates that it is a format. I think I understand that the "-" is to represent a 0 value and I understand that [red] means that the negative values will be displayed in red.
I also understand that #,##0.00 indicates 2 decimal places.

Thanks : )

This is a quick one...

I have created a spreadsheet to load a variable amount of data into it when needed. I'm running Data Sorts, and need to be able to capture the last row in a variable, so i can reference that in the "RANGE function. So, it would be something like...

Dim lstrw as int
lstrw = Cells(Rows.Count, "F").End(xlUp)

Then use it as such...

Range ([a1], lstrw)

I'm sure this is easier than I'm making it to be... thanks guys!

I think this depreciation project is going to be the death of me. I hope
someone can help.

In one column of the template the years are entered as text format, but
column I need to use for the vlookup is formatted in a custom year. How can
I get the formula to recognize the text format year is the same as the custom
year format.

I have spent an obscene amount of hours trying to figure this out. Any help
would be appreciated.

Thank you.

Excel 2003, SP2 win Win XP, SP2

Trying to use a VLookup to present user-selectable information from an
Excel range. The range contains large and small numbers, percents and
currency values in it - and VLookup doesn't seem to return the cell
number formatting of the "looked-up" cell.

I've kludged a way to get the formatting from the looked-up cell and
apply it to the cell doing the lookup, but what I'm doing converts the
looked-up cell's value to a text (I'm using the TEXT worksheet
function) - and that keeps me from doing summations, etc.

Is there any way to set a cell's number format with a combination of
built-in or user-defined worksheet functions that can co-exist with a
VLookup, HLookup or Match function?

Thanks in advance for any help!

James

Had several different types of conditional formatting in a sheet that had
been designed using Excel 2003 (*.xls). Converted the sheet to (*.xlsx)
format. I can successfully add and edit new conditional formatting to the
sheet, and when you save it, it appears to save all changes, but the
conditional format changes are not saved. Any ideas about what is going on
here?

Thanks,

Ostate in Houston

I know there must be an easy solution to this, but I am trying to combine text and numerical cells into a single string while retaining the number format.
Example:
A1 = Based on
B1 = 5,000.00
C1 = dollars
D1 = A1 & " " & B1 & " " & C1
This returns "Based on 5000 dollars"

I would instead like it to return "Based on 5,000.00 dollars" (retaining the original numerical format).

How can I automate this?

Excel 2003, SP2 win Win XP, SP2

Trying to use a VLookup to present user-selectable information from an
Excel range. The range contains large and small numbers, percents and
currency values in it - and VLookup doesn't seem to return the cell
number formatting of the "looked-up" cell.

I've kludged a way to get the formatting from the looked-up cell and
apply it to the cell doing the lookup, but what I'm doing converts the
looked-up cell's value to a text (I'm using the TEXT worksheet
function) - and that keeps me from doing summations, etc.

Is there any way to set a cell's number format with a combination of
built-in or user-defined worksheet functions that can co-exist with a
VLookup, HLookup or Match function?

Thanks in advance for any help!

James

Hello -

I wrote a little deleteCell() function that deletes a cell and shifts
the cells below up. I had to do that because most of the sheet is
locked and I don't want the user to fool with it. So I wanted to give
the option of a undelete as well. I store the .Value and
..Interior.ColorIndex in a string and integer respectively.

I was wondering if I could store the entire cell in an object or
something like that? I want to keep all the formatting including
character font etc. ...

I tried ...

' Global
dim gCell as object

Set gCell = lSheet.Cell(1,1)

This works ... just not when I try to assign it back later ...

lSheet.Cell(1,1) = gCell

I cannot just copy it to the clipboard because the user might put other
stuff there in-between the calls.

How can I make this work to easily store and restore the format of the
cell. Would the CellFormat type work instead of the Object type?

Thanks,
Joe

I want to know if it is possible to select a different default number format
in Excel 2003 so that it will apply automatically when I open a new workbook.

Hi

I need to save a range in a variable for later use in an autofill function.
It fails on the autofill line as the value in rng is returned as TRUE and
not
the range data I expected.

I have tried different permutations but all that changes is the error
message.

The code is as follows:

RNG = Range("A2", Range("A2").End(xlDown)).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG, Type:=xlFillDefault

Thanks for any suggestions.

Dear all,
Are there any methods such that I can pre-specify hh:mm this format in a cell or specify in the user defined type?
Thank you

Hello,

I am trying to use conditional formatting in a dynamic named range, where the I want to highlight cells that are either blank or are not found in a table.

The dynamic range is called DN, and in the conditional formatting formula I use =ISBLANK(DN)

I've attached an example, and in this, no cells get colored. Surprisingly, in another workbook of mine, ALL the cells in the range get colored orange! What am I doing wrong?

Thanks
Snap