Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Defining a variable "Count"

Posted on 2014-12-17
10
Medium Priority
?
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 49

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 71

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 49

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 49

Expert Comment

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

Accepted Solution

by:
Rgonzo1971 earned 2000 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 53

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 53

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

610 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