Find

**goldie12** · 07-06-2005, 09:31 AM

Ok the code is counting correctly, however, I need it to only count if "Size" appears between "HAT" and "HAT END" in column B and if the value in Column I is >10% for the "SIZE".

Any suggestions

Option Compare Text

Public Sub FindHat()
With Columns("B:B")
Set c = .Find("Hat", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(1, 0).Value = "Size" And c.Offset(1, 9).Value < 0.01 Then
TheCount = TheCount + 1
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

**Killian** · 07-06-2005, 12:01 PM

Hi and welcome to VBAX

Small couple of small modifications to your code and it seems to work fine

Set c = Columns("B:B").Find("HAT", , xlValues, xlWhole) 'find whole word
    With Columns("B:B")
        Set c = .Find("HAT", LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
            ' the offset is the count from the range used rather than the column number
            ' and to get greater than 10% is value>0.1
                If c.Offset(1, 0).Value = "Size" And c.Offset(1, 7).Value > 0.1 Then
                    TheCount = TheCount + 1
                End If
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

this assumes "Size" is always 1 row below "HAT".
If it's just somewhere between "HAT" and "END HAT" we'll have to do a little more ?

**goldie12** · 07-06-2005, 12:14 PM

Thanks for the reply, "Size" may or may not be between "HAT" and "HAT END". If it falls anywhere between them I want to check if the cells value is "SIZE" if it is I want to check the value in column I to see if the % is greater than 10%. If it is count that value and go to the next one.

**Killian** · 07-06-2005, 06:54 PM

I think For... Next loops might be in order here, then. Still using Find to get the end of the range to keep the iterations to a minimum

Public Sub FindHat()
Dim h As Long
Dim rngLastEndHat As Range
Dim e As Long
Dim s As Long
Dim TheCount As Long
'find the last instance of END HAT to minmize the search range
    Set rngLastEndHat = Columns("B:B").Find(What:="END HAT", After:=Range("B65536"), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=True)
If Not rngLastEndHat Is Nothing Then
        For h = 1 To rngLastEndHat.Row
            If Cells(h, 2).Text = "HAT" Then
                For e = h To rngLastEndHat.Row
                    If Cells(e, 2).Text = "END HAT" Then
                        For s = h To e
                            If Cells(s, 2).Text = "Size" And Cells(s, 9).Value > 0.1 Then
                                TheCount = TheCount + 1
                                Exit For
                            End If
                        Next s
                        Exit For
                    End If
                Next e
            End If
        Next h
    End If
End Sub

**goldie12** · 07-07-2005, 05:46 AM

Thank you so much the code works perfectly. One more question. Do you know how time (HH:MM:SS) are represented in VBA when writing code to search for time?

**Bob Phillips** · 07-07-2005, 06:01 AM

Originally Posted by goldie12

Thank you so much the code works perfectly. One more question. Do you know how time (HH:MM:SS) are represented in VBA when writing code to search for time?

Time is stored as a fraction of 1 day, but when using find, use its text representation, just as you would with Ctrl-F.

**goldie12** · 07-07-2005, 06:59 AM

Instead of counting the size can you make it just count "Hat" instead? I need to know how many "Hat"'s have "sizes" greater than 10%. Not how many "Sizes" in between the "HAT" are greater than 10%.

Thank you for your help!!!

Originally Posted by Killian

I think For... Next loops might be in order here, then. Still using Find to get the end of the range to keep the iterations to a minimum

Public Sub FindHat()
Dim h As Long
Dim rngLastEndHat As Range
Dim e As Long
Dim s As Long
Dim TheCount As Long
'find the last instance of END HAT to minmize the search range
Set rngLastEndHat = Columns("B:B").Find(What:="END HAT", After:=Range("B65536"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=True)
If Not rngLastEndHat Is Nothing Then
For h = 1 To rngLastEndHat.Row
If Cells(h, 2).Text = "HAT" Then
For e = h To rngLastEndHat.Row
If Cells(e, 2).Text = "END HAT" Then
For s = h To e
If Cells(s, 2).Text = "Size" And Cells(s, 9).Value > 0.1 Then
TheCount = TheCount + 1
Exit For
End If
Next s
Exit For
End If
Next e
End If
Next h
End If
End Sub

**goldie12** · 07-07-2005, 07:00 AM

Originally Posted by xld

Time is stored as a fraction of 1 day, but when using find, use its text representation, just as you would with Ctrl-F.

Thank you for your help

**goldie12** · 07-07-2005, 10:05 AM

Originally Posted by Killian

I think For... Next loops might be in order here, then. Still using Find to get the end of the range to keep the iterations to a minimum

Public Sub FindHat()
Dim h As Long
Dim rngLastEndHat As Range
Dim e As Long
Dim s As Long
Dim TheCount As Long
'find the last instance of END HAT to minmize the search range
Set rngLastEndHat = Columns("B:B").Find(What:="END HAT", After:=Range("B65536"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=True)
If Not rngLastEndHat Is Nothing Then
For h = 1 To rngLastEndHat.Row
If Cells(h, 2).Text = "HAT" Then
For e = h To rngLastEndHat.Row
If Cells(e, 2).Text = "END HAT" Then
For s = h To e
If Cells(s, 2).Text = "Size" And Cells(s, 9).Value > 0.1 Then
TheCount = TheCount + 1
Exit For
End If
Next s
Exit For
End If
Next e
End If
Next h
End If
End Sub

For some reason, the code only works in the Sub. I am trying to add the code into an existing function that is counting other items in my spreadsheet. Any suggestions on how to add this code to an exisiting function and sent these results to the same msgBox as the other counts?

**goldie12** · 07-07-2005, 01:09 PM

Can anyone explain to me why the following code only works when I manually type in the criteria onto an Excel spreadsheet. The data that does not work is information from an Access database. For some reason or another when I run the code on the Access database spreadsheet data I always get 0 for the count. If I open a new blank spreasheet and manually add some data it works fine

Option Compare Text 

Public Sub FindHat() 
Dim bn As Long 
Dim rngLastHatEnd As Range 
Dim hn As Long 
Dim de As Long 
Dim TheCount Long 
'find the last instance of END HAT to minmize the search range 
Set rngLastHatEnd = Columns("B:B").Find(What:="Hat End", After:=Range("B65536"), _ 
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ 
MatchCase:=False, SearchFormat:=True) 
If Not rngLastHatEnd Is Nothing Then 
For bn = 1 To rngLastHatEnd.Row 
If Cells(bn, 2).Text = "Hat" Then 
For hn = bn To rngLastHatEnd.Row 
If Cells(hn, 2).Text = "Hat End" Then 
For de = bn To hn 
If Cells(de, 2).Text = "Size" And Cells(de, 9).Value >= 0.1 Or Cells(de, 9).Value < 0.5 Then 
TheCount = TheCount + 1 
Exit For 
End If 
Next de 
Exit For 
End If 
Next hn 
End If 
Next bn 
End If 
MsgBox "The Count is: " & TheCount, vbInformation + vbOKOnly 
End Sub

**Killian** · 07-09-2005, 05:17 AM

Instead of counting the size can you make it just count "Hat" instead? I need to know how many "Hat"'s have "sizes" greater than 10%. Not how many "Sizes" in between the "HAT" are greater than 10%.

I don't understand, can you post some example data?

For some reason, the code only works in the Sub. I am trying to add the code into an existing function that is counting other items in my spreadsheet. Any suggestions on how to add this code to an exisiting function and sent these results to the same msgBox as the other counts?

Instead of incorporating the code, you could call it as a function that returns that value i.e. change your sub declaration to

Public Function FindHat() As Long

and add the line

FindHat = TheCount

to the end - this assigns the count to the function as it's return value. You can then call the function as required from your main routine

Sub CallingRoutine()
    MsgBox "The Count is: " & FindHat, vbInformation + vbOKOnly
End Sub

Can anyone explain to me why the following code only works when I manually type in the criteria onto an Excel spreadsheet.

I think you need to add a breakpoint or watch to check the values you are getting with the access data - you're using a precentage data type in Excel (value 0.1 displays 10%) -

Also, I think you might need to revisit this line

If Cells(de, 2).Text = "Size" And Cells(de, 9).Value >= 0.1 Or Cells(de, 9).Value < 0.5 Then

greater than 10% OR less than 50% counts everything...

**goldie12** · 07-11-2005, 08:57 AM

Thank you Killian you were right

Thread: Find

Thread Tools

Display

Find

Posting Permissions