userform search not working only looking at 1 row

[PLwolves87]

Hi,

i have a workbook which has 10 worksheets, i have a userform which has a search button. each worksheet is controlled by a combobox, but the search is also based on a date which is in a text box.

so basically you select the worksheet in the combobox which i have named ("Account2") then you will select a date which is in textbox1 and then press button and then the 8 textboxes display the data.

COMBOBOX is the worksheet it needs to look at for the data
TEXTBOX1 is the date search


i have date in row 1 and row 2 on a test sheet, the problem i have is that its only looking at the first row, it doesn't drop down so when i select a date in TEXTBOX1 which is in row2 im getting the MSGBOX "date not found" but the data is there.. please see below code

Private Sub CommandButton1_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(Account2.Value)

Dim lr As Long
lr = sh.Range("A" & Rows.Count).End(xlUp).Row

Dim i As Long
If Application.WorksheetFunction.CountIf(sh.Range("A3:AL9999"), Me.TextBox1.Value) = 0 Then
MsgBox "Date not Found", vbOKOnly + vbInformation
Exit Sub
End If


For i = 2 To lr
If sh.Cells(i, "A").Value = Me.TextBox1.Text Then

TextBox2 = sh.Cells(i, "C").Value
TextBox3 = sh.Cells(i, "D").Value
TextBox4 = sh.Cells(i, "E").Value
TextBox5 = sh.Cells(i, "F").Value
TextBox6 = sh.Cells(i, "G").Value
TextBox7 = sh.Cells(i, "H").Value
TextBox8 = sh.Cells(i, "I").Value
TextBox9 = sh.Cells(i, "J").Value



End If
Next i
End Sub

 

[dmt32]

as you can see i have 3 more of these to do, so hope you can help, sorry i couldnt attached the file so hopefully the photos can help

If you require the same code multiple times then you would create a common code for each search

I have not tested but try following but see if this resolves / helps

Place in either standard module or your userform code page

Sub GetValues(ByVal sh As Object, ByVal SearchBox As Object, StartBox As Long)
    Dim c           As Long
    Dim Search      As Variant, m as Variant
    Dim Form        As Object
  
    Set Form = SearchBox.Parent
    Search = SearchBox.Value
  
    If Len(Search) > 0 Then
        'ensure entry is a date                   'corece string date to long        'invalid date entry
        If IsDate(Search) Then Search = CLng(DateValue(Search)) Else MsgBox "Invalid Date Entry", 16, "Invalid Entry": Exit Sub
    Else
        Exit Sub
   End If
  
    'find date in range
    m = Application.Match(Search, sh.Columns(1), 0)
  
    c = 3
    If Not IsError(m) Then
        'populate textboxes
        m = CLng(m)
        For i = StartBox To StartBox + 7: Form.Controls("TextBox" & i).Value = sh.Cells(m, c).Value: c = c + 1: Next i
        Else: MsgBox "Date Not Found", 48, "Not Found"
        End If
      
End Sub

and to call it from your userform, pass the worksheet & date (search) textbox objects & the start textbox index that the data is returned to

    Dim sh          As Worksheet
  
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
  
  
    GetValues sh, Me.TextBox1, 2
  
  
    GetValues sh, Me.TextBox10, 11

Dave

 

[PLwolves87]

If you require the same code multiple times then you would create a common code for each search

I have not tested but try following but see if this resolves / helps

Place in either standard module or your userform code page

Sub GetValues(ByVal sh As Object, ByVal SearchBox As Object, StartBox As Long)
    Dim c           As Long
    Dim Search      As Variant, m as Variant
    Dim Form        As Object
 
    Set Form = SearchBox.Parent
    Search = SearchBox.Value
 
    If Len(Search) > 0 Then
        'ensure entry is a date                   'corece string date to long        'invalid date entry
        If IsDate(Search) Then Search = CLng(DateValue(Search)) Else MsgBox "Invalid Date Entry", 16, "Invalid Entry": Exit Sub
    Else
        Exit Sub
   End If
 
    'find date in range
    m = Application.Match(Search, sh.Columns(1), 0)
 
    c = 3
    If Not IsError(m) Then
        'populate textboxes
        m = CLng(m)
        For i = StartBox To StartBox + 7: Form.Controls("TextBox" & i).Value = sh.Cells(m, c).Value: c = c + 1: Next i
        Else: MsgBox "Date Not Found", 48, "Not Found"
        End If
     
End Sub

and to call it from your userform, pass the worksheet & date (search) textbox objects & the start textbox index that the data is returned to

    Dim sh          As Worksheet
 
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
 
 
    GetValues sh, Me.TextBox1, 2
 
 
    GetValues sh, Me.TextBox10, 11

Dave

Hi Dave,

will give this a try but do i put this code in Private Sub CommandButton1_Click()

 

[dmt32]

do i put this code in Private Sub CommandButton1_Click()

only the code needed to call the common procedure from your commandbuttons

example:

Private Sub CommandButton1_Click()
  
    Dim sh          As Worksheet
    
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
    
    
    GetValues sh, Me.TextBox1, 2
    
'or for TextBox10 searches
 
    GetValues sh, Me.TextBox10, 11
    

End Sub

Dave

 

[PLwolves87]

If you require the same code multiple times then you would create a common code for each search

I have not tested but try following but see if this resolves / helps

Place in either standard module or your userform code page

Sub GetValues(ByVal sh As Object, ByVal SearchBox As Object, StartBox As Long)
    Dim c           As Long
    Dim Search      As Variant, m as Variant
    Dim Form        As Object
 
    Set Form = SearchBox.Parent
    Search = SearchBox.Value
 
    If Len(Search) > 0 Then
        'ensure entry is a date                   'corece string date to long        'invalid date entry
        If IsDate(Search) Then Search = CLng(DateValue(Search)) Else MsgBox "Invalid Date Entry", 16, "Invalid Entry": Exit Sub
    Else
        Exit Sub
   End If
 
    'find date in range
    m = Application.Match(Search, sh.Columns(1), 0)
 
    c = 3
    If Not IsError(m) Then
        'populate textboxes
        m = CLng(m)
        For i = StartBox To StartBox + 7: Form.Controls("TextBox" & i).Value = sh.Cells(m, c).Value: c = c + 1: Next i
        Else: MsgBox "Date Not Found", 48, "Not Found"
        End If
     
End Sub

and to call it from your userform, pass the worksheet & date (search) textbox objects & the start textbox index that the data is returned to

    Dim sh          As Worksheet
 
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
 
 
    GetValues sh, Me.TextBox1, 2
 
 
    GetValues sh, Me.TextBox10, 11

Dave

its not displaying the values in the textboxes GetValues sh, Me.TextBox10, 11

 

[PLwolves87]

only the code needed to call the common procedure from your commandbuttons

example:

Private Sub CommandButton1_Click()
 
    Dim sh          As Worksheet
   
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
   
   
    GetValues sh, Me.TextBox1, 2
   
'or for TextBox10 searches
 
    GetValues sh, Me.TextBox10, 11
   

End Sub

Dave

Hi Dave,

PERFECT!!! its working thank you so much

 

[dmt32]

Hi Dave,

PERFECT!!! its working thank you so much

Thats ok hopefully all fixed now

Dave