VBA Code Error "Invalid or unqualified reference"

Question

I am very new to vba coding . In the worksheet I am trying to add an additional column using macros-column (Q)by checking conditions in simultaneous columns J,K,O).So if certain conditions pass in each of the columns I want a value to be entered in Q column for the respective row. This is the piece of code I put together .

Option Explicit

Sub Button2_Click()

Sheet1.Activate

Dim i As Long
    
    For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
    'Check so that we only process non-empty cells
    '(just in case there is an empty cell part-way through the data)
        If Cells(i, 10).Value = "No" And Cells(i, 15) <= 0 Then
        Cells(i, 17) = "Pending with employee"
        Else
        If Cells(i, 10).Value = "No" And Cells(i, 15) >= 0 And Cells(i, 11) = "No Action Pending" Then
        Cells(i, 17) = "Pending with employee"
        Else
        If Cells(i, 10).Value = "No" And Cells(i, 15) >= 0 And Cells(i, 11) = "Pending With Manager" Then
        Cells(i, 17) = "Pending with Manager"
        Else
        If Cells(i, 10).Value = "Yes" And Cells(i, 15) >= 0 And Cells(i, 11) = "No Action Pending" Then
        Cells(i, 17) = "All Done"
        
        'If Not IsEmpty(.Cells(i, "B").Value) Then
         ' If .Cells(i, "E").Value = "NA" Then'
         'ThisWorkbook.Worksheets("CTCto TCC Conversion").Cells(i, "F").Value = "NA" '
         
                End If
         
         End If
         End If
          End If
          
        Next i
        
    End With
        MsgBox "Column Created"
 End Sub

It throws me an error Invalid or unqualified reference .Please help me if there any errors that need to be rectified for the code to run .

Thanks

You need a `With ...` statement for `.Cells` and `.Rows.Count` to refer to. Or remove the `.` in front of those calls to implicitly refer to the `ActiveSheet`, which is what you do elsewhere. — BigBen, Aug 27 '21 at 14:35

score 1 · Answer 1 · answered Aug 28 '21 at 03:27

Using the `With` Statement

BigBen has already answered your question. Here's an example with a little extra.
Check the two lines containing .Cells(i, 15).Value. One of them should probably have the equal sign removed.
If you write several conditions in one line of code, all of them will be evaluated even if the first one is already True (or False) making it less efficient than writing each condition in a new line. In practice, you most often won't feel a difference. I cannot decide which one is more readable or maintainable for you though.

Option Explicit

Sub Button2_Click()

    With Sheet1
        Dim i As Long
        For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            If .Cells(i, 10).Value = "No" Then
                If .Cells(i, 15).Value <= 0 Then
                    .Cells(i, 17).Value = "Pending with employee"
                Else
                    If .Cells(i, 11).Value = "No Action Pending" Then
                        .Cells(i, 17).Value = "Pending with employee"
                    ElseIf .Cells(i, 11).Value = "Pending With Manager" Then
                        .Cells(i, 17).Value = "Pending with Manager"
                    End If
                End If
            ElseIf .Cells(i, 10).Value = "Yes" Then
                If .Cells(i, 15).Value >= 0 Then
                    If .Cells(i, 11).Value = "No Action Pending" Then
                        .Cells(i, 17).Value = "All Done"
                    End If
                End If
            End If
        Next i
    End With
    
    MsgBox "Column Created"

End Sub

VBA Code Error "Invalid or unqualified reference"

1 Answers1

Using the With Statement

Using the `With` Statement