Hi, Using XL 2000

My worksheet contains numerical values in Cols A, B, C & D.
Cols E & F Contain Contact Names. There are over 100 rows of data

In my userform, a user selects a numerical value in Combobox1 and combobox2

On a click event, I would like the following to execute:-

The value selected in CB1 is checked against every row and where it is
greater than value in Col A AND less than the value in Col B, reports True

The value selected in CB2 is checked against every row and where it is
greater than value in Col C AND less than the value in Col D, reports True

Only where 2 True's are present, I need the values in Cols e & F in the
respective row that meets the true condition to be added to ListBox1.

I hope i've manged to explain my problem.
Any VBA ideas welcome.

Thank you.

Paul

Dim cell as Range
for each cell in Range("A1:A100)
if cdbl(cb1.value) = cell and cdbl(cb1.value) <= cell(1,2) _
and cdbl(cb2.value) = cell(1,3) and cdbl(cb2.value) <= _
cell(1,4) then
listbox1.AddItem cell(1,5)
listbox1.list(listbox1.listcount-1,1) = cell(1,6)
end if
Next

--
Regards,
Tom Ogilvy


"Paulc" wrote in message
...
Hi, Using XL 2000

My worksheet contains numerical values in Cols A, B, C & D.
Cols E & F Contain Contact Names. There are over 100 rows of data

In my userform, a user selects a numerical value in Combobox1 and
combobox2

On a click event, I would like the following to execute:-

The value selected in CB1 is checked against every row and where it is
greater than value in Col A AND less than the value in Col B, reports True

The value selected in CB2 is checked against every row and where it is
greater than value in Col C AND less than the value in Col D, reports True

Only where 2 True's are present, I need the values in Cols e & F in the
respective row that meets the true condition to be added to ListBox1.

I hope i've manged to explain my problem.
Any VBA ideas welcome.

Thank you.

Paul

Hi, this CountData is a gem that Tom posted in many other posts. This
will check against all of the values in column A.

Dim cell as Range
Dim CountData&
CountData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
for each cell in Range("A1:A" & CountData)
if cdbl(cb1.value) = cell and cdbl(cb1.value) <= cell(1,2) _
and cdbl(cb2.value) = cell(1,3) and cdbl(cb2.value) <= _
cell(1,4) then
listbox1.AddItem cell(1,5)
listbox1.list(listbox1.listcount-1,1) = cell(1,6)
end if
Next

Tom, you are the man. I learn something new from all of your posts.
Lonnie M.

Many thanks tom.
Perfection as usual!

Regards,
Paul

"Tom Ogilvy" wrote:

Dim cell as Range
for each cell in Range("A1:A100)
if cdbl(cb1.value) = cell and cdbl(cb1.value) <= cell(1,2) _
and cdbl(cb2.value) = cell(1,3) and cdbl(cb2.value) <= _
cell(1,4) then
listbox1.AddItem cell(1,5)
listbox1.list(listbox1.listcount-1,1) = cell(1,6)
end if
Next

--
Regards,
Tom Ogilvy


"Paulc" wrote in message
...
Hi, Using XL 2000

My worksheet contains numerical values in Cols A, B, C & D.
Cols E & F Contain Contact Names. There are over 100 rows of data

In my userform, a user selects a numerical value in Combobox1 and
combobox2

On a click event, I would like the following to execute:-

The value selected in CB1 is checked against every row and where it is
greater than value in Col A AND less than the value in Col B, reports True

The value selected in CB2 is checked against every row and where it is
greater than value in Col C AND less than the value in Col D, reports True

Only where 2 True's are present, I need the values in Cols e & F in the
respective row that meets the true condition to be added to ListBox1.

I hope i've manged to explain my problem.
Any VBA ideas welcome.

Thank you.

Paul