Opening at a specific Excel Row using VBA

Question

0.00/5 (No votes)

See more:

I have a spreadsheet that has a rolling date in Column B and it always opens, unsurprisingly, at the top.

I've put the following code into ThisWorkbook but I am getting an error on the Set line ...

Run-time error '91';

Object variable or With block variable not set

Can somebody please tell me what I'm doing wrong ?!?

VB

Private Sub Workbook_Open()

Dim myDate As Date
Dim myCell As Range

myDate = Date

With Worksheets("Sheet1")
    Set myCell = Columns(2).Find(What:=myDate, LookIn:=xlValues).Activate
End With

Application.Goto Selection, True


End Sub

Posted 2-Jul-14 22:38pm

Gary Heath

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-07-03T00:14:00

Solution 1

The problem is that the Find has failed to find the date, so the .Activate is being issued against a null object.

Try checking the result first e.g.

VB

With Worksheets("Sheet1")
    Set myCell = Columns(2).Find(What:=myDate, LookIn:=xlValues)
    If Not myCell Is Nothing Then
        myCell.Activate
    End If
End With

Posted 3-Jul-14 0:14am

CHill60

Comments

Gary Heath 3-Jul-14 6:39am

Brilliant, thank you, I do love the clear and obvious Error Messages Microsoft display !!!

CHill60 3-Jul-14 7:09am

:laugh: