Basic Excel VB Script

Question

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

start by deciding which language because some of those are mutually exclusive. *Then* tell us where it happens, dont make us guess. If it is VB.NET, then see [What is a NullReference...](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Ňɏssa Pøngjǣrdenlarp, Feb 02 '15 at 23:05
Sorry for the confusion Plutonix. It's Excel VB, or VBA. Thanks for removing the other tags. The exception seems to occur at "For Each r In Sheet1.Cells.CurrentRegion.Find(s)". I'll try to update the original post to reflect that. — Gareth Bristow, Feb 02 '15 at 23:19
Find returns the *first* matching cell - assuming there is one: it doesn't return a range of all matches which you can loop through. Find returns `Nothing` if the search term wasn't located. See e.g.: http://www.cpearson.com/excel/findall.aspx — Tim Williams, Feb 02 '15 at 23:56
If you want to use named arguments then `Calculate difference = -10, r` should be `Calculate difference:=-10, r:=r` — Tim Williams, Feb 03 '15 at 00:04
Thanks for the link, Tim. I'll read over it and see if I can modify this. — Gareth Bristow, Feb 03 '15 at 02:02

Marek Stejskal · Accepted Answer · 2015-02-03T09:44:32.020

2

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

Sheet1.Cells.CurrentRegion doesn't actually select the region you would like to search in
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()

edited Feb 03 '15 at 09:44

answered Feb 02 '15 at 23:59

Marek Stejskal

2,698
1
20
30

Thanks Marek. I suspect #2. How would I iterate even if the function finds nothing/null? – Gareth Bristow Feb 03 '15 at 01:53
1

@GarethBristow Look at VBA Help for Range.Find and Range.FindNext for examples on how to iterate through a bunch of cells, where more than one might contain the desired string – Ron Rosenfeld Feb 03 '15 at 02:21
@GarethBristow I edited the answer with an example how to handle Nothing, but you should also take note what Ron Rosenfled and others say about FindNext. – Marek Stejskal Feb 03 '15 at 09:46

score 0 · Answer 2 · answered Feb 03 '15 at 08:52

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)

Basic Excel VB Script

2 Answers2