Solved

Defining a variable "Count"

Posted on 2014-12-17
10
65 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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 49

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 49

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 49

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

821 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