Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
I have this macro code:
Range("u22:u26").NumberFormat = _ Range("ad22").NumberFormat '(number format in ad22 is "0.00") Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [q16] Set AnswerCell = [u22:u26] 'set + - sign to cells 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more DP 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt If I rem out the first 2 lines with a ' it works and places a + or - where it should, but if I perform the first 2 lines just before that first, it doesent apply the + or -. How can I make the two work together so it does +0.00 or -0.00? TIA, Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
Providing the code was a good thing... the only thing you forgot to do is
tell us in words what it is supposed to be doing (so we don't have to fight our way through your logic). What do you have and what is this code supposed to be doing to it? -- Rick (MVP - Excel) "Aaron" wrote in message ... I have this macro code: Range("u22:u26").NumberFormat = _ Range("ad22").NumberFormat '(number format in ad22 is "0.00") Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [q16] Set AnswerCell = [u22:u26] 'set + - sign to cells 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more DP 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt If I rem out the first 2 lines with a ' it works and places a + or - where it should, but if I perform the first 2 lines just before that first, it doesent apply the + or -. How can I make the two work together so it does +0.00 or -0.00? TIA, Aaron. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
On Dec 18, 4:11*pm, "Rick Rothstein"
wrote: Providing the code was a good thing... the only thing you forgot to do is tell us in words what it is supposed to be doing (so we don't have to fight our way through your logic). What do you have and what is this code supposed to be doing to it? -- Rick (MVP - Excel) "Aaron" wrote in message ... I have this macro code: Range("u22:u26").NumberFormat = _ Range("ad22").NumberFormat '(number format in ad22 is "0.00") Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [q16] Set AnswerCell = [u22:u26] 'set + - sign to cells 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more DP 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt If I rem out the first 2 lines with a ' it works and places a + or - where it should, but if I perform the first 2 lines just before that first, it doesent apply the + or -. How can I make the two work together so it does +0.00 or -0.00? TIA, Aaron. Erm well I have a range of cells that I need to have a positive sign on when the number in them is positive as if its negative it naturally has a "-" before it. I also need the cells to be formatted according to another keycell whose decimal placing changes with another macro from a button link. So one cell is sitting there with 0.01 in it and it should say +0.01 (because in this example it is a positive number. Thankyou. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
Does this macro do what you want?
Sub ChangeFormat() Dim C As Range Dim F As String F = "+" & Range("ad22").NumberFormat & ";-" & Range("ad22").NumberFormat For Each C In Range("U22:U26") C.NumberFormat = F Next End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... On Dec 18, 4:11 pm, "Rick Rothstein" wrote: Providing the code was a good thing... the only thing you forgot to do is tell us in words what it is supposed to be doing (so we don't have to fight our way through your logic). What do you have and what is this code supposed to be doing to it? -- Rick (MVP - Excel) "Aaron" wrote in message ... I have this macro code: Range("u22:u26").NumberFormat = _ Range("ad22").NumberFormat '(number format in ad22 is "0.00") Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [q16] Set AnswerCell = [u22:u26] 'set + - sign to cells 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more DP 'Add one decimal place to the KeyCell number format acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign new format to answer cell AnswerCell.NumberFormat = acFmt If I rem out the first 2 lines with a ' it works and places a + or - where it should, but if I perform the first 2 lines just before that first, it doesent apply the + or -. How can I make the two work together so it does +0.00 or -0.00? TIA, Aaron. Erm well I have a range of cells that I need to have a positive sign on when the number in them is positive as if its negative it naturally has a "-" before it. I also need the cells to be formatted according to another keycell whose decimal placing changes with another macro from a button link. So one cell is sitting there with 0.01 in it and it should say +0.01 (because in this example it is a positive number. Thankyou. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number Format
On Dec 18, 7:58*pm, "Rick Rothstein"
wrote: Does this macro do what you want? Sub ChangeFormat() * Dim C As Range * Dim F As String * F = "+" & Range("ad22").NumberFormat & ";-" & Range("ad22").NumberFormat * For Each C In Range("U22:U26") * * C.NumberFormat = F * Next End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... On Dec 18, 4:11 pm, "Rick Rothstein" wrote: Providing the code was a good thing... the only thing you forgot to do is tell us in words what it is supposed to be doing (so we don't have to fight our way through your logic). What do you have and what is this code supposed to be doing to it? -- Rick (MVP - Excel) "Aaron" wrote in message .... I have this macro code: Range("u22:u26").NumberFormat = _ Range("ad22").NumberFormat '(numberformatin ad22 is "0.00") Dim KeyCell As Range, AnswerCell As Range Dim DP As Long Dim kcFmt As String Dim acFmt As String Set KeyCell = [q16] Set AnswerCell = [u22:u26] 'set + - sign to cells 'Get DP from Key Cell kcFmt = KeyCell.NumberFormat DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add anumberhere to apply more DP 'Add one decimal place to the KeyCellnumberformat acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) 'special case of kcFmt is "0" If kcFmt = "0" Then acFmt = "0.0" acFmt = "+" & acFmt & ";-" & acFmt & ";0" 'Assign newformatto answer cell AnswerCell.NumberFormat = acFmt If I rem out the first 2 lines with a ' it works and places a + or - where it should, but if I perform the first 2 lines just before that first, it doesent apply the + or -. How can I make the two work together so it does +0.00 or -0.00? TIA, Aaron. Erm well I have a range of cells that I need to have a positive sign on when thenumberin them is positive as if its negative it naturally has a "-" before it. I also need the cells to be formatted according to another keycell whose decimal placing changes with another macro from a button link. So one cell is sitting there with 0.01 in it and it should say +0.01 (because in this example it is a positivenumber. Thankyou. Yes it works great and I have incorporated it in my workbook to great effect. Thank- you again. Aaron. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
How do i change numbers in text format to number format? | New Users to Excel | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |