VBA : SEARCHING problem

[Pongwipat]

I want to create userform for searching between two IDS. For example If I put 150002(combobox3) and 150006(combobox4) then ID: 150002,150003,150004,150005,150006 will be showed in lisbox when searching.

'Case 5/3
'ID1<ID2

I try to use this code but it is not working?




If ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value < ComboBox4.Value Then


For sat = 2 To Sheets("Sheet1").Cells(10000, "a").End(xlUp).Row
If s >= (ComboBox3.Value - 150000) And s <= (ComboBox4.Value - 150000) Then


ListBox1.AddItem
ListBox1.List(s, 0) = Sheets("Sheet1").Cells(sat, "A")
ListBox1.List(s, 1) = Sheets("Sheet1").Cells(sat, "B")
ListBox1.List(s, 2) = Sheets("Sheet1").Cells(sat, "C")
ListBox1.List(s, 3) = Sheets("Sheet1").Cells(sat, "D")
ListBox1.List(s, 4) = Sheets("Sheet1").Cells(sat, "E")
ListBox1.List(s, 5) = Sheets("Sheet1").Cells(sat, "F")
ListBox1.List(s, 6) = Sheets("Sheet1").Cells(sat, "G")
ListBox1.List(s, 7) = Sheets("Sheet1").Cells(sat, "H")
ListBox1.List(s, 8) = Sheets("Sheet1").Cells(sat, "I")
s = s + 1


End If: Next

End If




Anyone can help or suggest?

[Pongwipat]

This code : I changed sheet1 to sheet2.


If ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value < ComboBox4.Value Then



For sat = 2 To Sheets("Sheet2").Cells(10000, "a").End(xlUp).Row
If s >= (ComboBox3.Value - 150000) And s <= (ComboBox4.Value - 150000) Then


ListBox1.AddItem
ListBox1.List(s, 0) = Sheets("Sheet2").Cells(sat, "A")
ListBox1.List(s, 1) = Sheets("Sheet2").Cells(sat, "B")
ListBox1.List(s, 2) = Sheets("Sheet2").Cells(sat, "C")
ListBox1.List(s, 3) = Sheets("Sheet2").Cells(sat, "D")
ListBox1.List(s, 4) = Sheets("Sheet2").Cells(sat, "E")
ListBox1.List(s, 5) = Sheets("Sheet2").Cells(sat, "F")
ListBox1.List(s, 6) = Sheets("Sheet2").Cells(sat, "G")
ListBox1.List(s, 7) = Sheets("Sheet2").Cells(sat, "H")
ListBox1.List(s, 8) = Sheets("Sheet2").Cells(sat, "I")
s = s + 1


End If: Next

End If

[SamT]

If ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value < ComboBox4.Value Then

For sat = 2 To Sheets("Sheet2").Cells(10000, "a").End(xlUp).Row
If UnknownValue >= (ComboBox3.Value - 150000) And s <= (ComboBox4.Value - 150000) Then
ListBox1.AddItem Sheets("Sheet2").Cells(sat, "A").Resize(1, 9)
UnknownValue = UnknownValue + 1
End If
Next
End If

[Pongwipat]

thank you but It is not working. What is the " UnknownValue " ?

anyone can suggest or help?

[Aussiebear]

Can you tell us where "it is not working"? In this case SamT has used UnknownValue as method to describe a variable

[Pongwipat]

I copy this code


If ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value < ComboBox4.Value Then

For sat = 2 To Sheets("Sheet2").Cells(10000, "a").End(xlUp).Row
If UnknownValue >= (ComboBox3.Value - 150000) And s <= (ComboBox4.Value - 150000) Then
ListBox1.AddItem Sheets("Sheet2").Cells(sat, "A").Resize(1, 9)
UnknownValue = UnknownValue + 1
End If
Next
End If

to userform1 code and I put 150002(combobox3) and 150006(combobox4) then listbox1 show nothing(it is not working).

Or, I have to change UnknownValue to .............(something else). Can SamT or anyone suggest?


I check two conditions (1)
s < (ComboBox4.Value - 150000) is working ( for example when I put 150006(combobox4) then the Id 150001,150002,150003,150004,150005,150006 showed in listbox1.

but (2)
s > (ComboBox3.Value - 150000) is not working.

How can I fix this problem?

[SamT]

UnkownValue is s

If s >= (ComboBox3.Value - 150000) And s <= (ComboBox4.Value - 150000) Then

[Pongwipat]

Thanks you very much for suggestion from SamT and Aussiebear.
I change condition from "If .....then" to "If not......then"

Dim a, b As Long
Dim sCrit As String
Dim tCrit, uCrit As Long
Dim vCrit, wCrit As String
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Set rngSource = Worksheets("Sheet2").Range("A2:I1000")

tCrit = ComboBox3.Value
With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "80;80;80;0;0;110;80;0;30"
.List = rngSource.Cells.Value
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 0)) >= tCrit Then
.RemoveItem a
End If
Next a
End With

uCrit = ComboBox4.Value
With Me.ListBox1
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 0)) <= uCrit Then
.RemoveItem a
End If
Next a
End With



It is now working.

And I will code to find dates between two dates ( start and end date) .

Anyone can suggest or give some example?

[Pongwipat]

I try to use this code by copying from previous code (between 2 id). I change " vCrit, wCrit " As Date but not working.
Can the date compare? Or, something wrong?

Anyone can suggest or help?



Private Sub CommandButton1_Click()
Dim a As Long
Dim sCrit As String
Dim tCrit, uCrit As Long
Dim vCrit, wCrit As Date
Dim rngSource As Range
Set rngSource = Worksheets("Sheet2").Range("A2:I1000")

'3. between two dates

If Not TextBox1.Value = Empty Then
vCrit = TextBox1.Value
With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "80;80;80;0;0;110;80;0;30"
.List = rngSource.Cells.Value
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 6)) >= vCrit Then
.RemoveItem a
End If
Next a
End With

If Not TextBox1 = Empty Then
wCrit = TextBox2.Value
With Me.ListBox1
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 6)) <= wCrit Then
.RemoveItem a
End If
Next a
End With
End If
End If

[SamT]

There is a Procedure in the attachment in this post (http://www.vbaexpress.com/forum/showthread.php?52981-Excel-to-return-looked-up-values-from-drop-down-list&p=326971&viewfull=1#post326971). look in the Userform code.

Ah! I still have that code. lbxLocation is a Single column ListBox. Column B is the equivalent of your Dates, but the List Items are in Column A.

Note that in Excel and VBA, a Range like ("A1:A1") is OK, but not for all situations, (.List = ListArray (a Variant) where Range.Count = 1.)


Private Sub Fill_Location()
Dim Address1 As String
Dim Address2 As String
Dim ListArray As Variant
Dim CodeCheck As Range

With Sheets("PostCodes").Range("B:B")
'PostCodes List starts below B1
Set CodeCheck = .Find(What:=tbxPostCode, After:=Range("B1"), LookAt:=xlWhole, SearchDirection:=xlNext)
If CodeCheck Is Nothing Then
MsgBox "Invalid Postal Code entered"
Exit Sub
End If

Address1 = .Find(What:=tbxPostCode, After:=Range("B1"), LookAt:=xlWhole, _
SearchDirection:=xlNext).Offset(0, -1).Address
Address2 = .Find(What:=tbxPostCode, After:=Range("B1"), LookAt:=xlWhole, _
SearchDirection:=xlPrevious).Offset(0, -1).Address
End With

If Sheets("PostCodes").Range(Address1 & ":" & Address2).Count = 1 Then
Me.lbxLocation.AddItem Sheets("PostCodes").Range(Address1)

Else
ListArray = Sheets("PostCodes").Range(Address1 & ":" & Address2)
With Me.lbxLocation
.Clear
.List = ListArray
End With
End If
End Sub

[snb]

Sub M_snb()
lbxLocation.List = Split(Split(Split(Join(Application.Transpose(sheets("Postcode").Columns(2).SpecialCells(2)), "|"), "bb5")(1), "bb12")(0), "|")
End sub

NB. "bb5" is the first value after which values have to be selected.
"bb12' is the last value before which values have to be selected

[Pongwipat]

Thank you for all advices and examples.
I can fix problem by this code : analyzing year --> month---> day. It is now working.



Private Sub CommandButton1_Click()
Dim a, LngIndex As Long
Dim ytCrit, mtCrit, dtCrit As Long
Dim yuCrit, muCrit, duCrit As Long
Dim sCrit As String
Dim tCrit, uCrit As Date
Dim tngSource As Range
Set tngSource = Worksheets("Sheet2").Range("A2:K1000")

' 2.between two days

'textbox1
tCrit = TextBox1.Value
ytCrit = Val(Year(tCrit))
mtCrit = Val(Month(tCrit))
dtCrit = Val(Day(tCrit))

With Me.ListBox1
.ColumnCount = 11
.ColumnWidths = "80;80;80;0;0;110;150;0;80;80;80;"
.List = tngSource.Cells.Value
' date format
For LngIndex = o To .ListCount - 1
.List(LngIndex, 6) = UCase(Format(CDate(.List(LngIndex, 6)), "dd/mm/yyyy"))
Next

For a = .ListCount - 1 To 0 Step -1
If Not Year(.List(a, 6)) = ytCrit Then
If Not Year(.List(a, 6)) > ytCrit Then
.RemoveItem a
End If
End If
Next a

For a = .ListCount - 1 To 0 Step -1
If Year(.List(a, 6)) = ytCrit And (Month(.List(a, 6)) > mtCrit Or Month(.List(a, 6)) < mtCrit) Then
If Not Month(.List(a, 6)) > mtCrit Then
.RemoveItem a
End If
End If
Next a

For a = .ListCount - 1 To 0 Step -1
If Year(.List(a, 6)) = ytCrit And Month(.List(a, 6)) = mtCrit Then
If Not Day(.List(a, 6)) >= dtCrit Then
.RemoveItem a
End If
End If
Next a

End With


'textbox2

uCrit = TextBox2.Value
yuCrit = Val(Year(uCrit))
muCrit = Val(Month(uCrit))
duCrit = Val(Day(uCrit))

With Me.ListBox1
For a = .ListCount - 1 To 0 Step -1
If Not Year(.List(a, 6)) = yuCrit Then
If Not Year(.List(a, 6)) < yuCrit Then
.RemoveItem a
End If
End If
Next a

For a = .ListCount - 1 To 0 Step -1
If Year(.List(a, 6)) = yuCrit And (Month(.List(a, 6)) > muCrit Or Month(.List(a, 6)) < muCrit) Then
If Not Month(.List(a, 6)) < muCrit Then
.RemoveItem a
End If
End If
Next a

For a = .ListCount - 1 To 0 Step -1
If Year(.List(a, 8)) = yuCrit And Month(.List(a, 9)) = muCrit Then
If Not Day(.List(a, 10)) <= duCrit Then
.RemoveItem a
End If
End If
Next a

End With

End Sub

[Pongwipat]

I have a bit problem when searching name(combobox1) with square branket "[]": For example "Peter[Jones]" ,which couldn't show in listbox when searching.

How to fix this problem.
Anyone can help or suggest


Dim a, LngIndex As Long
Dim ytCrit, mtCrit, dtCrit As Long
Dim yuCrit, muCrit, duCrit As Long
Dim sCrit As String
Dim tCrit, uCrit As Date
Dim vCrit, wCrit As Long
Dim tngSource As Range
Dim LastRow As Long
LastRow = Worksheets("Sheet2").Cells(Cells.Rows.Count, "K").End(xlUp).Row
On Error Resume Next
Set tngSource = Worksheets("Sheet2").Range("A2:K" & LastRow)

With Me.ListBox1
.ColumnCount = 11
.ColumnWidths = "80;80;80;0;0;110;150;0;80;80;80;"
.List = tngSource.Cells.Value
' date format
For LngIndex = o To .ListCount - 1
.List(LngIndex, 6) = UCase(Format(CDate(.List(LngIndex, 6)), "dd/mm/yyyy"))
Next LngIndex
End With

On Error Resume Next


'1.name
If ComboBox1.Value <> "" Then
sCrit = "*" & UCase(Me.ComboBox1) & "*"
With Me.ListBox1
For a = .ListCount - 1 To 0 Step -1
If Not UCase(.List(a, 1)) Like sCrit Then
.RemoveItem a
End If
Next a
End With
End If