Finding the nth non empty cell in a row and returning value to text box vba

[jimalya]

Hey everyone,

I have a userform that has 7 fields that are connected to an autofilter. On the click of a button it filters the data. I also have 16 other text boxes that will be used to reference data from the resultant row from the filter.

The following image displays the data prior to the filter.
Notes_Grid1.PNG

This image shows the userform with the autofilter data field in. The combo boxes on the left are linked to the filter. The text boxes on the right are linked to the non-contiguous non blank cells adjacent to column G.

NotesPic.PNG

Also attached is an image showing the filtered data if that helps.

The text box with WC1 as the label will be the column header of the data in the first non blank cell and the data of the first non blank cell will be in the text box below that one. The text box with WC2 as the label will contain the column header of the second non blank cell and the text box below it will contain the data from the 2nd cell with data, and so on.

I've researched several approaches, but they all seem to be using the index function which is not compatible with VBA. If you need the actual file or current code, let me know.

In a way, it would be treated like an Access database and linking the remaining relative data in the row.

Thanks in advance,
Jimalya

[tigeravatar]

jimalya,

Its hard to say without seeing your code, but hopefully you can adapt something like this. Note that the code assumes your textboxes have a naming convention. The 'WC' textboxes that will hold the header are assumed to be named 'txtWC#' where # is 1, 2, ...8, and that the textboxes that will hold the notes are named 'txtWC#Note' where # is again 1, 2, ...8

    Dim CheckCell As Range
    Dim ActiveRecord As Range
    Dim arrData(1 To 8, 1 To 2) As String
    Dim DataIndex As Long
    Dim i As Long
    
    'Your code to filter the data and/or adjust the active record goes here
    
    'Now you are working with the active record
    'You can use Intersect to determine which columns to check
    'This will check columns H:O for the row of the active record
    For Each CheckCell In Intersect(ActiveRecord.EntireRow, Range("H:O"))
        If Len(CheckCell.Text) > 0 Then
            DataIndex = DataIndex + 1
            arrData(DataIndex, 1) = Cells(1, CheckCell.Column).Text
            arrData(DataIndex, 2) = CheckCell.Text
        End If
    Next CheckCell
    
    For i = 1 To DataIndex
        Controls("txtWC" & i).Text = arrData(i, 1)
        Controls("txtWC" & i & "Note").Text = arrData(i, 2)
    Next i

[jimalya]

Thanks tigeravatar! I'll give it shot.

[jimalya]

Hey tigeravatar,
I'm getting an object variable not set in this line:

For Each CheckCell In Intersect(ActiveRecord.EntireRow, Range("H:O"))

[jimalya]

Here's my entire code if this helps.

Option Explicit
Dim rSource    As Range
Dim lFld       As Long
Dim oCtrl      As MSForms.Control
Dim sCrit      As String

Private Sub ComboBox1_Change()
    sCrit = Me.ComboBox1.Value
    lFld = 1
End Sub
Private Sub ComboBox2_Change()
    sCrit = Me.ComboBox2.Value
    lFld = 2
End Sub
Private Sub ComboBox3_Change()
    sCrit = Me.ComboBox3.Value
    lFld = 3
End Sub
Private Sub ComboBox4_Change()
    sCrit = Me.ComboBox4.Value
    lFld = 4
End Sub
Private Sub ComboBox5_Change()
    sCrit = Me.ComboBox5.Value
    lFld = 5
End Sub


Private Sub CommandButton1_Click()

    
  
    Dim CheckCell As Range
    Dim ActiveRecord As Range
    Dim arrData(1 To 17, 1 To 2) As String
    Dim DataIndex As Long
    Dim i As Long
    
    'Your code to filter the data and/or adjust the active record goes here
    Rows("1:1").AutoFilter
    With frmFilter
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=1, Criteria1:="*" & ComboBox1.Value
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=2, Criteria1:="*" & ComboBox2.Value
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=3, Criteria1:="*" & ComboBox3.Value
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=4, Criteria1:="*" & ComboBox4.Value
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=5, Criteria1:="*" & ComboBox5.Value
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=6, Criteria1:="*" & ComboBox6.Value
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=7, Criteria1:="*" & ComboBox7.Value
    
    End With


   Dim r As Range
   Set r = Intersect(ActiveSheet.AutoFilter.Range, Range("A:A"))
   frmFilter.txtOptionEnd.Value = Application.WorksheetFunction.Subtotal(103, r) - 1
    'Now you are working with the active record
    'You can use Intersect to determine which columns to check
    'This will check columns H:O for the row of the active record
    For Each CheckCell In Intersect(ActiveRecord.EntireRow, Range("H:O"))
        If Len(CheckCell.Text) > 0 Then
            DataIndex = DataIndex + 1
            arrData(DataIndex, 1) = Cells(1, CheckCell.Column).Text
            arrData(DataIndex, 2) = CheckCell.Text
        End If
    Next CheckCell

    For i = 1 To DataIndex
        Controls("txtWC" & i).Text = arrData(i, 1)
        Controls("txtNotes" & i).Text = arrData(i, 2)
    Next i


End Sub

Private Sub CommandButton2_Click()
    Unload frmFilter
    frmFilter.Show
    UserForm_Initialize
End Sub


Private Sub CommandButton4_Click()
    Unload Me
End Sub


Private Sub UserForm_Initialize()
Rows("1:1").AutoFilter
Dim V1, V2, V3, V4, V5, V6, V7
With Workbooks("Notes").Sheets("data")
    V1 = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    V2 = .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    V3 = .Range("C2:C" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    V4 = .Range("D2:D" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    V5 = .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    V6 = .Range("F2:F" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    V7 = .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

With frmFilter
Call AddToList(V1, .ComboBox1)
Call AddToList(V2, .ComboBox2)
Call AddToList(V3, .ComboBox3)
Call AddToList(V4, .ComboBox4)
Call AddToList(V5, .ComboBox5)
Call AddToList(V6, .ComboBox6)
Call AddToList(V7, .ComboBox7)
End With
   
End Sub
Sub AddToList(myRange, myBox As Control)
Dim e
Debug.Print myBox

With CreateObject("scripting.dictionary")
    .comparemode = 1
    
    For Each e In myRange
        If Not .exists(e) Then .Add e, Nothing
    Next
    
    If .Count Then myBox.List = Application.Transpose(.keys)
   End With


End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
End Sub

[tigeravatar]

jimalya,

In your userform you have a textbox that display what record the user is currently viewing ('1 of 3', '2 of 3', etc). You also have Previous and Next buttons which either are not yet coded or you did not provide their codes. I have assumed that the textbox displaying what record the user is currently viewing (the 1, 2, etc) is named txtActiveRecord, so you'll need to adjust the code to the correct name for that textbox.

With that adjustment, this should work for you (this is the CommandButton1_Click code):

Private Sub CommandButton1_Click()
    
    Dim CheckCell As Range
    Dim FilteredCell As Range
    Dim rngFiltered As Range
    Dim ActiveRecord As Range
    Dim i As Long
    
    'Filter the data
    With ActiveSheet.Range("A1:X200")
        .AutoFilter 'Remove any active filters
        .AutoFilter Field:=1, Criteria1:="*" & ComboBox1.Value
        .AutoFilter Field:=2, Criteria1:="*" & ComboBox2.Value
        .AutoFilter Field:=3, Criteria1:="*" & ComboBox3.Value
        .AutoFilter Field:=4, Criteria1:="*" & ComboBox4.Value
        .AutoFilter Field:=5, Criteria1:="*" & ComboBox5.Value
        .AutoFilter Field:=6, Criteria1:="*" & ComboBox6.Value
        .AutoFilter Field:=7, Criteria1:="*" & ComboBox7.Value
    End With
    
    'Check for data
    With Intersect(ActiveSheet.AutoFilter.Range, Range("A:A"))
        On Error Resume Next
        Set rngFiltered = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    End With
    
    If rngFiltered Is Nothing Then
        'No data, display message and exit sub
        MsgBox "There is no data with the selected filters", , "No Data"
        Exit Sub
    End If
    
    'There is data, show the number of visible rows (number of filtered records)
    frmFilter.txtOptionEnd.Value = rngFiltered.Cells.Count
    
    'Get the active record
    'This is based on the number that is in a textbox named txtActiveRecord which hold the number for the selected record
    'The textbox's number is adjusted by the Previous/Next buttons
    'If empty, the textbox's number should be set to 1
    If Len(txtActiveRecord.Text) = 0 Then txtActiveRecord.Text = 1
    For Each FilteredCell In rngFiltered.Cells
        i = i + 1
        If i = CLng(txtActiveRecord.Text) Then
            Set ActiveRecord = FilteredCell
            Exit For
        End If
    Next FilteredCell
    
    'Get non-blank entries for the ActiveRecord and populate the appropriate textboxes
    i = 0
    For Each CheckCell In Intersect(ActiveRecord.EntireRow, Range("H:O"))
        If Len(CheckCell.Text) > 0 Then
            i = i + 1
            Controls("txtWC" & i).Text = Cells(1, CheckCell.Column).Text
            Controls("txtNotes" & i).Text = CheckCell.Text
        End If
    Next CheckCell

End Sub

[jimalya]

Wow! That is perfect.

You are correct, I haven't coded that yet, but thanks for knocking that part out for me. That's great and it works just like it should. Would you happen to know how to add the previous and next functionality off the top of your head? If not, that's fine because you've helped out tremendously! However if you can, the buttons are btnPrevious and btnNext. Wow, I'm amazed.

[tigeravatar]

jimalya,

They would be very simple.
btnPrevious:

Private Sub btnPrevious_Click()
    
    If Me.txtActiveRecord.Text = 1 Then
        Me.txtActiveRecord.Text = Me.txtOptionEnd.Text
    Else
        Me.txtActiveRecord.Text = Me.txtActiveRecord.Text - 1
    End If
    CommandButton1_Click
    
End Sub

btnNext:

Private Sub btnNext_Click()
    
    If Me.txtActiveRecord.Text = Me.txtOptionEnd.Text Then
        Me.txtActiveRecord.Text = 1
    Else
        Me.txtActiveRecord.Text = Me.txtActiveRecord.Text + 1
    End If
    CommandButton1_Click
    
End Sub

[jimalya]

You are a magician wise one. Thank you so much. You've been a great help!!

[tigeravatar]

You're very welcome