PDA

View Full Version : Finding a Value



headworth
09-23-2009, 11:22 AM
I need some help with code

I am using Excel 2003
I have a worksheet with cells range A3:AF31 I have this code to find all H in the cells but it only finds H in one row
How can I change the code to look at all the row?

Dim cell, rng1 As Excel.Range

Set rng1 = Sheet1.Range("A3:AF31")

Dim count As Long
count = 0

For Each cell In rng1
If cell = "H" Then
count = count + 1
End If
Next
UserForm1.TextBox2.Text = count

Bob Phillips
09-23-2009, 11:25 AM
Dim rng1 As Excel.Range
Dim count As Long

Set rng1 = Sheet1.Range("A3:AF31")

count = Application.Countif(rng1, "H")
UserForm1.TextBox2.Text = count

headworth
09-23-2009, 12:04 PM
Thank you for your help

If I have names in column A3 to A20 how can i get the code to only find H
to the name I select in my combobox?

Bob Phillips
09-23-2009, 01:18 PM
Would that be multiple slections from a combobox, or just 1?

headworth
09-23-2009, 01:30 PM
It would be multiple Selections

If I select a name it is giving me all the H's in all the rows
when i am trying to only get the named row of H to that one name
Hope this helps

mdmackillop
09-23-2009, 02:12 PM
Private Sub ComboBox1_Change()
Dim rng1 As Excel.Range
Dim count As Long

Set rng1 = Columns(1).Find(ComboBox1).Resize(, 32)

count = Application.CountIf(rng1, "H")
MsgBox count
End Sub

Bob Phillips
09-23-2009, 02:12 PM
Sorry, that seems to be contradictory to my reading.

Do you want to count the number of H's for all selected names, or just one.

headworth
09-23-2009, 02:31 PM
Sorry I need to select one name at a time in combobox and find all H's in that
row only

Bob Phillips
09-23-2009, 02:52 PM
MsgBox ActiveSheet.Evaluate("SUMPRODUCT((A3:A31=""" & ComboBox1.Value & """)*(B3:AF31=""H""))")

headworth
09-23-2009, 09:44 PM
Thank you for all your help