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

Question

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

Can you elaborate what you mean with *"what's wrong?"* I see a lot of room for improvement (including the strange code indentation you use, the use of `Select` and the fact that you convert a date into a string before writing it into a cell), but I guess that's not what you are talking about. If your code doesn't work: Have you used the debugger to check where it fails? Especially the content of the variables `LR` and `st`? Are you sure you want to work on the Active Sheet? — FunThomas, Jan 05 '23 at 11:39
**1.** You may also want to see [What is the difference between .text, .value, and .value2?](https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) and [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) **2.** To check for empty cell, I usually use `IF Len(Trim(rng.Value2)) <> 0 Then` — Siddharth Rout, Jan 05 '23 at 12:21
I've updated my code. So if i use ure code "IF Len(Trim(rng.Value2)) <> 0 Then" how can i use this with offset? Is it possible to update my code because i don't have the knowledge about it. Thx in advance. — johnny sterckx, Jan 05 '23 at 13:19

score 0 · Answer 1 · answered Jan 05 '23 at 14:08

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.

Ok found it, it must be If Not Len(Trim(rng.Offset(, 4).Value2)) <> 0 Then 'Offset — johnny sterckx, Jan 05 '23 at 17:57
In your question, you stated that it should only add time and date if it's _not_ empty. Using `If Not ...` then you're only adding date/time if it's empty. I'm glad you got it to work however. — Notus_Panda, Jan 05 '23 at 20:10
Thx for your answer, the problem now is that all found values are now adjusted. See screenshot. [![enter image description here](https://i.stack.imgur.com/7rIjd.png)](https://i.stack.imgur.com/7rIjd.png) — johnny sterckx, Jan 05 '23 at 14:55

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

1 Answers1