Forum Discussion
VBA troubleshooting: "Object variable or With block variable not set" How do I fix this?
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:=xlPartWith 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!
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?
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-----------------------------------------------------------------------------------
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 SubBut 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 :)
- You're amazing for explaining all that - thank-you so much! I'm going to try it :D