delete row function

Question

update: This is what i have so far, its supposed to search if an entry exists and if it has an in time, it throws an error 91 on line 3. i want the code to search for preexisting entries and update the time.

Sub inout()
        Dim barcode As String
        Dim rng As Range
        Dim rownumber As Long, ws As Worksheet
    
        Set ws = ThisWorkbook.Worksheets("Sheet1")
    
        barcode = ws.Cells(2, 2).Value
    
        Set rng = ws.Columns("A").Find(What:=barcode, _
                            LookIn:=xlFormulas, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False)
        If Not (rng Is Nothing) Or rng.Offset(0, 2) > 0 Then
            'checking out...
            Set rng = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            rng.Value = barcode
            rng.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
            rng.Offset(0, 1).Value = Date
            rng.Offset(0, 2).Clear
        ElseIf rng Is Nothing Then
            'checking out...
            Set rng = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            rng.Value = barcode
            rng.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
            rng.Offset(0, 1).Value = Date
        Else
            'checking in...
            rng.Offset(0, 1).ClearContents
            rng.Offset(0, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
            rng.Offset(0, 2).Value = Date
        End If
    
        ws.Cells(2, 2) = ""
    End Sub

score 1 · Accepted Answer · answered Oct 04 '21 at 17:25

1

Please review How to avoid using Select in Excel VBA - it will make your code more reliable

Without the Select/Activate:

Sub inout()
    Dim barcode As String
    Dim rng As Range
    Dim rownumber As Long, ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    barcode = ws.Cells(2, 2).Value

    Set rng = ws.Columns("A").Find(What:=barcode, _
                        LookIn:=xlFormulas, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    
    If rng Is Nothing Then
        'checking out...
        Set rng = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        rng.Value = barcode
        rng.Offset(0, 1).NumberFormat = "m/d/yyyy h:mm AM/PM"
        rng.Offset(0, 1).Value = Date
    Else
        'checking in...
        rng.Offset(0, 1).ClearContents
        rng.Offset(0, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
        rng.Offset(0, 2).Value = Date
    End If
    
    ws.Cells(2, 2) = ""
End Sub

answered Oct 04 '21 at 17:25

Tim Williams

154,628
8
97
125

when i run the macro, nothing happens. :/ – wben113 Oct 06 '21 at 15:26
Did you try debugging? Put a breakpoint on the `barcode = ...` line and step through the code using F8 – Tim Williams Oct 06 '21 at 15:30
alright, ive gotten to code to actually function now however, it only updates the "IN" column instead of out – wben113 Oct 07 '21 at 23:40
Working example - https://drive.google.com/file/d/1mzVEXC_ohNUVSjlD2sHQqoyd-AG81439/view?usp=sharing – Tim Williams Oct 08 '21 at 00:21
thanks for the example tim! my only issue now is that once there is already an entry it wont update the out column. I believe ill need an elseif statement though im not too sure – wben113 Oct 08 '21 at 13:24
i updated my question with the code i tried aswell – wben113 Oct 08 '21 at 14:28

delete row function

1 Answers1