Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stumped
Hi
Im reading a collection of worksheets and printing totals under eac classification in a final worksheet (Sheet1) which is at the end of th tab of worksheets. So I have template - 163 sheets to read - totals (Sheet1) Im reading N93 from each cell, which is a percentage ranging from -500 to 1300%. For some reason, this is giving me an error. When I go to the debug, i seems as though its gets confused when numRatios = 3.016 Below is the code, maybe fresh eyes will be able to see hte error.. Thanks for the help! ' Counts number of most adverse ratios Count = Worksheets.Count ' exclude totals sheet Amount = Count - 1 numRatio = 0 numLess0 = 0 num0to100 = 0 num100to150 = 0 num150to175 = 0 num175to200 = 0 numMore200 = 0 ' start at second worksheet, exclude template For i = 2 To Amount numRatio = CDec(Worksheets(i).Range("N93")) If numRatio < 0 Then numLess0 = numLess0 + 1 ElseIf numRatio = 0 And numRatio <= 1 Then num0to100 = num0to100 + 1 ElseIf numRatio 1 And numRatio <= 1.5 Then num100to150 = num100to150 + 1 ElseIf numRatio 1.5 And numRatio <= 1.75 Then num150to175 = num150to175 + 1 ElseIf numRatio 1.75 And numRatio <= 2 Then num175to200 = num175to200 + 1 ElseIf numRatio 2 Then numMore200 = numMore200 + 1 End If Next ' prints number of scenarios presented Sheet1.Range("D113").Value = numLess0 Sheet1.Range("D114").Value = num0to100 Sheet1.Range("D115").Value = num100to150 Sheet1.Range("D116").Value = num150to175 Sheet1.Range("D117").Value = num175to200 Sheet1.Range("D118").Value = numMore200 Thanks again -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stumped
Okay so i found that hte problem has to do with if the cell has #NUM! i
it. How can I change hte formula =SMALL($I$83:$I$102,1) to be blank and not #NUM! if there are no valued in the range? Thanks -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stumped
=IF(ISERROR(SMALL($I$83:$I$102,1)),"",SMALL($I$83: $I$102,1))
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sheeny " wrote in message ... Okay so i found that hte problem has to do with if the cell has #NUM! in it. How can I change hte formula =SMALL($I$83:$I$102,1) to be blank and not #NUM! if there are no valued in the range? Thanks! --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stumped
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Still stumped | Excel Worksheet Functions | |||
Stumped | Excel Discussion (Misc queries) | |||
Stumped | Excel Discussion (Misc queries) | |||
I'm Stumped | Excel Discussion (Misc queries) | |||
stumped ?? | Excel Worksheet Functions |