Solved

Defining a variable "Count"

Posted on 2014-12-17
10
70 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 47

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 47

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 47

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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