Worksheet function not always working?

[Mixphonics]

Hello,

I've written some code to try to count the number of cells that are not empty and save the result as a variable but sometimes the code seems to think that the cells are empty even though they are not.

The code goes through rows 1 at a time and counts the quantity of cells that are not blank and compares them with a couple other values.

The strange thing is if I execute the actual function =COUNTA(with the range that I am selecting below) the COUNTA function works properly by itself in certain problematic sections of the worksheet.

This code below seems to work correctly most of the time but I've found a few instances where it is not properly counting non-blank cells:

Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)).Select

QtyTests = WorksheetFunction.CountA(Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)))

Does anyone have any suggestions as to why my QtyTests variable sometimes is saved as 0 even though there may be some cells that contain values?


Here is the expanded code:

Sub Macro()
'Global Variables
Dim QtyTests As Integer, ReqTests As Integer, InitQty As Integer, Cork As String, Corktype As String
Dim result As Long, x As Integer


'starting value of variable x
x = 0




'select starting position of macro
Sheets("ETS Testing").Select
Range("G3").Select


Do
ReqTests = 0
InitQty = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, 5).Value = "" Then
x = x + 1
End If
'Bale Qty for each grade
If IsNumeric(ActiveCell.Offset(0, 5).Value) Then
InitQty = ActiveCell.Offset(0, 5).Value
Else
x = x + 1
End If
'Corkgrade & type for each lot/grade
Cork = ActiveCell.Offset(0, 1).Value
'define the range of the 20 possible bales tested at ETS's results
Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)).Select
QtyTests = WorksheetFunction.CountA(Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)))
ActiveCell.Offset(0, -8).Select
result = InStr(Cork, "C3") & InStr(Cork, "C2") & InStr(Cork, "PRIMO") & InStr(Cork, "UNIQ")
If InStr(Cork, "2K6") Then GoTo loopsequence Else GoTo CTest


CTest:
If result = 0 Then Corktype = "natural" Else Corktype = "agglomerated"

If Corktype = "agglomerated" Then
If InitQty < 2 Then
ReqTests = 0
ElseIf InitQty <= 15 And InitQty >= 2 Then
ReqTests = 2
ElseIf InitQty <= 25 And InitQty > 15 Then
ReqTests = 3
ElseIf InitQty <= 90 And InitQty > 25 Then
ReqTests = 5
ElseIf InitQty <= 150 And InitQty > 90 Then
ReqTests = 5
ElseIf InitQty <= 280 And InitQty > 150 Then
ReqTests = 8
ElseIf InitQty >= 281 Then
ReqTests = 13
End If
End If


If Corktype = "natural" Then
If InitQty < 2 Then
ReqTests = 0
ElseIf InitQty <= 8 And InitQty >= 2 Then
ReqTests = 2
ElseIf InitQty <= 15 And InitQty > 8 Then
ReqTests = 3
ElseIf InitQty <= 25 And InitQty > 15 Then
ReqTests = 5
ElseIf InitQty <= 50 And InitQty > 25 Then
ReqTests = 8
ElseIf InitQty <= 90 And InitQty > 50 Then
ReqTests = 13
ElseIf InitQty <= 150 And InitQty > 90 Then
ReqTests = 20
End If
End If

loopsequence:
If ReqTests <= QtyTests Then
If IsNumeric(InitQty) Then
ActiveCell.Value = "OK"
End If
Else
ActiveCell.Value = "ALERT!!!!"
End If



Loop Until x > 500


MsgBox ReqTests





End Sub

 

[Mixphonics]

Can anyone confirm that the correct way to call the function COUNTA in excel vba is:


WorksheetFunction.CountA(Range())

 

[Jerry Sullivan]

Can anyone confirm that the correct way to call the function COUNTA in excel vba is:

WorksheetFunction.CountA(Range())

Yes, that is one correct way to call the function as in:

ReturnVal=WorksheetFunction.CountA(Range("A2:F2"))

One possible explanation for your unexpected results is that you might not be referencing the intended range.

Your code will be clearer and more efficient if you can reference the ranges without the use of
.Select, .Selection and ActiveCell to step through your range. Instead, reference the ranges using variables in relation to a known reference.

That might not be the cause of the problem you describe, but if you can rewrite your code to eliminate the Selecting of cells, it will be easier to debug.