How to give count of lines before all 5 values pass??

B

Brian

Hello, this is my first visit and post in this forum. I was told that thisis the place to ask. So here we go:

I have five cells per line with numbers in each cell. There are hundreds of lines in the file(on same page). What I'm looking to findout are the total lines searched before I find all five values pass a "greater than or equal to" each cell in the line in question.

Here is an example:
A1 = 15
B1 = 3
C1 = 18
D1 = 8
E1 = 2

So somehow the formula or however to do this, would count howmany lines would fail until I reach a line with :
A? is >or= 15 and
B? is >or= 3 and
C? is >or= 18 and
D? is >or= 8 and
E? is >or= 2
ALL must pass on the same line(?). The result would go into another cell (say F1). Then repeat the same process for each line in the file.

I have no clue as to where to start. I have never used VBA or macros before. Maybe I'm just into something that is way over my head, but help would be appreciated.

Thanks for your time!!!!
Brian
 
H

h2so4

Brian explained :
Hello, this is my first visit and post in this forum. I was told that this
is the place to ask. So here we go:

I have five cells per line with numbers in each cell. There are hundreds of
lines in the file(on same page). What I'm looking to findout are the total
lines searched before I find all five values pass a "greater than or equal
to" each cell in the line in question.

Here is an example:
A1 = 15
B1 = 3
C1 = 18
D1 = 8
E1 = 2

So somehow the formula or however to do this, would count howmany lines would
fail until I reach a line with : A? is >or= 15 and
B? is >or= 3 and
C? is >or= 18 and
D? is >or= 8 and
E? is >or= 2
ALL must pass on the same line(?). The result would go into another cell
(say F1). Then repeat the same process for each line in the file.

I have no clue as to where to start. I have never used VBA or macros before.
Maybe I'm just into something that is way over my head, but help would be
appreciated.

Thanks for your time!!!!
Brian
Click to expand...

a solution with a macro

Sub test()
i = 2
c = 0
While Cells(i, 1) <> ""
For j = 1 To 5
If Cells(1, j) > Cells(i, j) Then j = 99
Next j
If j > 10 Then
c = c + 1
Else
Cells(i, 6) = c
c = 0
End If
i = i + 1
Wend
End Sub
 
C

Claus Busch

Hi Brian,

Am Wed, 22 Jan 2014 10:40:26 -0800 (PST) schrieb Brian:
A1 = 15
B1 = 3
C1 = 18
D1 = 8
E1 = 2

So somehow the formula or however to do this, would count howmany lines would fail until I reach a line with :
A? is >or= 15 and
B? is >or= 3 and
C? is >or= 18 and
D? is >or= 8 and
E? is >or= 2
Click to expand...

for the exact values try:
=MATCH(15&"*"&3&"*"&18&"*"&8&"*"&2,A1:A100&"*"&B1:B100&"*"&C1:C100&"*"&D1:D100&"*"&E1:E100,0)-1
and enter the array formula with CTRL+Shift+Enter

else try following function in a standard module and call it into the
sheet e.g. with:
=myCount(A1:E20)

Function myCount(myRng As Range) As Long
Dim myArr As Variant
Dim i As Long, j As Long, n As Long

myArr = Array(15, 3, 18, 8, 2)
i = 1
Do
n = 0
For j = 1 To 5
If Cells(i, j) >= myArr(j - 1) Then
n = n + 1
End If
Next
If n <> 5 Then
myCount = myCount + 1
i = i + 1
Else
Exit Do
End If
Loop While i < myRng.Rows.Count
End Function


Regards
Claus B.
 
B

Brian

Hey Thanks guys!!! I appreciate the quick responses.

This stuff is beyond my grasp of things. I don't even know where to put the code you guys took the time to write. I have no clue whatsoever what to do with it.

I'm sorry for wasting your time!!! Thanks for trying, but i'm not qualified. It would be nice to learn though, but I don't think this is the place for wasting your time.


THANKS Again!!!
Brian
 
N

Norman Jones

Hey Thanks guys!!! I appreciate the quick responses.

This stuff is beyond my grasp of things. I don't even
know where to put the code you guys took the time to write.
I have no clue whatsoever what to do with it.

I'm sorry for wasting your time!!! Thanks for trying, but
i'm not qualified. It would be nice to learn though, but I
don't think this is the place for wasting your time.


THANKS Again!!!
Brian
Click to expand...

Hi Brian,

Don't be discouraged so easily!

With the suggested code, try:

Alt-F11 to open the VBA editor
Menu: Insert | Module
Where the cursor is flashing, choose Edit | Paste
Alt-Q to close the VBA editor and return to Excel

In the case of the solution proposed by H2SO4:
Alt-F8 to open the Macro window
Select Test | Run

In the case of Claus's function, this may be used in your workbook like
a native Excel function, as Claus showed with his example usage.

===
Regards,
Norman


===
Regards,
Norman
 
B

Brian

Thanks Norman!! I will give it a try. I will post in here again and let you know how I made out.
 
B

Brian

Thanks Norman!! I will give it a try. I will post in here again and let you know how I made out.
Click to expand...

Nothing is happening. I opened the macro window and saw the TEST macro that I pasted:Sub test()
i = 2
c = 0
While Cells(i, 1) <> ""
For j = 1 To 5
If Cells(1, j) > Cells(i, j) Then j = 99
Next j
If j > 10 Then
c = c + 1
Else
Cells(i, 6) = c
c = 0
End If
i = i + 1
Wend
End Sub

Then I selected run, but nothing is happening???
 
N

Norman Jones

Nothing is happening. I opened the macro window and saw the TEST macro that I pasted:Sub test()
i = 2
c = 0
While Cells(i, 1) <> ""
For j = 1 To 5
If Cells(1, j) > Cells(i, j) Then j = 99
Next j
If j > 10 Then
c = c + 1
Else
Cells(i, 6) = c
c = 0
End If
i = i + 1
Wend
End Sub

Then I selected run, but nothing is happening???
Click to expand...
Hi Brian,

I have not tested H2So4's code, but the code is designed to write 0 or
1 responses in column F in qualifying rows.


===
Regards,
Norman
 
B

Brian

Hi Brian,



I have not tested H2So4's code, but the code is designed to write 0 or

1 responses in column F in qualifying rows.





===

Regards,

Norman
Click to expand...

Thanks for spending some time to try and teach me something that I now find very interesting!!!!!!! Are there any good books to read that will teach me about this stuff?????
 
N

Norman Jones

Thanks for spending some time to try and teach me something that I
now find very interesting!!!!!!! Are there any good books to read
that will teach me about this stuff?????
Click to expand...

Hi Brian,

John Walkenbach is the widely acknowledged author of choice and I can
give an unqualified endorsement for *any* of his books.

See:

I would suggest JW's *Excel VBA Programming For Dummies* and, later, as
your proficiency increases, try any version of *Excel Power Programming
With VBA*

You should be able to find good second-hand versions cheaply at Amazon.


===
Regards,
Norman
 
B

Brian

I failed to supply the link:



http://spreadsheetpage.com/





===

Regards,

Norman
Click to expand...

I got the dummies book and another on VBA and Macros. It seems I am way over my head with this stuff. I don't know what to look for in the books. I looked at looping, validate cells, IF command, I don't know what I'm doing!!!!
 
N

Norman Jones

I got the dummies book and another on VBA and Macros. It seems
I am way over my head with this stuff. I don't know what to look for
in the books. I looked at looping, validate cells, IF command, I don't
know what I'm doing!!!!
Click to expand...

Hi Brian,

The secret is small, consistent steps!

Think of VBA as a language or a dialect (which it is): If I gave you a
book on the French language, you would not expect to develop fluent
spoken French and have an extensive knowledge of the grammar overnight.
More realistically, you might set yourself the target of initially
learning some basic grammar and developing a vocabulary sufficient, say,
to be able to enable you to go on holiday to France and successfully
order food and book a hotel room. Having achieved such a goal,
continuing your small steps, you would increase your knowledge of French
grammar and extend your vocabulary to the point where you could
enjoyably read a book in french or watch a French film.

Can I suggest, therefore, that you start at the first chapter in John
Walkenbach's VBA Dummies book and, initially, take small, bite size
chunks; read the book with Excel open on your computer and do not be
afraid to experiment - a great deal of understanding can ensue from
initial errors and their resolution. Bear in mind, also, that you are
not alone: you always have available to you the resource of this NG
where there are many only too happy to assist you overcome problems and
queries!

Remember that, whilst a language - any language - may appear daunting at
the outset, every baby learns at least one!

I would remind you of the first sentence in my first response: *Don't be
discouraged so easily!*

===
Regards,
Norman
 
G

GS

Very kind of you to say so, Gary!

Let us hope that it re-enthuses Brian!


===
Regards,
Norman
Click to expand...

@NORMAN
Well.., if Brian's anything like I was when I started with VBA and JW's
books, I'm sure it'll hook him in like it did me (and many others)! I
guess he's also caught on that we'll (hopefully) be here to help him
along!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
B

Brian

Hi Brian,



The secret is small, consistent steps!



Think of VBA as a language or a dialect (which it is): If I gave you a

book on the French language, you would not expect to develop fluent

spoken French and have an extensive knowledge of the grammar overnight.

More realistically, you might set yourself the target of initially

learning some basic grammar and developing a vocabulary sufficient, say,

to be able to enable you to go on holiday to France and successfully

order food and book a hotel room. Having achieved such a goal,

continuing your small steps, you would increase your knowledge of French

grammar and extend your vocabulary to the point where you could

enjoyably read a book in french or watch a French film.



Can I suggest, therefore, that you start at the first chapter in John

Walkenbach's VBA Dummies book and, initially, take small, bite size

chunks; read the book with Excel open on your computer and do not be

afraid to experiment - a great deal of understanding can ensue from

initial errors and their resolution. Bear in mind, also, that you are

not alone: you always have available to you the resource of this NG

where there are many only too happy to assist you overcome problems and

queries!



Remember that, whilst a language - any language - may appear daunting at

the outset, every baby learns at least one!



I would remind you of the first sentence in my first response: *Don't be

discouraged so easily!*



===

Regards,

Norman
Click to expand...

Norman, Thanks for the inspiration!!! I have taken your words to heart cause you seem like a genuine person. Your post was very well written and you made an excellent point. The best way to TRUELY learn about VBA and MACROS is to start from the beginning and to learn it as a new language. I have started reading the dummies book from page one. I appreciate you takingthe time to post such an inspiring post. I WILL LEARN this stuff, by taking the baby steps. I believe this is the best way, just as you stated.

And, Norman and Gary, If I do need help with any questions along the way, Iwill be leaning on you guys for the proper direction. I'm sure you guys won't have a problem helping me along the way. THANKS for that!!!!

It will take me some time to get to where I would like to be, and learn about the type of code that I'm interested in. I have not much reading time during the week, but hope to make up for that on the weekend. Thanks Again!!!!

Now Learning!!
Brian
 
G

GS

Norman, Thanks for the inspiration!!! I have taken your words to
heart cause you seem like a genuine person. Your post was very well
written and you made an excellent point. The best way to TRUELY
learn about VBA and MACROS is to start from the beginning and to
learn it as a new language. I have started reading the dummies book
from page one. I appreciate you taking the time to post such an
inspiring post. I WILL LEARN this stuff, by taking the baby steps.
I believe this is the best way, just as you stated.

And, Norman and Gary, If I do need help with any questions along the
way, I will be leaning on you guys for the proper direction. I'm
sure you guys won't have a problem helping me along the way. THANKS
for that!!!!

It will take me some time to get to where I would like to be, and
learn about the type of code that I'm interested in. I have not much
reading time during the week, but hope to make up for that on the
weekend. Thanks Again!!!!

Now Learning!!
Brian
Click to expand...

Nice attitude!! Best wishes in your endeavors...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

cells with one to multiple lines 1
Cells with varying number of lines 1
Rows with cells of varying number of lines 5
Excel How to change lines in graph to individual colours 0
Formula to Identify groups of numbers to be added. 4
PowerPoint Transition/animation to pass from paragraph to lines 0
Concatenate multiple lines 2
Count Values in a Range, Not Formulas 2

Top