Even though the cell isn't empty, this code is seeing it as empty-CodePudding

This code is for something I'm doing at work, it's outputting "No patient reference!" in a MsgBox. If I remove the if that checks if the cell is IsEmpty I get "Patient not found" in a MsgBox. It seems like I'm missing something and I'm not sure what, can anyone help?

Private Sub CommandButton2_Click()

Dim emptytest2 As Boolean

emptytest2 = IsEmpty(Sheet1.Range("C28").Value)

If emptytest2 = False Then
   MsgBox "No patient reference!"
   End
End If

Dim found As Range
Dim band1 As Range
Dim foundoff As Range
Set found = Sheet2.Columns("B").Find(what:=Sheet1.Range("C28").Value, LookIn:=xlValues, lookat:=xlWhole)

If Not found Is Nothing Then
    Set band1 = Sheet1.Range("C29")
    MsgBox found & " - Data found for patient in cell " & found.Address
    Set foundoff = Range(found.Address).Offset(, 30)
    band1.Copy
    Sheet2.Range(foundoff.Address).PasteSpecial
Else
    MsgBox "Patient not found"
    End
End If

MsgBox "Successfully added band cutoff data to " & found

End Sub

CodePudding user response：

Option Explicit

Private Sub CommandButton2_Click()

    Dim ref As Range, found As Range, rng As Range
    
    Set ref = Sheet1.Range("C28")
    If Len(ref.Value) = 0 Then
        MsgBox "No patient reference!", vbExclamation
        Exit Sub
    End If
   
    Set found = Sheet2.Columns("B:B").Find(what:=ref.Value, _
        LookIn:=xlValues, lookat:=xlWhole)

    If found Is Nothing Then
        MsgBox "Patient Ref '" & ref.Value & "' not found", vbExclamation
        Exit Sub
    Else
        Set rng = found.Offset(, 30) ' col AF
        rng.Value = ref.Offset(1) ' C29
        MsgBox "Patient Ref: '" & ref.Value & "' found in cell " & found.Address
    End If
    MsgBox "Successfully added " & rng.Value & " to " & rng.Address

End Sub