Microsoft Excel - Search function in Excel VB

Asked By Pratyush Pattanaik
08-Sep-10 09:20 AM
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
  Rolf Jaeger replied to Pratyush Pattanaik
08-Sep-10 03:59 PM
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
  Pratyush Pattanaik replied to Rolf Jaeger
08-Sep-10 04:37 PM
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.
  Rolf Jaeger replied to Pratyush Pattanaik
09-Sep-10 02:08 AM
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
  Pratyush Pattanaik replied to Rolf Jaeger
09-Sep-10 07:40 AM
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.

  Rolf Jaeger replied to Pratyush Pattanaik
11-Sep-10 02:53 PM
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
  Pratyush Pattanaik replied to Rolf Jaeger
11-Sep-10 05:13 PM
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  
  Rolf Jaeger replied to Pratyush Pattanaik
14-Sep-10 02:30 PM
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
Create New Account
help
how can I use randbetween without repeating numbers in a set Excel Excel Worksheet Discussions WorksheetFunction (1) Microsoft Excel (1) PowerPoint (1) Excel 2003 (1) RANDBETWEEN (1) Workbook (1) COUNTIF (1) Macro (1) See JE McGimpsey's site for a solution to this problem. http: / / www.mcgimpsey.com / excel / udfs / randint.html Note: requires use of VBA Gord Dibben MS Excel MVP Increase the Maximum Number value in your Randbetween formula. For Example if you want
Llamar una funcion de Excel a Access Excel Hola a tod@s! Alguien me puede ayudar a decirme como o si se puede llamar una funcion de Microsoft Excel "RoundUp" o cualquier otra funcion de Microsft Excel a Microsoft Access utilizando VBA. De antemano muchas gracias! Saludos VBA - Spanish Discussions Application WorksheetFunction.RoundUp (1) Microsoft Excel (1) MiExcel.WorksheetFunction.RoundUp (1) Excel (1) VBA (1) CreateObject (1) GetObject (1) RoundUp
Unique Random Numbers Excel How can a series of unique, whole number, random numbers be generated so that no two numbers are alike? Excel Miscellaneous Discussions WorksheetFunction (1) Microsoft Excel (1) WorksheetFunction.CountIf (1) Excel (1) VB (1) ClearAndCall90 (1) BiffMicrosoft (1) FillRange (1) See this: http: / / mcgimpsey.com excel / udfs / randint.html - - Biff Microsoft Excel MVP Hi CJ, I do not remember where I
ms excel spreadsheet - fill in required cells before saving it Excel After creating an Excel (Excel 2003) spreedsheet, how can i get the user to fill in the necessary / required cells to fill it in is not good enough)? Or is there a way to have Excel check to make sure these cells are filled in upon saving it? - - Matt K. Excel Discussions Application.WorksheetFunction.CountA (1) WorksheetFunction (1) Microsoft Excel (1) Excel 2003 (1) Sheets (1) Excel (1) You can create a
MAX of ABS of 2 values syntax Excel Would this syntax be correct ? Var1 = WorksheetFunction.Max(WorksheetFunction.Abs (Rng1), WorksheetFunction.Abs(Rng2)) Thank you for your help, J.P. Excel Programming Discussions WorksheetFunction (1) Microsoft Excel (1) MAX (1) ABS (1) VBA (1) Rng2.Address (1) Rng1.Address (1) Rng2.Value