Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
Hi,
I have a macro tied to a button called UP Sub DPshiftup() ' ' DPshiftup Macro ActiveSheet.Unprotect With Range("Q16") If .NumberFormat = "0" Or .NumberFormat = "General" Then .NumberFormat = "0.0" Else .NumberFormat = .NumberFormat & "0" End If End With ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Which is fine, but I need it to continue going up as long as I hit the "UP" button. IE 0 to 0.0 to 0.00 to 0.000 to 0.000 0 to 0.000 00 to a maximum of 0.000 000. I also need the cell to be formatted so that after every 3rd decimal place there is a space before the other decimal place holders are applied. I also need one to shift down also starting at a maximum of 0.000 000 to 0.000 00 to 0.000 0 etc down to 0. TIA Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
Give this macro a try...
Sub DPshiftup() With Range("Q16") If .NumberFormat = "0" Or .NumberFormat = "General" Then .NumberFormat = "0.0" Else .NumberFormat = .NumberFormat & "0" .NumberFormat = Replace(.NumberFormat, "0000", "000 0") End If End With End Sub Although you didn't ask, I guess you could use something like this to remove zeroes from the number format... Sub DPshiftdown() With Range("Q16") If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub .NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1)) If Right(.NumberFormat, 1)= "." Then .NumberFormat = Val(.NumberFormat) End With End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... Hi, I have a macro tied to a button called UP Sub DPshiftup() ' ' DPshiftup Macro ActiveSheet.Unprotect With Range("Q16") If .NumberFormat = "0" Or .NumberFormat = "General" Then .NumberFormat = "0.0" Else .NumberFormat = .NumberFormat & "0" End If End With ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Which is fine, but I need it to continue going up as long as I hit the "UP" button. IE 0 to 0.0 to 0.00 to 0.000 to 0.000 0 to 0.000 00 to a maximum of 0.000 000. I also need the cell to be formatted so that after every 3rd decimal place there is a space before the other decimal place holders are applied. I also need one to shift down also starting at a maximum of 0.000 000 to 0.000 00 to 0.000 0 etc down to 0. TIA Aaron. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
On Dec 9, 12:58*pm, "Rick Rothstein"
wrote: Give this macro a try... Sub DPshiftup() * With Range("Q16") * * If .NumberFormat = "0" Or .NumberFormat = "General" Then * * * .NumberFormat = "0.0" * * Else * * * .NumberFormat = .NumberFormat & "0" * * * .NumberFormat = Replace(.NumberFormat, "0000", "000 0") * * End If * End With End Sub Although you didn't ask, I guess you could use something like this to remove zeroes from the number format... Sub DPshiftdown() * With Range("Q16") * * If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub * * .NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1)) * * If Right(.NumberFormat, 1)= "." Then .NumberFormat = Val(.NumberFormat) * End With End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... Hi, I have a macro tied to a button called UP Sub DPshiftup() ' ' DPshiftup Macro * ActiveSheet.Unprotect * *With Range("Q16") * * * *If .NumberFormat = "0" Or .NumberFormat = "General" Then * * * * * *.NumberFormat = "0.0" * * * *Else * * * * * *.NumberFormat = .NumberFormat & "0" * * * *End If * *End With * *ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Which is fine, but I need it to continue going up as long as I hit the "UP" button. IE 0 to 0.0 to 0.00 to 0.000 to 0.000 0 to 0.000 00 to a maximum of 0.000 000. I also need the cell to be formatted so that after every 3rd decimal place there is a space before the other decimal place holders are applied. I also need one to shift down also starting at a maximum of 0.000 000 to 0.000 00 to 0.000 0 etc down to 0. TIA Aaron. Wow thank-you this has been a problem i have been trying to solve for 2 years now, but no one else fully understood my Q and subsequently the suggestions didnt work. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
Wow thank-you this has been a problem i have been trying
to solve for 2 years now, but no one else fully understood my Q and subsequently the suggestions didnt work. I'm glad I was able to be of help. -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
On Dec 9, 1:17*pm, "Rick Rothstein"
wrote: Wow thank-you this has been a problem i have been trying to solve for 2 years now, but no one else fully understood my Q and subsequently the suggestions didnt work. I'm glad I was able to be of help. -- Rick (MVP - Excel) Actually, any chance of adding the letter "g" after the zeroes so it goes 0.000 0 g? That would solve the other question I posted. If not how could I achieve this? TIA Aaron. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
Give these a try...
Sub DPshiftdown() With Range("Q16") If .NumberFormat = "0 \g" Or .NumberFormat = "General" Then Exit Sub .NumberFormat = Replace(.NumberFormat, " \g", "") .NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1)) If Right(.NumberFormat, 1) = "." Then .NumberFormat = Val(.NumberFormat) .NumberFormat = .NumberFormat & " \g" End With End Sub Sub DPshiftup() With Range("Q16") .NumberFormat = Replace(.NumberFormat, " \g", "") If .NumberFormat = "0" Or .NumberFormat = "General" Then .NumberFormat = "0.0 \g" Else .NumberFormat = .NumberFormat & "0 \g" .NumberFormat = Replace(.NumberFormat, "0000", "000 0") End If End With End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... On Dec 9, 1:17 pm, "Rick Rothstein" wrote: Wow thank-you this has been a problem i have been trying to solve for 2 years now, but no one else fully understood my Q and subsequently the suggestions didnt work. I'm glad I was able to be of help. -- Rick (MVP - Excel) Actually, any chance of adding the letter "g" after the zeroes so it goes 0.000 0 g? That would solve the other question I posted. If not how could I achieve this? TIA Aaron. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increase & Decrease Decimal Place
On Dec 9, 1:51*pm, "Rick Rothstein"
wrote: Give these a try... Sub DPshiftdown() * With Range("Q16") * * If .NumberFormat = "0 \g" Or .NumberFormat = "General" Then Exit Sub * * .NumberFormat = Replace(.NumberFormat, " \g", "") * * .NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1)) * * If Right(.NumberFormat, 1) = "." Then .NumberFormat = Val(.NumberFormat) * * .NumberFormat = .NumberFormat & " \g" * End With End Sub Sub DPshiftup() * With Range("Q16") * * .NumberFormat = Replace(.NumberFormat, " \g", "") * * If .NumberFormat = "0" Or .NumberFormat = "General" Then * * * .NumberFormat = "0.0 \g" * * Else * * * .NumberFormat = .NumberFormat & "0 \g" * * * .NumberFormat = Replace(.NumberFormat, "0000", "000 0") * * End If * End With End Sub -- Rick (MVP - Excel) "Aaron" wrote in message ... On Dec 9, 1:17 pm, "Rick Rothstein" wrote: Wow thank-you this has been a problem i have been trying to solve for 2 years now, but no one else fully understood my Q and subsequently the suggestions didnt work. I'm glad I was able to be of help. -- Rick (MVP - Excel) Actually, any chance of adding the letter "g" after the zeroes so it goes 0.000 0 g? That would solve the other question I posted. If not how could I achieve this? TIA Aaron. Excellent, thanks heaps. Works great! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage Increase/Decrease | Excel Discussion (Misc queries) | |||
increase/decrease decimal | Excel Worksheet Functions | |||
% of increase or decrease | Excel Discussion (Misc queries) | |||
How do I set a cell that can increase but never decrease? | Excel Discussion (Misc queries) | |||
Value Increase/Decrease/No Change | Excel Worksheet Functions |