Solved

Defining a variable "Count"

Posted on 2014-12-17
10
58 Views
Last Modified: 2014-12-23
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

0
Comment
Question by:RWayneH
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40505589
'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

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40505865
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40505919
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

0
 

Author Comment

by:RWayneH
ID: 40506317
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40506351
Try the code and see if it works.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40506423
Hi,
no need of loop

lngLastRow = Range("E" & Rows.Count).End(xlUp).Row
ColumnDoesNotHaveZero = Evaluate("NOT(-SUM(--(E1:E" & lngLastRow & "=0)))")

Replace the bolded E with the column where the data has no 0 values, if there isn't one then use UsedRange method after saving of course

lngLastRow = ActiveSheet.UsedRange.Rows.Count
ColumnDoesNotHaveZero = Evaluate("NOT(-SUM(--(E1:E" & lngLastRow & "=0)))")

Regards
0
 

Author Comment

by:RWayneH
ID: 40509397
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.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40510626
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
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40511498
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
0
 

Author Closing Comment

by:RWayneH
ID: 40514802
Thanks for the help with this.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now