Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
TIA for any help offered! I have a macro in which I am looking to see if a range is blank (you may have seen it in my other posts to this site). In the macro, I have tried the following arguements (I have tested all rng(s) and variables in the macro and know they work): For Each cell In rng1 If IsEmpty(cell) Then Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, icol).Offset(0, -2)) nonblank = 0 With rng3 On Error Resume Next nonblank = .SpecialCells(xlCellTypeFormulas).Cells.Count nonblank = .SpecialCells(xlCellTypeConstants).Cells.Count End With If nonblank 0 Then cell.Interior.ColorIndex = 6 End If End If Next cell and I have tried... For Each cell In rng1 If IsEmpty(cell) Then Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, icol).Offset(0, -2)) nonblank = 0 With rng3 On Error Resume Next nonblank = Evaluate("CountA(rng3)") End With If nonblank < 0 Then cell.Interior.ColorIndex = 6 End If End If Next cell Both of these statements seem to work only some of the time. There are instances where rng3 holds no data but the macro calculates that data is present. I have checked the cells in question using the functions COUNTA and ISBLANK, both of which indicate the cell is blank. Does it have something to do with the statements I used? -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=530987 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
Hi Celt,
Depending on how users operate ... some use the space bar to delete cell contents ... To be on the safe side, you 'd be better off using: If Len(yourcell) = 0 Then ... HTH Cheers Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
Hi Carim. Hope all is well with you. So deleting with the spacebar leaves something in the cell that the macro picks up as a "constant" or Data? Good to know. Thanks very much!! -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=530987 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
nonblank = Evaluate("CountA(rng3)")
would need to be nonblank = Evaluate("CountA(" & rng3.Address & ")") or nonblank = Application.CountA(rng3) -- Regards, Tom Ogilvy "Celt" wrote: TIA for any help offered! I have a macro in which I am looking to see if a range is blank (you may have seen it in my other posts to this site). In the macro, I have tried the following arguements (I have tested all rng(s) and variables in the macro and know they work): For Each cell In rng1 If IsEmpty(cell) Then Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, icol).Offset(0, -2)) nonblank = 0 With rng3 On Error Resume Next nonblank = .SpecialCells(xlCellTypeFormulas).Cells.Count nonblank = .SpecialCells(xlCellTypeConstants).Cells.Count End With If nonblank 0 Then cell.Interior.ColorIndex = 6 End If End If Next cell and I have tried... For Each cell In rng1 If IsEmpty(cell) Then Set rng3 = Range(Cells(cell.Row, 4), .Cells(cell.Row, icol).Offset(0, -2)) nonblank = 0 With rng3 On Error Resume Next nonblank = Evaluate("CountA(rng3)") End With If nonblank < 0 Then cell.Interior.ColorIndex = 6 End If End If Next cell Both of these statements seem to work only some of the time. There are instances where rng3 holds no data but the macro calculates that data is present. I have checked the cells in question using the functions COUNTA and ISBLANK, both of which indicate the cell is blank. Does it have something to do with the statements I used? -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=530987 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
Got it. Thanks Tom!!! -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=530987 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
Hi Tom, Hoping you could help me with this one too. I'm trying to piggybac off of the guidance you gave me earlier. All my ranges work. I have Dim'd "cell" as Range. For Each cell In rng1 If IsNumeric(cell) = True Then nodecimal = Evaluate("Trunc(" & cell.Address & ")-(" & cell.Address ")") If nodecimal < 0 Then cell.Interior.ColorIndex = 6 End If End If Next cell End With When I "run to cursor" test the macro cell=Nothing. I think I am usin this statement inappropriately. Can you point me in the right directio to the correct method? Thanks in advance for any help given -- Cel ----------------------------------------------------------------------- Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941 View this thread: http://www.excelforum.com/showthread.php?threadid=53098 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
Hi Celt,
Hope everything is going fine, also with you ... Regarding your problem, I believe the problem is with the way you have declared the variable nodecimal ... with Dim nodecimal As Double ... it should be fine ... In addition, my personaI preference for the formula would be : nodecimal = cell.Value - Int(cell.Value) HTH Cheers Carim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
Thanks very much Carim!! :) I think I understand it know. I'll give your coding a try. -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=530987 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
You are welcome ...
Carim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro calculating incorrectly.. what can cause this?
You are welcome ...
Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel is Calculating Incorrectly | Excel Worksheet Functions | |||
Calculating Macro | Excel Discussion (Misc queries) | |||
Macro for calculating | Excel Discussion (Misc queries) | |||
insert column macro will incorrectly add another value | Excel Discussion (Misc queries) | |||
Calculating Commissions Macro | Excel Programming |