Bob Phillips
12/15/2006 1:47:00 PM
It might be a formula returning an error, maybe try
Sub ForceCellsToNegative()
Dim Rng As Range
Dim rCell As Range
Set Rng =
Range("B105,B109,B61,B62,B64,B65,B67,B68,G7,G8,G20:G31,G33:G44,G46:G57,G77:G79,G82:G101,G103:G104,G107:G108")
For Each rCell In Rng.Cells
With rCell
If IsNumeric(.Value) Then
If .Value <> "" Then
..Value = -Abs(.Value)
..Font.Name = "Arial"
..Font.Bold = True
..NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End If
End If
If Not Rng Is Nothing Then
Else
Exit Sub
End If
Abs takes the absloute value of a number, Abs(11) is 11, Abs(-11) is 11,
so -Abs will return the negative of any number.
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"JOUIOUI" <JOUIOUIM@Discussions.microsoft.com> wrote in message
news:1841E725-ABF8-43F3-A8CA-09015599BC73@microsoft.com...
> I'm using this code to force any currency values in the selected cells to
> negative. Most of the time the codes runs great, but other times it
> doesn't,
> I get a "run time error -13 Type Mismatch". It seems to stop running when
> it
> hits cell G92. Any ideas what could be wrong.
>
> Also I copied this code from this site. I understand most of it but what
> does the text Abs mean in the code ".Value = -Abs(.Value)"? Thanks for
> your
> assistance and happy holidays.
>
> Sub ForceCellsToNegative()
>
> Dim Rng As Range
> Dim rCell As Range
>
> Set Rng =
> Range("B105,B109,B61,B62,B64,B65,B67,B68,G7,G8,G20:G31,G33:G44,G46:G57,G77:G79,G82:G101,G103:G104,G107:G108")
>
> For Each rCell In Rng.Cells
> With rCell
> If .Value <> "" Then
> .Value = -Abs(.Value)
> .Font.Name = "Arial"
> .Font.Bold = True
> .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
> End If
> If Not Rng Is Nothing Then
>
> Else
> Exit Sub
> End If
>
> End With
> Next rCell
>
> End Sub
>
>
>
>