Do I need to set a value to range before I refer to it? Error nr 91

Question

I have problem with an error number 91 - Object variable or with block variable not set which I don't understand.

When I search for it on the internet and here it says that you to set a value to the range or it will be set to nothing, but why can't I have it as nothing and then update it in my code?

What I'm trying to do is searching the headers in a For loop which goes through the sheets. I want it to search and find 3 specific headings.

Sub InfogaPrislista()
'
' InfogaPrislista Makro
' Adds a price list and updates the code in excel
'
    Dim sKolumn As Range 'Fuse Column
    Dim fKolumn As Range 'Fixed price kolumn
    Dim rKolumn As Range 'Variable price kolumn
    
    Dim Säkringar As ArrayList 'List with fuses
    Dim Blad As ArrayList 'List with sheets
    
    Set Säkringar = New ArrayList
    Set Blad = New ArrayList
    
    'Adding fuses to list
    Säkringar.Add prislistor.Cells(4, 1)
    Säkringar.Add prislistor.Cells(5, 1)
    Säkringar.Add prislistor.Cells(6, 1)
    Säkringar.Add prislistor.Cells(10, 1)
    Säkringar.Add prislistor.Cells(11, 1)
    Säkringar.Add prislistor.Cells(12, 1)
    Säkringar.Add prislistor.Cells(13, 1)
    Säkringar.Add prislistor.Cells(14, 1)
    Säkringar.Add prislistor.Cells(15, 1)
    Säkringar.Add prislistor.Cells(20, 1)
    Säkringar.Add prislistor.Cells(21, 1)
    Säkringar.Add prislistor.Cells(25, 1)
    Säkringar.Add prislistor.Cells(26, 1)
    
    'Adding sheets to list
    Blad.Add brududden
    Blad.Add rinnefors
    Blad.Add järpforsen
    Blad.Add rexed
    Blad.Add sälboda
    
    If prislistor.Range("B29") = "Enfas" Then
    
        ' Adds a specific fuse price list
        'Sometimes I get an error here, but I think it depends on where and what I'm doing in the Excel workbook
        prislistor.Range("A2:C7").Select
        Selection.Copy
        prislistor.Range("D2").Select
        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False
        prislistor.Range("E4:F7").Select
        Selection.ClearContents
        prislistor.Range("C29").Select
        
        'This is supposed to search through the headings in every sheet in the sheets list for the different columns I set as ranges at row 6-8 in this code but I get error nr 91  
        Dim i as Long
        For i = 0 To 4
            sKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Säkring", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
            rKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Rörligtpris nät", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
            fKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Fastpris nät", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
        Next i  
     End if  
End Sub

Sorry if this post becomes bad it's my first one with code. As I said earlier I have searched the internet and it is telling me to set a value to the ranges before I refer to them, which I don't understand why.

What is error 91. I can't remember all the descriptions to go with numbers. I think it's data type mismatch or run-time? What is `ArrayList`? I get "User-defined type not defined" on that line. — Darren Bartrup-Cook, Mar 02 '23 at 10:58
Knew I'd seen and used ArrayList before - `CreateObject("System.Collections.ArrayList")` — Darren Bartrup-Cook, Mar 02 '23 at 11:02
It is recommended to avoid the use of `Select` and `Activate` which is covered [here](https://stackoverflow.com/a/10717999). If you won't take the advice, to avoid the first error, right before `prislistor.Range("A2:C7").Select`, you can use the following 2 lines: `If Not ThisWorkbook Is ActiveWorkbook Then ThisWorkbook.Activate: If Not prislistor Is ActiveSheet Then prislistor.Select`. The error is occurring because you can only select a cell on the active sheet and `prislistor` isn't active at the time (line 2). Also, you cannot select a worksheet if its workbook isn't active (line 1). — VBasic2008, Mar 02 '23 at 12:44
Thanks @VBasic2008, by avioding Select that error got solved. — Tim AKA Kaspian, Mar 02 '23 at 13:18
The main error is occurring because while writing the variable declarations, you have decided that they will be ranges while in the loop, you have changed your mind and decided to return the column numbers forgetting that they were declared as ranges. Either declare the variables as Longs or return the cells using the `Set` keyword and get rid of the trailing `.Column` occurrences. I guess that's what you get when using long unreadable lines. BTW, `Blad(i).Cells(1, 1).EntireRow` is the same as `Blad(i).Rows(1)`; `SearchOrder` is irrelevant while `SearchDirection` and `MatchCase` are default. — VBasic2008, Mar 02 '23 at 19:29

Darren Bartrup-Cook · Answer 1 · 2023-03-02T11:27:01.303

0

Error 91 - Object variable or with block variable not set.

I had to set your Array List to:

Dim Säkringar As Object 'List with fuses
Dim Blad As Object 'List with sheets

Set Säkringar = CreateObject("System.Collections.ArrayList")
Set Blad = CreateObject("System.Collections.ArrayList")

The problem is in FIND.
sKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Säkring", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

If "Säkring" isn't found then the range returned is set to Nothing. You can't get the column number of Nothing. So you need to check if the returned range is Nothing first and then set sKolumn to some default value if it is.

    Dim sKolumnRef As Range
    Set sKolumnRef = Blad(i).Cells(1, 1).EntireRow.Find(What:="Säkring", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If sKolumnRef Is Nothing Then
        sKolumn = 1
    Else
        sKolumn = sKolumnRef.Column
    End If

edited Mar 02 '23 at 11:27

answered Mar 02 '23 at 11:14

Darren Bartrup-Cook

18,362
1
23
45

Thank you @Darren for helping me! I'm a bit new to VBA, but I can't realy get the hang of this. I know for sure that the first row of the sheets in this list ´´´Blad.Add brududden Blad.Add rinnefors Blad.Add järpforsen Blad.Add rexed Blad.Add sälboda´´´ contains the word "Säkring". So as my brain goes the range shouldn't be set to Nothing, it should get a range. What am I mississing to understand? Sorry for my incompetence, I'm glad for your patience. – Tim AKA Kaspian Mar 02 '23 at 14:13
If the first row in those sheets does contain the word you're looking for then it should return a range and you won't have any problem getting the column number from it. But that one time that someone overtypes what you're looking for - or any other reason for it to vanish then you'll get an error and the user will see a "Object variable or with block variable not set", so for those one in a million chances it's worth putting in something meaningful to the user. Maybe rather than setting `sKolumn` to a default value you could add a custom error message. (Cont....) – Darren Bartrup-Cook Mar 02 '23 at 14:33
So if `sKolumn` is nothing then a message could pop up saying "Couldn't find Säkring in row 1. Please contact admin". This link should help with custom error messages: [VBA Throw / Raise Error – Err.Raise – Custom Error Msg](https://www.automateexcel.com/vba/throw-raise-error-err-raise/) – Darren Bartrup-Cook Mar 02 '23 at 14:35
Thanks @Darren I will add error related lines of code at the last stage of the coding, just because I want to be able to se what error I am geting and where in the code. I think I found out why i couldn't find the string I were looking for in the code. It had to do with that I had fixed the upper row in the sheets I were searching through. So instead of searching through that row it search through the second row and couldn't find the string. That is at least what I think. – Tim AKA Kaspian Mar 03 '23 at 13:21

VBasic2008 · Answer 2 · 2023-03-02T20:45:41.413

Avoiding Variable Declaration Errors

As I wrote in the comments, the first error is occurring if the prislistor worksheet is not the active sheet, while the second error is occurring because you have declared the column variables as Ranges instead of Longs.
You can use the following if you're sure the headers exist but I would recommend you apply what is illustrated in Darren Bartrup-Cook's answer; I basically do it all the time. I didn't apply it here because I don't know your next step regarding the columns.

Sub InfogaPrislista()
'
' InfogaPrislista Makro
' Adds a price list and updates the code in excel
'
    'Adding fuses to list
    
    Dim Säkringar As ArrayList 'List with fuses
    Set Säkringar = New ArrayList
    
    Säkringar.Add prislistor.Cells(4, 1)
    Säkringar.Add prislistor.Cells(5, 1)
    Säkringar.Add prislistor.Cells(6, 1)
    Säkringar.Add prislistor.Cells(10, 1)
    Säkringar.Add prislistor.Cells(11, 1)
    Säkringar.Add prislistor.Cells(12, 1)
    Säkringar.Add prislistor.Cells(13, 1)
    Säkringar.Add prislistor.Cells(14, 1)
    Säkringar.Add prislistor.Cells(15, 1)
    Säkringar.Add prislistor.Cells(20, 1)
    Säkringar.Add prislistor.Cells(21, 1)
    Säkringar.Add prislistor.Cells(25, 1)
    Säkringar.Add prislistor.Cells(26, 1)
    
    'Adding sheets to list
    
    Dim Blad As ArrayList 'List with sheets
    Set Blad = New ArrayList
    
    Blad.Add brududden
    Blad.Add rinnefors
    Blad.Add järpforsen
    Blad.Add rexed
    Blad.Add sälboda
    
    If prislistor.Range("B29") = "Enfas" Then
        
        ' Adds a specific fuse price list
        With prislistor
            .Range("A2:C7").Copy
            .Range("D2").Insert Shift:=xlToRight
            'Application.CutCopyMode = False ' the next line does it anyway
            .Range("E4:F7").ClearContents
            'If Not ThisWorkbook Is ActiveWorkbook Then ThisWorkbook.Activate
            'If Not prislistor Is ActiveSheet Then .Select
            '.Range("C29").Select
        End With
        
        ' Searches through the headings in every sheet in the sheets list
        ' for the different columns I set as ranges at row 6-8 in this code.
        
        ' If you keep the variables closer to the action,
        ' you will be able to spot such errors more easily.
        Dim sKolumn As Long 'Fuse Column
        Dim fKolumn As Long 'Fixed price kolumn
        Dim rKolumn As Long 'Variable price kolumn
        Dim i As Long 'Sheets counter
        
        For i = 0 To 4
            With Blad(i).Rows(1)
                ' the 2nd argument is 'After', important when looking for a 1st occurrence
                ' instead of 'xlValues', use 'xlFormulas' to make it work even if a column is hidden
                ' instead of 'xlPart', use 'xlWhole' if you're looking for the whole string
                ' since you're searching in a single row, 'SearchOrder' is irrelevant
                ' the default 'SearchDirection' parameter is 'xlNext' so you can safely omit it
                ' the default 'MatchCase' parameter is 'False' so you can safely omit it
                sKolumn = .Find(What:="Säkring", LookIn:=xlValues, LookAt:=xlPart).Column
                rKolumn = .Find(What:="Rörligtpris nät", LookIn:=xlValues, LookAt:=xlPart).Column
                fKolumn = .Find(What:="Fastpris nät", LookIn:=xlValues, LookAt:=xlPart).Column
            End With
            ' e.g.
            Debug.Print Blad(i).Name, sKolumn, rKolumn, fKolumn
        Next i
    
    End If

End Sub

Do I need to set a value to range before I refer to it? Error nr 91

2 Answers2

Avoiding Variable Declaration Errors