Object doesn't support this property or method (Error 438)

I have error 438 message but cannot figure out why ? Do you have an idea ? For each cells in my range B5:B28, I want to check string value and print a number accordingly to that string to the cell next to the right.

Public Sub RolloutStage()

    Dim rng As Range
    For Each rng In Worksheets("backEnd_Lost&Found").Range("B5:B28")

        If Worksheets("backEnd_Lost&Found").rng.Value = "Live" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 8
        
        ElseIf Worksheets("backEnd_Lost&Found").rng.Value = "Configuration" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 7
        
        ElseIf Worksheets("backEnd_Lost&Found").rng.Value = "Testing" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 6
        
        ElseIf Worksheets("backEnd_Lost&Found").rng.Value = "Planned" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 5
        
        ElseIf Worksheets("backEnd_Lost&Found").rng.Value = "Pending" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 4
        
        ElseIf Worksheets("backEnd_Lost&Found").rng.Value = "Not planned" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 3
        
        ElseIf Worksheets("backEnd_Lost&Found").rng.Value = "No contact" Then
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 2
        
        Else
        
        Worksheets("backEnd_Lost&Found").rng.Value = "Not interested"
        Worksheets("backEnd_Lost&Found").ActiveCell.Offset(0, 1).Value = 1
        
        End If
    Next

End Sub

CodePudding user response：

Here is the correct version. Thank you

Public Sub RolloutStage()
    
        Dim rng As Range
        For Each rng In Worksheets("backEnd_Lost&Found").Range("B5:B28")
    
            If rng.Value = "Live" Then
            rng.Offset(0, 1).Value = 8
            
            ElseIf rng.Value = "Configuration" Then
            rng.Offset(0, 1).Value = 7
            
            ElseIf rng.Value = "Testing" Then
            rng.Offset(0, 1).Value = 6
            
            ElseIf rng.Value = "Planned" Then
            rng.Offset(0, 1).Value = 5
            
            ElseIf rng.Value = "Pending" Then
            rng.Offset(0, 1).Value = 4
            
            ElseIf rng.Value = "Not planned" Then
            rng.Offset(0, 1).Value = 3
            
            ElseIf rng.Value = "No contact" Then
            rng.Offset(0, 1).Value = 2
            
            Else
            
            rng.Value = "Not interested"
            rng.Offset(0, 1).Value = 1
            
            End If
        Next
    
    End Sub

CodePudding user response：

Conditionally Populate Adjacent Cells

In your code...

You cannot use a variable as an object's property: instead of ws.rng.Value, use rng.Value.
A worksheet has no ActiveCell property: instead of ws.ActiveCell, use rng.

The For Each...Next Loop

What does the For Each cell In rg.Cells line do? You could think of it that in the first iteration, it writes the following invisible line right below:
```
Set cell = rg.Cells(1) ' B5
```
So in the continuation, you will use this cell to check the value and again use this cell to write another value to the cell adjacent to the right.
In the next iteration, the invisible line looks like this:
```
Set cell = rg.Cells(2) ' B6
```
etc.

An Improvement

Public Sub RolloutStage()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("backEnd_Lost&Found")
    Dim srg As Range: Set srg = ws.Range("B5:B28")

    Dim sCell As Range, dCell As Range
    
    For Each sCell In srg.Cells
        Set dCell = sCell.Offset(, 1)
        Select Case CStr(sCell.Value)
            Case "Live": dCell.Value = 8
            Case "Configuration": dCell.Value = 7
            Case "Testing": dCell.Value = 6
            Case "Planned": dCell.Value = 5
            Case "Pending": dCell.Value = 4
            Case "Not planned": dCell.Value = 3
            Case "No contact": dCell.Value = 2
            Case Else: sCell.Value = "Not interested": dCell.Value = 1
        End Select
    Next sCell
 
End Sub