PDA

View Full Version : [SOLVED] Run-Time error '91' Object variable or With variable not set



akocak4
06-11-2019, 10:06 PM
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
06-12-2019, 12:00 AM
Funny thing is, if you set it to lookin xlFormulas, it seems to work.

Paul_Hossler
06-12-2019, 07:52 AM
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
06-12-2019, 08:28 PM
Thank you Paul,

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

Regards

akocak4
06-12-2019, 08:30 PM
How can I change the position of this post as "SOLVED". I did not find it ... :(
:banghead::banghead::banghead::banghead:

Paul_Hossler
06-13-2019, 05:31 AM
#3 in my signature