Run-Time error '91' Object variable or With variable not set

[akocak4]

Hi Friends,

I have a problem on a simple code. I hope you can help me. I am getting Run-Time error '91' error.
Thank you. Please see attached file.

Regards

[Jan Karel Pieterse]

Funny thing is, if you set it to lookin xlFormulas, it seems to work.

[Paul_Hossler]

1. The 'real' value in J2 is 43999 since Excel stores dates as serial numbers so that's why xlFormulas works

2. You Dim MyDate as a Date, but assign a string so Excel nicely (?) converts it for you

3. You have Custom Number formatting on row 2 (dd-mmm-yy) so I think .Find returns Nothing if you don't give it a string it can locate

4. The Worksheets(I).Rows(2).Nothing is likely what causes the Error 91 since it doesn't have a .Column

Dim MyDate As Date    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


'MyDate = "2019-05-14"

MyDate = DateSerial(2019, 5, 14)    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'MyDate = Application.inputbox("Put Date:", "Choose Date")

'two ways <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Colx = Worksheets(i).Rows(2).Find(what:=Format(MyDate, "dd-mmm-yy"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column

or

Colx = Worksheets(i).Rows(2).Find(what:=MyDate, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False).Column ' Define Column

[akocak4]

Thank you Paul,

You are right, it was problem of DIM Type. It is solved.

Regards

[akocak4]

How can I change the position of this post as "SOLVED". I did not find it ...

[Paul_Hossler]

#3 in my signature