Choosing multiple Criteria to populate a textbox.

Yesterday I posted the snippet of code that is listed

below. Surely there must be a seasoned programmer out there that has

an idea of how to tackle this, even if the below code is totally

changed. This is what I want to do using VBA code:

I have 5 columns, filled with data on my Excel worksheet.
I have a form made up. To fill a textbox on my form I want to be able

to take data from the worksheet with the following criteria:

If column (c) value = "admission" AND column (d) value = "Home" and

a checkbox on my form is checked:

Then the count of the rows that have column (c) with the specified

value AND column (D) with the specified value, will be totalled and

placed in the textbox on the form.

Here is a snippet of code I had done which will count and put the

total in the textbox, but I cannot figure out how to add in the other

criteria of the column (c) value = "admission". The way it is now, it

is totalling all rows in column (D) with a value of "Home". I cannot

figure out how to have multiple criteria being met.


Dim Ct As Integer
Ct = 0

If ckHome2 = True Then
Range("d2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = "Home" Then
Ct = Ct + 1
txtHome2.Text = Ct
End If



I have been working on this for weeks to no avail. Can someone

please, please, please, please help me with this. Thanks.

You can do:

If x="admission" And y="home" Then
....
End If

or

If x="admission" Then
If y="home" Then
...
End If
End If

Also, there is no need to select the cell you are evaluating. You can
simply use:
x = Range("C1").Offset(i,0).Value
(where i is a counter incrementing as you step through each row).

Hope that points you in the right direction.

Cheers,
Andrew

alonge wrote:
> Yesterday I posted the snippet of code that is listed
>
> below. Surely there must be a seasoned programmer out there that has
>
> an idea of how to tackle this, even if the below code is totally
>
> changed. This is what I want to do using VBA code:
>
> I have 5 columns, filled with data on my Excel worksheet.
> I have a form made up. To fill a textbox on my form I want to be able
>
> to take data from the worksheet with the following criteria:
>
> If column (c) value = "admission" AND column (d) value = "Home" and
>
> a checkbox on my form is checked:
>
> Then the count of the rows that have column (c) with the specified
>
> value AND column (D) with the specified value, will be totalled and
>
> placed in the textbox on the form.
>
> Here is a snippet of code I had done which will count and put the
>
> total in the textbox, but I cannot figure out how to add in the other
>
> criteria of the column (c) value = "admission". The way it is now, it
>
> is totalling all rows in column (D) with a value of "Home". I cannot
>
> figure out how to have multiple criteria being met.
>
>
> Dim Ct As Integer
> Ct = 0
>
> If ckHome2 = True Then
> Range("d2").Select
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
> If ActiveCell.Value = "Home" Then
> Ct = Ct + 1
> txtHome2.Text = Ct
> End If
>
>
>
> I have been working on this for weeks to no avail. Can someone
>
> please, please, please, please help me with this. Thanks.
>
>
> --
> alonge
> ------------------------------------------------------------------------
> alonge's Profile: http://www.excelforum.com/member.php...o&userid=27874
> View this thread: http://www.excelforum.com/showthread...hreadid=476208

If I understand what you are trying to do (and I may not completely), you
can use the Offset property to read and compare the value in Column C. For
example:

If ActiveCell.Value = "Home" AND ActiveCell.Offset(0,-1).Value = "Admission"
Then Ct = Ct + 1

This is assuming you want your count value to reflect the number of rows
where both "Home" and "Admission" both exist in the same row.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"alonge" <alonge.1ww5ac_1129291511.0871@excelforum-nospam.com> wrote in
message news:alonge.1ww5ac_1129291511.0871@excelforum-nospam.com...

Yesterday I posted the snippet of code that is listed

below. Surely there must be a seasoned programmer out there that has

an idea of how to tackle this, even if the below code is totally

changed. This is what I want to do using VBA code:

I have 5 columns, filled with data on my Excel worksheet.
I have a form made up. To fill a textbox on my form I want to be able

to take data from the worksheet with the following criteria:

If column (c) value = "admission" AND column (d) value = "Home" and

a checkbox on my form is checked:

Then the count of the rows that have column (c) with the specified

value AND column (D) with the specified value, will be totalled and

placed in the textbox on the form.

Here is a snippet of code I had done which will count and put the

total in the textbox, but I cannot figure out how to add in the other

criteria of the column (c) value = "admission". The way it is now, it

is totalling all rows in column (D) with a value of "Home". I cannot

figure out how to have multiple criteria being met.


Dim Ct As Integer
Ct = 0

If ckHome2 = True Then
Range("d2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = "Home" Then
Ct = Ct + 1
txtHome2.Text = Ct
End If



I have been working on this for weeks to no avail. Can someone

please, please, please, please help me with this. Thanks.


--
alonge
------------------------------------------------------------------------
alonge's Profile:
http://www.excelforum.com/member.php...o&userid=27874
View this thread: http://www.excelforum.com/showthread...hreadid=476208

Your code is exactly what I needed to make it work, and now I should be able to complete the whole thing. Thank-you so much, it is greatly appreciated. I did not know how else to contact you, hoping you will read this reply.