PDA

View Full Version : [SOLVED] 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 :hi:
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
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!!!




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
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
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 :confused2 :confused2 :confused2


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