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?

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

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

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

Got it.

Thanks Tom!!!

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

You are welcome ...

Carim

You are welcome ...

Carim