![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Guest
Posts: n/a
|
Does anyone know how to create a userform with 3 textboxs to search the excel with repeated values. So user can enter 3 values to search a specific data or enter 1 or 2 values to find the first value found in the excel. The worksheet has the following format Weight Color Code ------ ----- ---- 30 40 50 ------ ----- ---- 30 20 50 ------------------ 30 40 30 |
|
|
|
#2 |
|
Guest
Posts: n/a
|
I am new in VBA and I have been spending 10 hours a day for the last 7 days to came up with the code. But this code can't work. Can someone modify the code to make it work? Please
Private Sub SearchButton_Click() MillToFind = tbMillToFind.Text GrdeToFind = tbGrdeToFind.Text ColrToFind = tbColrToFind.Text BswtToFind = tbBswtToFind.Text LongGradeDescriptionToFind = tbLongGradeDescription.Text Set e = Columns(2).Find(What:=GrdeToFind, LookAt:=xlWhole) Set f = Columns(3).Find(What:=ColrToFind, LookAt:=xlWhole) Set g = Columns(4).Find(What:=BswtToFind, LookAt:=xlWhole) If f Is Nothing Then MsgBox ColrToFind & "Colour Code was not found.", vbInformation, "Result" With tbColrToFind .SelStart = 0 .SelLength = 100 .SetFocus End With Exit Sub ElseIf f = "" Then If g Is Nothing Then MsgBox BswtToFind & "Basis Weight was not found.", vbInformation, "Result" With tbBswtToFind .SelStart = 0 .SelLength = 100 .SetFocus End With Exit Sub ElseIf g = "" Then Else g.Activate Unload Me End If Else If g Is Nothing Then MsgBox BswtToFind & "Basis Weight was not found.", vbInformation, "Result" With tbBswtToFind .SelStart = 0 .SelLength = 100 .SetFocus End With Exit Sub Else If g = "" Then f.Activate Unload Me Else If f.Address = g.Offset(0, -1).Address Then f.Activate: Unload Me Else Set g = Columns(4).Find(What:=BswtToFind, LookAt:=xlWhole) Do Until g Is Nothing If g.Offset(0, -1).Value = ColrToFind Then Exit Do End If g.FindNext(After:=ActiveCell).Activate Loop 'Do Until False = True ' Setup Endless Loop!! 'Cells.FindNext(After:=ActiveCell).Activate ' ' Note that This assumes that Color is 1 Columns before ' ' Weight ' If ActiveCell.Offset(0, -1).Address = g.Address Then ' ' Do What you need to Do HERE ' MsgBox f.Address: ' MsgBox g.Address: ' ' Exit Do ' End If 'Loop 'Do ' Set f = Columns(3).FindNext(After:=f) 'Loop Until f.Offset(0, 0).Value = g.Offset(0, -1).Value Or f Is Nothing 'If f.Address = g.Offset(0, -1).Address Then ' MsgBox f.Address: ' MsgBox g.Address: ' f.Activate: ' Unload Me 'Else MsgBox "No Data" 'End If End If End If End If End If End Sub |
|
|
|
#3 |
|
Guest
Posts: n/a
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|