Increase & Decrease Decimal Place

A

Aaron

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.
 
R

Rick Rothstein

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
 
A

Aaron

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 ExitSub
    .NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1))
    If Right(.NumberFormat, 1)= "." Then .NumberFormat = Val(.NumberFormat)
  End With
End Sub

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.
 
R

Rick Rothstein

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.
 
A

Aaron

I'm glad I was able to be of help.

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.
 
R

Rick Rothstein

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)


I'm glad I was able to be of help.

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.
 
A

Aaron

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)




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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Formatting 2
Excel VBA Formula Help 4
DP jog macro. 5
.numberformat Q 4
Number Format 4
Macro for Shading and Unshading 4
Date Stamp with protection 6
change event question 3

Top