Why am I getting this error: Object Variable or With block variable not set?

Question

I have found out what causes this error but I cannot pinpoint where it is coming from in my code. I even tested this macro in a separate worksheet in excel and it worked but in this worksheet it is not working. Can anyone guess as to why or offer any workarounds? I put a comment on the line with the error. Assume data is put in correctly.

Private Sub CommandButton1_Click()
    Dim startDate As Date, endDate As Date, reason As String, name As String
    name = InputBox("Please enter the name of the SLG as appears in column 1 on the worksheet:")
    startDate = InputBox("Please enter the start date in MM/DD/YYYY format:")
    endDate = InputBox("Please enter the end date in MM/DD/YYYY format:")
    reason = InputBox("Please short description for the absence:")

    Dim rng1 As Range, columnNumberStart As Integer, rowNumber As Integer, columnNumberEnd As Integer, test1 As String, test2 As String

    Worksheets("FY-15 Schedule").Activate

    Set rng1 = ActiveSheet.UsedRange.Find(name)
    rowNumber = rng1.Row

    Set rng1 = ActiveSheet.UsedRange.Find(startDate) 
    columnNumberStart = rng1.Column 'Says Error is on this line

    Set rng1 = ActiveSheet.UsedRange.Find(endDate)
    columnNumberEnd = ActiveSheet.UsedRange.Find(endDate).Column

    test1 = Cells(rowNumber, columnNumberStart).Address
    test2 = Cells(rowNumber, columnNumberEnd).Address

    Dim rng2 As Range
    Set rng2 = Range(test1, test2)


    rng2.Value = reason

End Sub

what happens if the user doesn't input a date in the correct format (or a date at all?) or if for some reason there is no "Active" sheet — xQbert
– xQbert, Commented Sep 2, 2014 at 21:14
I apologize but I am assuming all data is input correctly and it still doesnt work — billmann
– billmann, Commented Sep 2, 2014 at 21:15
Seems like obvious source of this error is that the .Find method has returned Nothing to the range object, so then you are essentially doing Nothing.Column, which is an error. Google for help on "finding date values" in Excel. I don't remember how to do it off the top of my head... — David Zemens
– David Zemens, Commented Sep 2, 2014 at 21:35

David Zemens · Accepted Answer · 2014-09-02 21:41:58Z

3

First, explicitly coerce the date from the inputbox input:

startDate = CDate(InputBox("Please enter the start date in MM/DD/YYYY format:"))

And do the same for the other date field.

Then, there is still possible source of this same error: when the .Find method has returned Nothing to the range object, (e.g., the date is not found/doesn't exist in the sheet) then you are essentially doing Nothing.Column, which is an error.

If the date does not exist in the sheet, this will always raise an error, which you could trap like this, or use GoTo statements to return to the inputbox, etc...

Set rng3 = ActiveSheet.UsedRange.Find(startDate) 
If rng3 Is Nothing Then 
    MsgBox "Start date" & Cstr(startDate) & " not found!", vbInformation
    Exit Sub
End If
columnNumberStart = rng1.Column

answered Sep 2, 2014 at 21:41

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

guitarthrower Over a year ago

I did some quick testing and Excel requires the search string be in M/D/YYYY format.

David Zemens Over a year ago

@guitarthrower or CDate will coerce that date to proper format for the Find method. Cheers~

guitarthrower Over a year ago

So it does. Brilliant!

billmann Over a year ago

thanks guys but I think it has something to do with the worksheet I am using. I tried your solution and used the debugger and no luck there. I have another worksheet that it does work with but they are identical for all intents and purposes. Just some different data. All formatted the same. I am stumped

David Zemens Over a year ago

any chance you can upload a sheet with a few rows of example dates and your macro, to Google Docs or Dropbox? I can probably look at it later tonight...

000 · Accepted Answer · 2015-04-20 22:17:13Z

0

I figured it out! The problem was this: It was not finding the dates because the dates were being generated by a formula!! My first date was manually typed in but the rest of them were a fill series from the second cell which was A2=A1+1 and all the way down the line. I wanted it that way so the start date could be changed but for some reason excel was not recognizing those as dates!?! How weird?!?!

Problem solved though. What you said was correct about it not finding the date but appearance wise when looking at the spreadsheet it is there in plain sight but when looking at the cells their values were formulas.

edited Apr 20, 2015 at 22:17

000

27.3k10 gold badges74 silver badges103 bronze badges

answered Sep 3, 2014 at 0:12

billmann

578 bronze badges

3 Comments

David Zemens Over a year ago

Great! Excel has some difficulty knowing that you want the Find method to return the result of an evaluated formula, it's not that it doesn't recognize the value as a date, only that the Find method is not suited for that without some tweaking, I think. Good job for finding the workaround :)

Rory Over a year ago

If you're using Find you should specify all the relevant arguments, such as whether to look in values or formulas. Remember that these can persist between uses of Find (both in code and in the UI) so they may not be set to what you need.

billmann Over a year ago

@Rory thank you very much I did not look at all the optional parameters/arguments. I should research the methods I use before I use them from now on. That could save me a lot of trouble.

Collectives™ on Stack Overflow

Why am I getting this error: Object Variable or With block variable not set?

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related