VBA troubleshooting: "Object variable or With block variable not set" How do I fix this?

Copper Contributor

I'm using Excel version 15.38 for Mac and I'm getting a runtime error that I don't know how to fix it. This macro worked perfectly for months, I stopped using this spreadsheet over the summer, and today when I go to use it again, it mysteriously won't work. When I try to run it, it gets stuck saying there's a runtime error 91 Object variable or With block variable not set.

If I click on "debug", the following code is highlighted:

 

Selection.Find(What:="visit amazon's ", After:=ActiveCell, LookIn:= _

        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

        xlNext, MatchCase:=False).Activate

 

(Attached screenshot is what the data looks like prior to running the macro. The macro is supposed to remove punctuation, remove certain phrases etc.)

 

Can any of you smart people tell me what's wrong? I don't understand VBA code at all - this macro was created by recording me doing all this stuff manually - but I'm good at following directions :)

 

For what it's worth, here is the entire macro code from start to finish:

Sub step1_amazon_keywordprocessing()

'

' step1_amazon_keywordprocessing Macro

' Change to lower_Use data text to columns to get rid of : and (_get rid of Visit Amazon's_Get rid of page

'

' Keyboard Shortcut: Option+Cmd+r

'

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "=LOWER(RC[-1])"

    Range("B1").Select

    Selection.AutoFill Destination:=Range("B1:B201"), Type:=xlFillDefault

    Range("B1:B201").Select

    Columns("B:B").Select

    Selection.Copy

    Columns("C:C").Select

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=False

    Columns("B:B").Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Columns("A:A").Select

    Selection.ClearContents

    Range("C1").Select

    Selection.Copy

    Columns("A:A").Select

    Columns("C:C").Select

    Application.CutCopyMode = False

    Selection.Copy

    Columns("A:A").Select

    ActiveSheet.Paste

    Columns("C:C").Select

    Application.CutCopyMode = False

    Selection.ClearContents

    Columns("A:A").Select

    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

        :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))

    Columns("B:H").Select

    Selection.ClearContents

    Columns("A:A").Select

    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

        :="(", FieldInfo:=Array(1, 1)

    Columns("B:H").Select

    Selection.ClearContents

    Columns("A:A").Select

    Selection.Find(What:="visit amazon's ", After:=ActiveCell, LookIn:= _

        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

        xlNext, MatchCase:=False).Activate

    ExecuteExcel4Macro _

        "FORMULA.REPLACE(""visit amazon's "","""",2,1,FALSE,FALSE,,FALSE,FALSE,FALSE,FALSE)"

    Selection.Find(What:=" page", After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False).Activate

    ExecuteExcel4Macro _

        "FORMULA.REPLACE("" page"","""",2,1,FALSE,FALSE,,FALSE,FALSE,FALSE,FALSE)"

End Sub


Thank-you in advance to anyone who can help me with this - I really appreciate it! :)

 

14 Replies

That code will definitely fail. You're using the Selection object to find something after the ActiveCell. If nothing is found, an error is thrown. Overall it's very messy code. The macro recorder makes sure of that. But, it does make a great starting place!

It looks like you took some repetitive actions in the recorded code. I'm not 100% that I followed everything, but I removed all of the Select methods you called, which aren't needed at all. Also, there is no worksheet referenced for the ranges, so it will ALWAYS run on the ActiveSheet. If you are always going to run this code on this worksheet, then it won't be a problem.

Here is the code:

 

Sub step1_amazon_keywordprocessing()
'
' step1_amazon_keywordprocessing Macro
' Change to lower_Use data text to columns to get rid of : and (_get rid of Visit Amazon's_Get rid of page
'
' Keyboard Shortcut: Option+Cmd+r
'
Range("B1:B201").FormulaR1C1 = "=LOWER(RC[-1])"
Columns("B:B").Copy
Columns("C:C").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Columns("A:B").ClearContents

Columns("C:C").Copy Columns("A:A")
Columns("C:C").ClearContents
Application.CutCopyMode = False

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))

Columns("B:H").ClearContents

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="(", FieldInfo:=Array(1, 1)

Columns("B:H").ClearContents

Columns("A:A").Find(What:="visit amazon's ", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate

ExecuteExcel4Macro "FORMULA.REPLACE(""visit amazon's "","""",2,1,FALSE,FALSE,,FALSE,FALSE,FALSE,FALSE)"

On Error Resume Next Columns("A:A").Find(What:=" page", After:=Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate
On Error Goto 0 ExecuteExcel4Macro "FORMULA.REPLACE("" page"","""",2,1,FALSE,FALSE,,FALSE,FALSE,FALSE,FALSE)" End Sub

I would recommend stepping through your code the first time, until you know it's working right. To do that, just put your cursor anywhere in this routine (to make it the active routine) and the F8 key will step through each line of code. Tapping the F8 key will execute a single line of code at a time. The yellow highlighted line is the one which will run when you press F8.

 

Also, I added bare-minimum error handling to your Find method. This will ensure it doesn't bomb when executing that line. More importantly, I took out the Selection object you used, and replaced ActiveCell with Range("A1").

 

HTH

 

Thank-you SO much for doing that Zack - I can't tell you how much I appreciate you taking the time :) I learned some new things from what you said that will come in handy over the long term. Plus, it was informative to see your cleaner version of the macro code and I intend to study it until I understand what it's doing.

(Writing all the rest of this partly in case someone else comes along who finds this useful...)

I went through the code line by line as you said, and figured out that the recorded code was getting stuck on the part where it had to find "Visit Amazon's " because in this particular set of data, that wasn't on there. Then the same issue would happen when it was supposed to find " page" because that wasn't in this set of data either.

(For non-techies like me who might read this: F8 didn't work on my Mac (maybe it's a Windows-only shortcut?), but using the little button in the VBA editor that looked like a down error pointing to a dot, which said "step into" if I hovered over it, seems to do the same thing as F8 was supposed to do.)

So I saw what you meant when you said "If nothing is found, an error is thrown."

So if I created a test line that DID contain "Visit Amazon's Test Author page", the macro with the messy recorded code ran fine.

But since I don't want to do the work of adding that every time I want to clean up my data with the macro (just in case it doesn't contain that stuff), I added "On Error Resume Next" before each section of the macro that was supposed to find and replace since the stuff it's looking for won't always be found and that's no big deal. 

Now the macro works despite the messy code.

My next job is to play with YOUR code.

AND, by the way, this ""On Error Resume Next" that you added to your cleaner code is seriously a game changer since now I can make the macro also look for symbols and punctuation marks, and replace those with nothing - this is HUGE for me since prior to your response, I couldn't get the macro to do that for me (which I now understand is because it would get stuck if the thing it was looking for wasn't present). WAHOO! This new knowledge will save me loads of tedious, repetitive manual fixing up in future since now I can make a macro do it all FOR me without getting itself stuck. THANK-YOU SO MUCH!!! 

re: "Also, there is no worksheet referenced for the ranges, so it will ALWAYS run on the ActiveSheet."

Yes, this is okay for my purposes - but thanks for pointing that out!

I'm going to go play with the code more now. This non-techie is so flipping happy with this you have no idea :D


Very happy to help! :)

 

I'm not sure about Mac, but I don't think they have the F-keys on their keyboards and it's typically a PC thing. I use the VBE (Visual Basic Editor) keyboard shortcuts all the time. Huge time savers.

 

I wasn't sure what your Excel4Macro was supposed to be doing, but if it's just replacing values, you can do that in one fell swoop too:

 

RangeToWorkWith.Replace What:="This", Replacement:="That", LookAt:=xlPart

The benefit for this is, not only is it taking care of two lines in one fell swoop, but it won't throw an error like the Find method will, so no need to wrap it in error handling.

 

As long as we're talking about error handling, it should be noted this is the least preferred method for handling errors. The first is to take logical action in your code to prevent known errors from forming. The next would be trapping errors - performing some kind of check if an error was processed, like "If Err.Number <> 0".

 

Thanks for the detailed response. Have a great day!

 

re: "I wasn't sure what your Excel4Macro was supposed to be doing"

Ha, neither am I! That's what you get when non-technie uses "record macro" lol

re: "
if it's just replacing values, you can do that in one fell swoop too:

 

RangeToWorkWith.Replace What:="This", Replacement:="That", LookAt:=xlPart

The benefit for this is, not only is it taking care of two lines in one fell swoop, but it won't throw an error like the Find method will, so no need to wrap it in error handling.

Ahh, cool. I'm going to play with that - because yes, it's nothing more than replacing values.

For syntax, say I wanted it to replace all "!" in column A with "" (i.e. erase the ! and replace it with nothing), and then replace all "-" in column A with " " (i.e. replace a dash with a single space)... how would I write that using that type of code? I can guess all sorts of potential ways to "fill in the blanks", but don't know which one is correct... for the exclamation mark, for example...

Option 1:

 

RangeToWorkWith.Replace What:="!", Replacement:="", LookAt:=A

 

Option 2

A.Replace What:="!", Replacement:="", LookAt:=A

Option 3:

Range(A1).Replace What:="!", Replacement:="", LookAt:=A1

Something else? 

Sorry... I realize this must be insanely basic to you since you know what you're doing hehe  And if you're tired of answering questions, that's cool too - the information you've already provided has been very helpful!

Really close! The 'RangeToWorkWith' is a Range object variable. So it can be any Range. For example...

Range("A1")

...or...


Dim RangeToWorkWith As Range
Set RangeToWorkWith = Range("A1")

 

In the latter, 'RangeToWorkWith' is now the same as Range("A1"). The benefit of a variable is you can set it to whatever you want, and most importantly make them dynamic.

 

The "LookAt" parameter of the Find or Replace method should be either xlPart or xlWhole, both part of the 'XlLookAt' class of constants (which may be a little above your head at this point, but that's ok - immersion!). This says whether you want to look at the entire contents of the cell for the What parameter. In your case it should be xlPart.

 

So, let's say you want to do all of this in column A. For me, I don't want to look at the whole column, because that's a LOT of cells, and I'm probably using a fraction of them. Also, let's say we have a header in row 1. So data starts on row 2 and goes down an unknown number of rows. This is called a dynamic range, because it may not always be the same size, and is why a recorded macro may not work so well as it hard-codes the range address into the Range object.

 

Dim ReplaceSheet As Worksheet
Dim ReplaceRange As Range

Set ReplaceSheet = ThisWorkbook.Worksheets("Sheet1")
Set ReplaceRange = ReplaceSheet.Range("A2", ReplaceSheet.Cells(ReplaceSheet.Rows.Count, "A").End(xlUp))

ReplaceRange.Replace What:="!", Replacement:="", LookAt:=xlPart
ReplaceRange.Replace What:="-", Replacement:=" ", LookAt:=xlPart

With the above code, it doesn't matter if your data is in A2:A10, or A2:A1000, it will work on that data range. It basically looks at the bottom-most cell in that column and goes up until it finds data (the keyboard shortcut equivalent is, from the bottom of the sheet, press CTRL + Up Arrow).  Then it uses that range with the Replace method.

 

Another added benefit of using a variable (properly scoped) is you get intellisense on that object. Since I declared the variable of a Range type, when you type that variable name and press period, you see a drop-down list of all methods and properties associated with that object. Methods have the little green icon, properties have the little card with a hand on it. Methods do something, and may or may not take parameters passed to it (we're passing three parameters in the above code). Properties let us read (and sometimes write) data to that property. A property can be values (i.e. text, numbers, dates) or objects (i.e. Range, Shape, Worksheet, Workbook). And hence commenses your crash course into Object Oriented Programming (OOP)! :)

 

I don't mind answering any question you might have. Post away!

You're amazing for explaining all that - thank-you so much! I'm going to try it :D

Okay, I'm back :D

So this is what I tried to create based on the above info, previous posts you've shared, and some Googling:

(What I want it to do is to find and replace certain characters and words I don't want, but to ONLY do this on the sheet I'm currently looking at. And if it doesn't find the character it just looked for (will happen often), I want it to skip ahead to the next one listed in the code.)

Sub Sher_step2_amazon_keywordprocessing_find_and_replace_stuff()
'
' Sher_step2_amazon_keywordprocessing_find_and_replace_stuff Macro

' Change to lower_find and replace prohibited symbols and words
'
Dim ReplaceSheet As Worksheet
Dim ReplaceRange As Range

Set ReplaceSheet = ThisWorkbook.Worksheets("Sheet1")
Set ReplaceRange = ReplaceSheet.Range("A2", ReplaceSheet.Cells(ReplaceSheet.Rows.Count, "A").End(xlUp))

ReplaceRange.Replace What:="!", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="@", Replacement:="at", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="#", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="$", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="%", Replacement:="percent", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="^", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="&", Replacement:="and", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="<", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:=">", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="- ", Replacement:=" ", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="-", Replacement:=" ", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="  ", Replacement:=" ", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:=". ", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:=".", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="  ", Replacement:=" ", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="/", Replacement:=" ", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:=",", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="“", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="“", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="”", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="—", Replacement:=" ", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="®", Replacement:="", LookAt:=xlPart
On Error Resume Next

ReplaceRange.Replace What:="Kindle", Replacement:="", LookAt:=xlPart
On Error Resume Next

End Sub

But I got stumped on the following:

1. As written, the macro appears to do it's thing only on a sheet of my workbook called "Sheet1" (which I don't want it to do). What I DO want is for it to do its thing only on whatever sheet I'm currently working on regardless of the name. (Not all sheets in the workbook though... only the one sheet I'm looking at). :)

If I delete the line of code that references "sheet1", I get an error when I try to run the macro. If I cave and decide that fine, I'll tell it to run on a specific sheet, and I replace "sheet1" with the name of the sheet I want it to run on right now (ExperimentWithNewMacro), I get a runtime error (Runtime error 9. Subscript out of range.) - if I click on "Debug", it highlights this row of code:

Set ReplaceSheet = ThisWorkbook.Worksheets("ExperimentWithNewMacro")

I tried fiddling around with it, Googling, tweaking things etc... but couldn't get it to work. I figure I must be missing something, and I'm crossing my fingers that it'll be quick and obvious for you to see where I went wrong :)


Okay, awesome news! Thanks to all your help Zack, I got the second macro to do what I want it to! Holy cow - this is beyond amazing since it will save me HOURS of repetitive manual labour replacing symbols and prohibited words with spaces or flat-out nothing.

I thought I'd posted another questions about how I got stuck, but I don't see it here. I kept playing around with the problem and managed to fix it despite being clueless haha So if that new question from today DOES show up, you can ignore it.

Thanks again!!! :D

That's awesome! We do love saving people time. ;)

Very much appreciate the follow-up. It helps us know that what we do is worth it.

Hey guys,

 

I am trying to fill-in data in a website with VBA, but I am getting a run time error 91. I have the idea that there is an error due to auto-complete, but I am not 100% sure.

The website is to calculate travel time, the value is (Dutch) travel data.

 

Who can help me?  

@Zack Barresse?

VBA Code:

--------------------------------------------------------------------------------------

 

Sub GetHTMLDocument()

Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLButtons As MSHTML.IHTMLElementCollection
Dim HTMLButton As MSHTML.IHTMLElement

IE.Visible = True
IE.navigate "https://www.anwb.nl/verkeer/routeplanner"

Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop

Set HTMLDoc = IE.Document
Set HTMLInput = HTMLDoc.getElementById("routing-input-address-field-0")
HTMLInput.Value = "9711NA"


End Sub

-----------------------------------------------------------------------------------

@Gijs1 please start your own thread. Feel free to message me the link. Also, your code looks fine and you shouldn't be getting that error. You would, however, need two references (Tools > References...) Microsoft HTML Object Library and Microsoft Internet Controls.

Just leaving this response here in case someone finds this useful. I was facing a similar issue when running a macro. I was trying to run the macro on a recently downloaded file and it kept throwing the Runtime Error 91. Enabling Editing (clicking on Enable Editing) fixed the issue for me. 

I am having a similar issue with "Error 91". My code includes "Error Handling", but for some weird reason, after the cell search has failed once and correctly executed my code, a subsequent search failure causes the macro to bomb with "Error 91". It's as though a line of reset code is needed after the 1st failed search.

Here is my complete code...

Sub Confirm_Quicken_Data_Links()

' This macro searches the "Expenditure" sheet for each Payee Link held in "Quicken Data". Search results are stored in the "LinkTest".
' A check is made for Payees having a "2-Line" Header text.

Dim N As Integer
Dim X As String
Dim Y As Integer
Dim Row As Integer
Dim Col As Integer
Dim Payee As String
Dim PayeeAddrs As String
Dim PayeeLinkAddrs As String
Dim Payees As Integer
Dim ExpenseCategory As String
Dim LinkRange As Range


Sheets("Quicken Data").Select
N = 0
Range("B1").Select
Selection.End(xlToRight).Select
Payees = ActiveCell.Column - 1
Set LinkRange = Sheets("Expenditures").Range("LinkRange")
Range("B1").Offset(0, N).Select
Do While N <= Payees
Err.Clear
Payee = ActiveCell
PayeeAddrs = ActiveCell.Address
PayeeLinkAddrs = ActiveCell.Offset(15, 0).AddressLocal
PayeeAddrs = "'Quicken Data'!" & PayeeAddrs
X = "IFERROR(SEARCH(CHAR(10)," & PayeeAddrs & "),0)"
Range("SearchCell_2").Select ' Cell location for "Formula" search
ActiveCell.Formula = "=" & X
If ActiveCell.Value = 0 Then ' 1-LINE Header Text(Payee Name)
Y = 0
Else
Y = 1 ' 2-LINE Header Text(Payee Name)
End If
Sheets("Expenditures").Select
Range("A1").Select
On Error Resume Next
Cells.Find(What:=PayeeLinkAddrs, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
On Error GoTo NOTFOUND
If Err.Number = 0 Then ' No Error - Search String Found
Expense = ActiveCell.Offset(0, -5).Value
Sheets("LinkTest").Select
Range("A2").Offset(N, 0).Select
If Y = 1 Then
X = "Replace(" & PayeeAddrs & ", Search(CHAR(10)," & PayeeAddrs & "), 1, CHAR(32))"
ActiveCell.Formula = "=" & X
Else
ActiveCell = Payee
End If
Range("A2").Offset(N, 1) = PayeeLinkAddrs
Range("A2").Offset(N, 2) = Expense
Else

NOTFOUND:
Sheets("LinkTest").Select
Range("A2").Offset(N, 0).Select
If Y = 0 Then
X = "Replace(" & PayeeAddrs & ", Search(CHAR(10)," & PayeeAddrs & "), 1, CHAR(32))"
ActiveCell.Formula = "=" & X
Else
ActiveCell = Payee
End If
Range("A2").Offset(N, 1) = PayeeLinkAddrs
Range("A2").Offset(N, 2) = "Not Found"
End If
N = N + 1
Expense = ""
Sheets("Quicken Data").Select
Range(PayeeLinkAddrs).Offset(-15, 1).Select ' Next Payee
Loop
End Sub

Thanks

Sorry for simply adding my problem to your conversation. I couldn't figure out how to create my own post! But the main issue is the same as yours.