Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Troble with Code
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,G1 07: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" wrote in message ... 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,G1 07: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Troble with Code | Excel Programming | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |