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:

1. Select all data in my worksheet
2. Name the selection to AllData
3. 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

CodePudding user response：

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