VBA Checking if a Value Exists in Column

user_15353792 0
Viewed 152

I'm writing some VBA to check if a value exists in a column.

lRowStatic = Worksheets("GLMapping_Static").Cells(Rows.Count, 1).End(xlUp).Row 
lRow = Worksheets("GLMapping").Cells(Rows.Count, 1).End(xlUp).Row 
 For i = 1 To lRow
    If IsError(Application.Match(Worksheets("GLMapping").Cells(i, 1).Value, Worksheets("GLMapping_Static").Range(Cells(1, 1), Cells(lRowStatic, 1)), 0)) Then
        MsgBox "Its not in the range"
    Else
        MsgBox "Its in the range"
    End If 
 Next i

I'm confused because this code appears to work well if the "GLMapping_Static" worksheet is currently activated. If the "GLMapping" worksheet is currently activated, then I get a 1004 error.

Any idea what is causing this? I assumed there was a cell reference that didn't include a worksheet name, but I'm not seeing one.

Thanks

vba excel
2 Answers

Qualifying Objects

  • The critical part is the expression

     Worksheets("GLMapping_Static").Range(Cells(1, 1), Cells(lRowStatic, 1))

    where Cells are not qualified so when you choose a different worksheet than GLMapping_Static, Cells will refer to the wrong worksheet resulting in a run-time error.

  • The first example is illustrating how to fully qualify objects (wb-ws-rg). To simplify, one could say that .Range, .Cells, .Rows, and .Columns belong to a worksheet (object), each .Worksheets belongs to a workbook (object), and each .Workbooks belongs to the Application (object).

  • The other examples are just showing the benefits of using variables and some possible improvements on other accounts.

The Code

Option Explicit

Sub Humongous()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim lRowStatic As Long
    lRowStatic = wb.Worksheets("GLMapping_Static") _
        .Cells(wb.Worksheets("GLMapping_Static").Rows.Count, 1).End(xlUp).Row
    
    Dim lRow As Long
    lRow = wb.Worksheets("GLMapping") _
        .Cells(wb.Worksheets("GLMapping").Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    For i = 1 To lRow
        If IsError(Application.Match(wb.Worksheets("GLMapping").Cells(i, 1) _
                .Value, wb.Worksheets("GLMapping_Static") _
                .Range(wb.Worksheets("GLMapping_Static") _
                .Cells(1, 1), wb.Worksheets("GLMapping_Static") _
                .Cells(lRowStatic, 1)), 0)) Then
            MsgBox "Its not in the range"
        Else
            MsgBox "Its in the range"
        End If
    Next i

End Sub

Sub Sheeted()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("GLMapping_Static")
    Dim sLast As Long: sLast = sws.Cells(sws.Rows.Count, 1).End(xlUp).Row
    Dim srg As Range: Set srg = sws.Range(sws.Cells(1, 1), sws.Cells(sLast, 1))
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("GLMapping")
    Dim dLast As Long: dLast = dws.Cells(dws.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    For i = 1 To dLast
        If IsError(Application.Match(dws.Cells(i, 1).Value, srg, 0)) Then
            MsgBox "Its not in the range"
        Else
            MsgBox "Its in the range"
        End If
    Next i
End Sub

Sub Ranged()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim srg As Range
    Dim sLast As Long
    With wb.Worksheets("GLMapping_Static")
        sLast = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set srg = .Range(.Cells(1, 1), .Cells(sLast, 1))
    End With
    
    Dim drg As Range
    Dim dLast As Long
    With wb.Worksheets("GLMapping")
        dLast = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set drg = .Range(.Cells(1, 1), .Cells(dLast, 1))
    End With
    
    Dim dCell As Range
    For Each dCell In drg.Cells
        If IsNumeric(Application.Match(dCell, srg, 0)) Then
            MsgBox "Its in the range"
        Else
            MsgBox "Its not in the range"
        End If
    Next i

End Sub
user_98140690

You can do something like this.

Sub TryMe()

Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws1 = Sheets("GLMapping_Static")
Set ws2 = Sheets("GLMapping")
wb.Activate
ws1.Select

lRowStatic = ws1.Cells(Rows.Count, 1).End(xlUp).Row
lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
 For i = 1 To lRow
    If IsError(Application.Match(ws2.Cells(i, 1).Value, ws1.Range(Cells(1, 1), Cells(lRowStatic, 1)), 0)) Then
        MsgBox "Its not in the range"
    Else
        MsgBox "Its in the range"
    End If
 Next i
 
End Sub
user_52126140
Related