I'm new to Visual Basic and am having some trouble. I'm receiving the error,
Object variable or With block variable not set
at For Each r In Sheet1.Cells.CurrentRegion.Find(s)
It looks like I'm not setting the pointer correctly but I'm not sure how to do that. I'm sure there are some other errors so any ideas or comments most welcome.
The code is to find a specific string somewhere in the sheet and calculate a percentage of the total invoiced amount for that line. There is a check to ensure the line isn't part of the same invoice, in which case the same total is used.
Public charges(2) As String
Public r As Range
Public s As Variant
Public difference As Integer
Sub Script()
Range("M1").Value = "Apportioned Rate"
charges(0) = "Standard Rebuild Valuation"
charges(1) = "billable charge"
For Each s In charges
For Each r In Sheet1.Cells.CurrentRegion.Find(s) //where exception occurs
Select Case r.Offset(0, -4).Value
Case Is <> r.Offset(-1, -4).Value
Calculate difference = 0, r
Case Is = r.Offset(-10, -4).Value
Calculate difference = -10, r
Case Is = r.Offset(-9, -4).Value
Calculate difference = -9, r
Case Is = r.Offset(-8, -4).Value
Calculate difference = -8, r
Case Is = r.Offset(-7, -4).Value
Calculate difference = -7, r
Case Is = r.Offset(-6, -4).Value
Calculate difference = -6, r
Case Is = r.Offset(-5, -4).Value
Calculate difference = -5, r
Case Is = r.Offset(-4, -4).Value
Calculate difference = -4, r
Case Is = r.Offset(-3, -4).Value
Calculate difference = -3, r
Case Is = r.Offset(-2, -4).Value
Calculate difference = -2, r
Case Is = r.Offset(-1, -4).Value
Calculate difference = -1, r
End Select
Next r
Next s
End Sub
Public Function Calculate(ByVal difference As Integer, r As Range) As Variant
Select Case r.Value
Case Is = charges(0)
r.Offset(difference, 6).Value = r.Offset(difference, 5).Value * 0.5
Case Is = charges(1)
r.Offset(difference, 6).Value = r.Offset(difference, 5).Value * 0.25
End Select
End Function