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

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!