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]

Hi,
untested but see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim m           As Variant, Search As Variant
    Dim i             As Long
    Dim sh          As Worksheet
    
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
    
    Search = Me.TextBox1.Value
    If Not IsDate(Search) Then Exit Sub Else Search = CLng(DateValue(Search))
    
    m = Application.Match(Search, sh.Columns(1), 0)
    
    If Not IsError(m) Then
    
        m = CLng(m)
        For i = 2 To 9
            Me.Controls("TextBox" & i).Value = sh.Cells(m, i + 1).Value
        Next i
        
    Else
    
        MsgBox "Date Not Found", 64, "Not Found"
        
    End If
    
End Sub

I have assumed that the dates in the search range are real dates


Hope Helpful

Dave

 

[PLwolves87]

Hi,
untested but see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim m           As Variant, Search As Variant
    Dim i             As Long
    Dim sh          As Worksheet
   
    Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)
   
    Search = Me.TextBox1.Value
    If Not IsDate(Search) Then Exit Sub Else Search = CLng(DateValue(Search))
   
    m = Application.Match(Search, sh.Columns(1), 0)
   
    If Not IsError(m) Then
   
        m = CLng(m)
        For i = 2 To 9
            Me.Controls("TextBox" & i).Value = sh.Cells(m, i + 1).Value
        Next i
       
    Else
   
        MsgBox "Date Not Found", 64, "Not Found"
       
    End If
   
End Sub

I have assumed that the dates in the search range are real dates


Hope Helpful

Dave

Hi Dave,

yes they are real dates, and the code you have done above is perfect, THANK YOU VERY MUCH!!

 

[dmt32]

Hi Dave,

yes they are real dates, and the code you have done above is perfect, THANK YOU VERY MUCH!!

Glad suggestion resolved the issue & appreciate your feedback

Dave

 

[PLwolves87]

Hi Dave,

yes they are real dates, and the code you have done above is perfect, THANK YOU VERY MUCH!!

hi dave,

i still have one problem, because i have textbox1 which looks for date and then once found it updates the below
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

i have textbox 10 which will do the same function as Textbox1 but then updates the below, i was hoping the code you provided id be able to just change the textbox1 to textbox 10 and then the above textboxs which need updating to the below, but the code you have given me over rides the above textboxes, do you have code so that i can change the textbox and the cells for each command button?
textbox11
textbox12
textbox13
textbox14
textbox15
textbox16
textbox17
textbox18

 

[PLwolves87]

hi dave,

i still have one problem, because i have textbox1 which looks for date and then once found it updates the below
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

i have textbox 10 which will do the same function as Textbox1 but then updates the below, i was hoping the code you provided id be able to just change the textbox1 to textbox 10 and then the above textboxs which need updating to the below, but the code you have given me over rides the above textboxes, do you have code so that i can change the textbox and the cells for each command button?
textbox11
textbox12
textbox13
textbox14
textbox15
textbox16
textbox17
textbox18

sorry dave, i did try this but it didnt bring any data back

Dim m As Variant, Search As Variant
Dim i As Long
Dim sh As Worksheet

Set sh = ThisWorkbook.Worksheets(Me.Account2.Value)

Search = Me.TextBox10.Value
If Not IsDate(Search) Then Exit Sub Else Search = CLng(DateValue(Search))

m = Application.Match(Search, sh.Columns(1), 0)

If Not IsError(m) Then

m = CLng(m)
For i = 11 To 18
Me.Controls("TextBox" & i).Value = sh.Cells(m, i + 1).Value
Next i

Else

MsgBox "Date Not Found", 64, "Not Found"

 

[dmt32]

sorry dave, i did try this but it didnt bring any data back

Are you returning the correct columns?

For i = 11 To 18
Me.Controls("TextBox" & i).Value = sh.Cells(m, i + 1).Value
Next i

variable i + 1 starts are Column 12 is this correct?

Dave

 

[PLwolves87]

Are you returning the correct columns?

For i = 11 To 18
Me.Controls("TextBox" & i).Value = sh.Cells(m, i + 1).Value
Next i

variable i + 1 starts are Column 12 is this correct?

Dave

textbox1 should be one row and then textbox10 should be the 2nd row of data because a new row is created when a new date is added to the worksheet.

no the columns are incorrect, is there anyway of getting the below code in so this way i can just edit the textbox numbers to which column i need the lookup from please as ideally i need to do this for 5 different command buttons but all looking at date range. so the first one is working perfect with the code you gave me but just need to change it for the other buttons. (sorry to be a pain dave)

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

 

[dmt32]

If you are searching the same range for same date? then as written, match will only find the first instance in the range.

To understand further what you are trying to do would be helpful if you could place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it.

Dave

 

[PLwolves87]

on the attachment for totals(userform) you select your account (worksheet) from the combobox1, then in textbox1 you enter the date and then it will search for all of the data and the display in textbox2,3,4,5,6,7,8 and 9,

then in textbox10 i want to search for a 2nd date range based on the same account (worksheet) from the combobox1, it will then display the data in textbox11,12,13,and so on.

when you look at the spreadsheet if i enter in textbox1 03/03/2023 all this data should display in textbox2,3,4,5,6,7,8 and 9, and then if i enter into textbox10 10/03/2023 then all this data should display in textbox11,12,13 and so on

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