Is there an easy way to tell if array.column(x) has any contents?

[ker_01]

I am cycling through a large array, and pulling a value from each
'row'/order. I then check it against a second array to see if that order type
has further criteria to identify it as a 'good' or 'bad' order; if so, I
check whether it is a good or bad order.

I now realize that I also need to check to see if the second array's
matching 'column' contains any entries at all (in addition to looking for a
matching entry). Based on this code, I added a comment where I need to check
to see if PEExclude.Columns(PELimited) has any values at all.

I'm hoping there is an easy way; uBound doesn't help because it returns the
size of the whole array. Another option would be to just check the first
value of the array to see if it is blank, but that leaves room for human
error, if the workbook users skip the first row for any reason and put data
in a subsequent row.

I welcome any ideas!
Thanks,
Keith


PEIndicator = 0 'default assumption is that there is noproduct-specific info

'Use ALD code to see if there is product-specific information
'Error = no special criteria, so PEIndicator would remain zero
PELimited = Application.Match(AllRecd(i, 20), PEFamily, False)

If Not (IsError(PELimited)) Then
'check to see if this particular purchase is 'good'
PEP = Application.Match(AllRecd(i, 18), PEInclude.Columns(PELimited),
False)
If IsError(PEP) Then
'If it isn't a good one, check to see if it is a bad one
PEN = Application.Match(AllRecd(i, 18),
PEExclude.Columns(PELimited), False)
'Also check to make sure there are >0 bad ones listed
'?? how do I check to see if array contains any values here??
If IsError(PEN) Then
PEIndicator = 3 '*ignore* this product; not in good or bad list
Else
PEIndicator = 2 'bad/undesirable based on SDS item code
End If
Else
PEIndicator = 1 'good item
End If
End If

 

[Otto Moehrbach]

Keith
Use an IF statement with the worksheet COUNTA function. COUNTA counts
all the occupied cells in the range. If you want to see if Column E has any
entries, use:
If Application.COUNTA(Columns("E:E") > 0 Then
If you know the column has a header always, then change the 0 to a 1. HTH
Otto

 

[Bob Phillips]

Try something like

MsgBox
Application.CountA(Application.Index(Application.Transpose(PEExclude), 2))