"run time error 91 object variable or with block variable not set" working with ranges

Question

I'm a VBA beginner. I get a 'run time error 91 object variable or with block variable not set' trying to run the following code. Here is what I'm trying to do:

Select all data in my worksheet
Name the selection to AllData
Go through this range and wherever 'X' is found in Column D, change value of Column W to 'No'.

The error refers to the 9th row where I try to set No_Of_Rows to the row count of my range. Apparently I should 'Set' my object beforehand..? But I don't know what I'm doing wrong...

Thanks in advance for your help.

Sub ChngColW()
    Dim AllData As Range
    Dim No_Of_Rows As Integer
    Dim i As Long
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "AllData"
    No_Of_Rows = AllData.Rows.Count
    For i = 1 To No_Of_Rows
        If AllData("D" & i).Value = "X" Then
        AllData("W" & i).Value = "No"
    End If
    Next i End Sub

AllData is the name of a range, not the name of a range variable. Use Set AllData = Selection instead of naming it. — Rory
– Rory, Commented Jun 7, 2022 at 9:12

VBasic2008 · Accepted Answer · 2022-06-07 10:15:51Z

A Quick Fix

Sub ChangeColumnW()
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
     
    ' Reference the range.
    Dim AllData As Range
    Set AllData = ws.Range("A1", ws.Range("A1").End(xlToRight).End(xlDown))
    
    ' Name the range (useless).
    'AllData.Name = "AllData"
    ' Now you could refer to it with 'Range("AllData")' but what's the benefit?
    
    Dim i As Long
    
    For i = 2 To AllData.Rows.Count
        If AllData(i, "D").Value = "X" Then
            AllData(i, "W").Value = "No"
        End If
    Next i

End Sub

An Alternative

Just 'looking' in the column not caring about the range including some improvements.

Sub ChangeColumnNoConstants()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    
    Dim r As Long
    Dim fCount As Long
    
    For r = 2 To lRow
        If StrComp(CStr(ws.Cells(r, "D").Value), "X", vbTextCompare) = 0 Then
            ws.Cells(r, "W").Value = "No"
            fCount = fCount + 1
        End If
    Next r
    
    MsgBox "Found 'X' in " & fCount & " cells.", vbInformation
    
End Sub

Sub ChangeColumnUsingConstants()
    
    Const sCol As String = "D"
    Const sCrit As String = "X"
    
    Const dCol As String = "W"
    Const dCrit As String = "No"
    
    Const fRow As Long = 2
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row
    
    Dim r As Long
    Dim fCount As Long
    
    For r = fRow To lRow
        If StrComp(CStr(ws.Cells(r, sCol).Value), sCrit, vbTextCompare) = 0 Then
            ws.Cells(r, dCol).Value = dCrit
            fCount = fCount + 1
        End If
    Next r
    
    MsgBox "Found '" & sCrit & "' in " & fCount & " cells.", vbInformation
    
End Sub

Thank you so much! I tried the simpler code using Constants and it worked liked a charm :)

Collectives™ on Stack Overflow

"run time error 91 object variable or with block variable not set" working with ranges

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related