CODEWITHSUNDEEP
excelvba
ProteinStack
ProteinStack

Reputation: 13

Check if cell is blank, then show the value of adjacent cell in userform textbox

First time posting here.

I need some help!

So I am trying to create a loop upon initialization of user form. Basically when I click the user form, the text box on top will show the name of the person with a blank adjacent cell

Private Sub UserForm_Initialize()

    Dim i As Long
    Set wb = ThisWorkbook.Sheets("Sheet1")

    With wb
    
    i = 2

    Do Until IsEmpty(Cells(i, 2).Value) = False

        'Check if cell i, 2 is blank (in this case Range B2 is blank so code will proceed)

        If IsEmpty(Cells(i, 2).Value) = True Then
    
            'Get value of adjacent cell and place in text box
            Textbox1.Value = Sheets("Sheet1").Cells(i, 1).Value
            'value is name of person with blank adjacent cell value

        End If
    
        i = i + 1
        

    Loop

    'code will loop until there is no longer any blanks in column B
    End With
End Sub

Range B2 is blank

Range B2 is blank

Range A2 should be value of text box

Range A2 should be value of text box

Upvotes: 1

Views: 423

Answers (2)

Usama El Kady
Usama El Kady

Reputation: 41

try this code using for loop

Private Sub UserForm_Initialize()
Dim mysheet As Worksheet
Dim LastRow As Long

Set mysheet = ActiveSheet
'find the last row number of a range using Find Function
  LastRow = mysheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For i = 2 To LastRow
    If IsEmpty(Cells(i, 2).Value) Then
    Me.TextBox1.Text = Cells(i, 1).Value ' add person's name to textbox1
    End If
Next i
End Sub

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54807

A Do...Loop

Option Explicit

Private Sub UserForm_Initialize()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim i As Long: i = 2

    Do Until IsEmpty(ws.Cells(i, "B").Value)
        i = i + 1
    Loop
    
    TextBox1.Value = ws.Cells(i, "A").Value

End Sub

Here are some other flavors doing the same:

Sub DoLoop2()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim i As Long: i = 2

    Do
        If IsEmpty(ws.Cells(i, 2).Value) Then Exit Do
        i = i + 1
    Loop
    TextBox1.Value = ws.Cells(i, 1).Value

End Sub

Sub DoLoop3()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim i As Long: i = 2

    Do
        If IsEmpty(ws.Cells(i, 2).Value) Then
            TextBox1.Value = ws.Cells(i, 1).Value
            Exit Do
        End If
        i = i + 1
    Loop

End Sub

Upvotes: 1

Related Questions