Macro calculating incorrectly.. what can cause this?

[Celt]

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?

 

[Carim]

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

 

[Celt]

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!!

 

[Tom Ogilvy]

nonblank = Evaluate("CountA(rng3)")

would need to be

nonblank = Evaluate("CountA(" & rng3.Address & ")")

or
nonblank = Application.CountA(rng3)

 

[Celt]

Got it.

Thanks Tom!!!

 

[Celt]

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

 

[Carim]

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

 

[Celt]

Thanks very much Carim!!

I think I understand it know. I'll give your coding a try.

 

[Carim]

You are welcome ...

Carim

 

[Carim]

You are welcome ...

Carim