CODEWITHSUNDEEP
vbaexcel
Gareth Bristow
Gareth Bristow

Reputation: 13

Basic Excel VB Script

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

Upvotes: 0

Views: 129

Answers (2)

Marek Stejskal
Marek Stejskal

Reputation: 2708

There are two possible reasons for the error that I can see.

  1. Sheet1.Cells.CurrentRegion doesn't actually select the region you would like to search in
  2. You cannot iterate through Nothing, which is what the .Find() function returns if it doesn't find anything.

Check these in this order and see where the problem is.

EDIT: You should test first if it does find anything, like this:

Dim rng as Range
set rng = Sheet1.Cells.CurrentRegion.Find(s)

If rng is nothing then
    ' "nothing" handling
Else
    ' your main search code
End if

Also, as Ron Rosenfeld and others said in the comments, .Find() returns only the first result and you need to iterate through the other by .FindNext()

Upvotes: 2

Philippe.H
Philippe.H

Reputation: 302

The reason why Sheet1.Cells.CurrentRegion does not select the region you are expecting is that CurrentRegion selects a range bounded by any combination of blank rows and blank columns.

So basically Sheet1.Cells selects a range of all cells in the sheet and active cell is A1. Now if Column B is blank and Row 2 is also blank, then you end up selecting Range("A1").

You can test the behaviour of CurrentRegion in Excel by using the shortcut Ctrl+*.

Your code works if you skip CurrentRegion, i.e.:

For Each r In Sheet1.Cells.Find(s)

Upvotes: 0

Related Questions