Object variable not set???

[Dirkelton]

I'm currently trying to learn VBA via a step by step book. I'm getting on OK but have hit a brick wall with the following code to auto fill a formula to a range.


Sub FillFormulas()


Dim myOuter As Range
Dim myInner As Range
Dim myFormula As String


Set myOuter = Cells.Find("price").CurrentRegion

Set myInner = Intersect(myOuter, myOuter.Offset(2, 1))

Cells.Find("Discount").CurrentRegion.CreateNames True

myFormula = myOuter.Range("b2").Address(True, False, xlR1C1, False, myInner)

myFormula = "=" & myFormula & "*"

myFormula = myFormula & _
myOuter.Range("A3").Address(False, True, xlR1C1, False, myInner)

myFormula = myFormula & " * (1 - Discount)"

myInner.FormulaR1C1 = myFormula

End Sub


I've input as advised in the book but keep getting Run Time Error '91': Object Variable or With block variable not set.

Everything works ok in the Immediate Window but just doesn't run as a macro.

Can anyone help please

 

[xenou]

Unfortunately your code is very obscure - what are the ranges "price" and "discount". Why would you be intersecting them? What is the formula you are adding? An error could occur at any and all of these lines.

However, you can learn a lot by debugging your code:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

I would suggest you step through line by line an check what each line is doing. Actually, I suggest you toss this section of the book and move on - it's a strange bit of code to be using for educational purposes.