asked on

Defining a variable "Count"

In the following code I have run this and there was 4 rows and the count was 14? The previous test was 14, but the current test was 4... How can I reset the "Count" so it reads accurate each time and does not keep what the previous count was. Please advise and thanks.

'Set as True to start.
    ColumnDoesNotHaveZero = True
    Count = ActiveSheet.UsedRange.Rows.Count
'loop thru column E and look for a zero.
For i = 1 To Count Step 1
    If ActiveSheet.Cells(i, 5).Value = 0 Then  '5 is for column E
        ColumnDoesNotHaveZero = False  'it has a zero, set to False
    End If
Next

Open in new window

Martin Liss

'Set as True to start.
    ColumnDoesNotHaveZero = True

'loop thru column E and look for a zero.
For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If ActiveSheet.Cells(i, 5).Value = 0 Then  '5 is for column E
        ColumnDoesNotHaveZero = False  'it has a zero, set to False
        Exit For
    End If
Next

Open in new window

Qlemo

Martin,
that does not change anything in regard of "Count".
a) Count might be needed with later code
b) The loop will still process 14 rows if none of the E cells has a 0

RWayneH,
UsedRange gets adjusted only after you have saved the file. Even that might not help, and there are certain methods to get the real count of rows filled with data, all sounding cumbersome. So I would first try the save.

Martin Liss

that does not change anything in regard of "Count".

You're right- I was just cleaning up his code. But going back and looking at the question makes me think that it's the UsedRange that's the problem, and if so this is better.

Dim lngLastRow as Long
lngLastRow = Range("E1048576").End(xlUp).Row

'Set as True to start.
    ColumnDoesNotHaveZero = True

'loop thru column E and look for a zero.
For i = 1 To lngLastRow
    If ActiveSheet.Cells(i, 5).Value = 0 Then  '5 is for column E
        ColumnDoesNotHaveZero = False  'it has a zero, set to False
        Exit For
    End If
Next

Open in new window

RWayneH

ASKER

This is assuming .xlsx and it is actually a .xls Using static values in code is not my fav. My issue was that column E can have a header in row 1 and a single value in row 2. I was using xlDown and it went all the way down the sheet... Will this working and fix that issue?

Martin Liss

Try the code and see if it works.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

Sorry Martin, but as I was testing this I like the no loop solution that Rgonzo1971 gave. It works good, however I do not understand the syntax of: Evaluate("NOT(-SUM(--(E1:E" & lngLastRow & "=0)))")

How could this be used in different applications, like finding text, or a word in text string? Example would be, how do I find the word "Failed" column K? "Failed" could be by itself or as part of a string. I want to understand this because there are numerous places that I could use this. E1:E" & lngLastRow is the range. & "=0)))") is the zero part.,, Please advise and thanks.

Rgonzo1971

it is like a formula in Excel

=NOT(-SUM(--(E1:E" & lngLastRow & "=0)))
in this case an array formula

(E1:E" & lngLastRow & "=0) creates an array of true or false ( is it 0 or not)
the -- transforms it in 1s and 0s
the sum if there is any which have 0 then more than 0
the not(- to transform in vba true or false

Regards

Rgonzo1971

In this case, you could also use (Not tested)

var = Evaluate("COUNTIF(E1:E" & lngLastRow & ","""=0""")=0)"

or

var = WorksheetFunction.CountIf(Range("E1:E" & lngLastRow), "=0") = 0

Regards

RWayneH

ASKER

Thanks for the help with this.

Defining a variable &quot;Count&quot;

Defining a variable "Count"