VBA HLOOKUP - Error- why.. oh why..

[Sinbad]

Hi all,

I have 2 questions...

1. Don´t quite understand why I get an error when using this VBA code. It does not like me using curdate in the Hlookup statement

Sub ph()

'Dimensioning
Dim curdate As String
Dim ph As Variant

'Set values
curdate = Range("B34")

'work things out
MsgBox WorksheetFunction.HLookup(curdate, ActiveSheet.Range("AK2:AW2"), 1, False)
End Sub

when I change the code to this (replace curdate)

Sub ph()

'Dimensioning
Dim curdate As String
Dim ph As Variant

'work things out
MsgBox WorksheetFunction.HLookup(Range("B34"), ActiveSheet.Range("AK2:AW2"), 1, False)
End Sub

it works just fine ...


in B34 is a date eg. 01.01.2018 and in the Range AK2:AW2 are various dates in the same format.
I changed the


2. in the working code, it returns 43101 ?? where does that value come from ? I tried with 01.01.2018 in cell B34 and it is also in the search range. I would like it to simply return a 1 if found and a 0 if not found.

Thank you as always.

 

[mole999]

43101 is the number of days from 1/1/1900. each day is a whole number

 

[MARK858]

43101 is the date code for 1/1/2018. Format it as a date and it will show up correctly.
Are your dates real dates? if yes why are you declaring them as strings?

 

[Sinbad]

yup, they are real dates, I did declare them as Date but makes no real differance that I can notice. Same behaviour.

 

[MARK858]

Sub ph()

'Dimensioning
Dim curdate As Long
'Set values
curdate = Range("B34")

'work things out
MsgBox Format(WorksheetFunction.HLookup(curdate, ActiveSheet.Range("AK2:AW2"), 1, False), "dd/mm/yyyy")
End Sub

Change "dd/mm/yyyy" to "mm/dd/yyyy" if you use American dates.

 

[Sinbad]

Thanks Mark858, that is an interesting take, did not know you could combine it in that way.