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
ffice
ffice" /><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




'// = = = = = = = = = = = = = = = = = = = = = = = = = = = <o


'// Adds =ROUND( ) to Formulas in current Selection <o


'// May have more than one area selected <o


'// Avoids adding Round to beginning if already used <o


'// = = = = = = = = = = = = = = = = = = = = = = = = = = = <o


<o


Dim BigRng As Range <o


Dim Rng As Range <o


Dim Cell As Range <o


Dim Equ As String <o


Dim iRound As Integer <o


<o


On Error Resume Next <o


If Not (TypeOf Selection Is Range) Then Exit Sub <o


Set BigRng = Selection.SpecialCells(xlFormulas) <o


If BigRng Is Nothing Then Exit Sub <o


<o


iRound = InputBox("Round to how many digits?", , 0) <o


<o


Equ = "=Round(#,n_)" <o


Equ = Replace(Equ, "n_", iRound) <o


<o


For Each Rng In BigRng.Areas <o


For Each Cell In Rng.Cells <o


If Not Cell.Formula Like "=ROUND(*" Then <o


Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2)) <o


End If <o


Next Cell <o


Next Rng <o


End Sub <o


<o


<o


<o


<o


<o


<o


<o


<o

