Solved

Defining a variable "Count"

Posted on 2014-12-17
10
68 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 46

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 69

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 46

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 46

Expert Comment

by:Martin Liss
ID: 40506351
Try the code and see if it works.
0
 
LVL 50

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 50

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 50

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

856 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