Search function in Excel VB

Asked By Pratyush Pattanaik

08-Sep-10 09:20 AM

Earn up to 0 extra points for answering this tough question.

Hi, I have a row of containing dates (in the format mm/dd/yy) from 2010 to end of 2011 by week and I want to search and find a particular date (say 08/14/10) - the 1st instance of it.

I have been trying Find, Worksheetfunction.Hlookup but nothhing seems to work. What am I doing wrong?

a = Application.WorksheetFunction.HLookup(CDate("08/14/10"), Worksheets("Raw Data").Range("A2:ZZ3"), 1, False)
c = Worksheets("Raw Data").Range("B2:ZZ2").Find(CDate("8/14/10"), , xlDate, xlWhole, xlByColumns, xlNext, False, True)

In Find, I have tried different combinations of parameters for "Search what", "Search in" but nothing seems to work

Thanks

re: Search function in Excel VB

Rolf Jaeger replied to Pratyush Pattanaik

08-Sep-10 03:59 PM

Reply

Hi Partyush:

this code example might steer you in the right direction:

Sub FindDate()

Dim a As Range

Dim db As Range

Set db = ActiveSheet.UsedRange

Set a = db.Cells.Find(What:="8/14/2010", After:=ActiveCell, LookIn:=xlValues, LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, SearchFormat:=False)

End Sub

Hope this helped,
Rolf

re: Search function in Excel VB

Pratyush Pattanaik replied to Rolf Jaeger

08-Sep-10 04:37 PM

Reply

No, Rolf, a is still returning "nothing" when I hover my mouse on it while in debugging mode. I used usedrange as ActiveSheet.Range("A2:ZZ2") which is the row where my date list exists.

re: Search function in Excel VB

Rolf Jaeger replied to Pratyush Pattanaik

09-Sep-10 02:08 AM

Reply

Hi Pratyush:

try inserting the following statement before the 'Set a = ...' statement. That should do the trick.

db.Cells(1, 1).Activate

Hope this helped,
Rolf

re: Search function in Excel VB

Pratyush Pattanaik replied to Rolf Jaeger

09-Sep-10 07:40 AM

Reply

Nope, still having problems. I think it's the difference between what "Lookin" parameter wants to see and what the format of the cells are. The lookin parameter (when I hover my mouse over it) shows -4163 (for any date I type) while the "8/14/2010" shows as string or if I use Cdate("8/14/2010") shows as date. So since -4163 will never match date or a string, so the entire expression is evaluating to "nothing". I think as a last resort, I'll just forget Find and run a small loop and an If statement to check all the rows for that date...

but thanks for looking at this issue, Rolf. Dates are always a troublesome issue in VBA.

re: Search function in Excel VB

Rolf Jaeger replied to Pratyush Pattanaik

11-Sep-10 02:53 PM

Reply

Hi Pratyush:

YES, dates have always been a problem with VBA, but take a look at this modified code and see whether you might be able to adjust it to your situation (the trick may by to convert that date you are looking for to its DateSerial number):

Sub FindDate()

Dim d As Date

d = dateValue("8/14/2010")

Dim a As Range

Dim db As Range

Set db = ActiveSheet.UsedRange

db.Cells(1, 1).Activate

Set a = db.Cells.Find( _

What:=d, After:=ActiveCell, LookIn:=xlValues, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False)

If a Is Nothing Then

db.Cells(1, 1).Activate

Dim dateSerialNumber As Double

dateSerialNumber = Conversion.CDbl(DateSerial(Year(d), Month(d), Day(d)))

Set a = db.Cells.Find(What:=dateSerialNumber, After:=ActiveCell, LookIn:=xlValues, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False)

End If

End Sub

Hope this helped,
Rolf

re: Search function in Excel VB

Pratyush Pattanaik replied to Rolf Jaeger

11-Sep-10 05:13 PM

Reply

That's a good line of thought. I tried it out but it's still evaluating to "nothing". Seems like the serial # is not what the xlValues is looking at, which is strange as then what exactly the lookin value is looking at !!!

I used a small if statement within a loop to check...saw no other option. Thanks

re: Search function in Excel VB

Rolf Jaeger replied to Pratyush Pattanaik

14-Sep-10 02:30 PM

Reply

Hi Pratyush:

thanks for letting me know. This is indeed weird! Needless to say the code works fine on my end. Could you by any chance post a copy the portion of your workbook where this problem occurs, or send to office@soarentcomputing.com ?

Best wishes,
Rolf