VBA Using 2 OS (Win98 & Winxp)

[Cyber]

Hi Guys can you help me regarding this problem I have a excel vba macr
but when itried to run in different operating system my function won'
change
here's my code:

Private Sub holidate(xhdate As Date) ' to compute if date entered i
Holiday

Dim crowval As Byte

With Worksheets("Sheet2").Range("A1:A30")
Set Holivalue = .Find(xhdate, LookIn:=xlValues, MatchCase:=False)

' >>.FIND METHOD WON'T WORK IN WINDOWS 98 OS I DON'T KNOW WHY?

If Not Holivalue Is Nothing Then
If Holivalue = xhdate Then
xholid = True
crowval = Holivalue.Row
xcomval = Worksheets("Sheet2").Cells(crowval, 2)
End If
Else
xholid = False
End If

End With
End Sub

Hope you help me guys regarding this eerie problem Thanks :

 

[Stephen Bullen]

Hi Cyber,

Set Holivalue = .Find(xhdate, LookIn:=xlValues, MatchCase:=False)

' >>.FIND METHOD WON'T WORK IN WINDOWS 98 OS I DON'T KNOW WHY?

Remove the MatchCase parameter, as this is more likely to be an Excel
version issue than a Windows one.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Dave Peterson]

I've had lots of trouble with .find with dates. I don't suspect that it's a
windows difference, though.

One thing about using .find in code. I think you'd be better served by
specifying all the parms--Find remembers the last parms that were used--either
via code or through the user interface.

And if you were calling this subroutine from a Function that was placed in a
worksheet cell, then .find won't work for this until xl2002.

But this version (using application.match() and clng()) worked ok for me:

Option Explicit
Dim xHolid As Boolean
Dim xComVal As Variant
Private Sub holidate(xhdate As Date)
Dim res As Variant
With Worksheets("Sheet2").Range("A1:A30")
res = Application.Match(CLng(xhdate), .Cells, 0)
If IsNumeric(res) Then
xHolid = True
xComVal = .Cells(res).Offset(0, 1)
Else
xHolid = False
End If
End With
End Sub
Sub testme()
Call holidate(DateSerial(2004, 10, 31))
MsgBox xHolid & "--" & xComVal
End Sub