Hi,
Wondering if anyone can help with 2 macros I have that work but both have kinks that I would like to check
1) Number format macro
................ Selection.NumberFormat = "#,##0;[Red](#,##0);-;"
This macro is for formatting numbers in a way I like. However if I inadvertantly use it on text, it will not show the text. I just need to change something small but not sure what it is - can anyone shed any light please
2) Round Formula macro
................
Sub Round_Add() <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = <o></o>
'// Adds =ROUND( ) to Formulas in current Selection <o></o>
'// May have more than one area selected <o></o>
'// Avoids adding Round to beginning if already used <o></o>
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = <o></o>
<o> </o>
Dim BigRng As Range <o></o>
Dim Rng As Range <o></o>
Dim Cell As Range <o></o>
Dim Equ As String <o></o>
Dim iRound As Integer <o></o>
<o> </o>
On Error Resume Next <o></o>
If Not (TypeOf Selection Is Range) Then Exit Sub <o></o>
Set BigRng = Selection.SpecialCells(xlFormulas) <o></o>
If BigRng Is Nothing Then Exit Sub <o></o>
<o> </o>
iRound = InputBox("Round to how many digits?", , 0) <o></o>
<o> </o>
Equ = "=Round(#,n_)" <o></o>
Equ = Replace(Equ, "n_", iRound) <o></o>
<o> </o>
For Each Rng In BigRng.Areas <o></o>
For Each Cell In Rng.Cells <o></o>
If Not Cell.Formula Like "=ROUND(*" Then <o></o>
Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2)) <o></o>
End If <o></o>
Next Cell <o></o>
Next Rng <o></o>
End Sub <o></o>
<o></o>
<o>This macro is used to put a round formula onto any formula. However the major problem is that if I do not select at least 2 cells, it applies the macro to the WHOLE sheet which is not what I need.</o>
<o></o>
<o>Would appreciate any help</o>
<o></o>
<o>Cheers</o>
<o></o>
<o>GusC</o>
Wondering if anyone can help with 2 macros I have that work but both have kinks that I would like to check
1) Number format macro
................ Selection.NumberFormat = "#,##0;[Red](#,##0);-;"
This macro is for formatting numbers in a way I like. However if I inadvertantly use it on text, it will not show the text. I just need to change something small but not sure what it is - can anyone shed any light please
2) Round Formula macro
................
Sub Round_Add() <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>'// = = = = = = = = = = = = = = = = = = = = = = = = = = = <o
></o>'// Adds =ROUND( ) to Formulas in current Selection <o
></o>'// May have more than one area selected <o
></o>'// Avoids adding Round to beginning if already used <o
></o>'// = = = = = = = = = = = = = = = = = = = = = = = = = = = <o
></o><o
> </o>Dim BigRng As Range <o
></o>Dim Rng As Range <o
></o>Dim Cell As Range <o
></o>Dim Equ As String <o
></o>Dim iRound As Integer <o
></o><o
> </o>On Error Resume Next <o
></o>If Not (TypeOf Selection Is Range) Then Exit Sub <o
></o>Set BigRng = Selection.SpecialCells(xlFormulas) <o
></o>If BigRng Is Nothing Then Exit Sub <o
></o><o
> </o>iRound = InputBox("Round to how many digits?", , 0) <o
></o><o
> </o>Equ = "=Round(#,n_)" <o
></o>Equ = Replace(Equ, "n_", iRound) <o
></o><o
> </o>For Each Rng In BigRng.Areas <o
></o>For Each Cell In Rng.Cells <o
></o>If Not Cell.Formula Like "=ROUND(*" Then <o
></o>Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2)) <o
></o>End If <o
></o>Next Cell <o
></o>Next Rng <o
></o>End Sub <o
></o><o
></o> <o
>This macro is used to put a round formula onto any formula. However the major problem is that if I do not select at least 2 cells, it applies the macro to the WHOLE sheet which is not what I need.</o><o
></o> <o
>Would appreciate any help</o><o
></o> <o
>Cheers</o><o
></o> <o
>GusC</o>
