What"s wrong with my code (check if cell is empty)-CodePudding

What is wrong here? When a number is found check if near cells are empty. Else time and date in this cells.

LR = Cells(Rows.Count, "B").End(xlUp).Row
st = TextBox1.Value
If st = "" Then
MsgBox "textbox is empty"
Exit Sub
End If

For j = LR To 1 Step -1
      code = Range("B" & j).Text
      If code = st Then
        Range("B" & j).Select
        
        If ActiveCell.Offset(, 4).Value <> vbNullString Then
            ActiveCell.Offset(, 4) = Format(Now, "dd/mm/yyyy ")
            
         If ActiveCell.Offset(, 5).Value <> vbNullString Then
            ActiveCell.Offset(, 5) = Format(Now, "HH:MM")
            
        Exit For
        
        End If
    End If
End If

CodePudding user response：

Since you sorta already got your answer from the rest there, I'll delete my answer if they want to post theirs.

As for adjusting your code (and indentations), here goes:

Sub checkIfEmpty()
    Dim LR As Long, j As Long
    Dim st As String
    Dim code As String
    Dim rng As Range
    
    st = ActiveWorkbook.ActiveSheet.TextBox1.Value
    LR = Range("B" & Rows.Count).End(xlUp).Row
    
    If Len(Trim(st)) = 0 Then
        MsgBox "Textbox is empty"
        Exit Sub
    End If
    
    For j = LR To 1 Step -1 'not sure why you're going backwards if you're not deleting/adding rows
        'no headers btw? If you do have headers, change it to For j = LR To 2 Step -1
        Set rng = Range("B" & j)
        code = rng.Value2
        If code = st Then
            If Len(Trim(rng.Offset(, 4).Value2)) <> 0 Then 'Offset basically is looking at the range <in this case> 4 columns further so then you take its value
                rng.Offset(, 4).Value2 = Format(Now, "dd/mm/yyyy")
            End If
            If Len(Trim(rng.Offset(, 5).Value2)) <> 0 Then
                rng.Offset(, 5).Value2 = Format(Now, "HH:MM")
            End If
        End If
    Next j 'don't put this inside your if ;)
End Sub

Hope that helps and as I said, let me know if I need to delete my answer.

CodePudding user response：

Thx for your answer, the problem now is that all found values are now adjusted. See screenshot.