sharky12345
07-06-2013, 02:28 AM
I'm using the following code to search for the value of a Textbox in a range and if that value is found then the range is displayed in a Listbox;
Dim Rng As Range, Dn As Range
Dim Fd As Boolean
ListBox1.Height = 100
With Sheets("6 Month Data")
Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
End With
With ListBox1
.Clear
.ColumnCount = 4
.ColumnWidths = "30,75,75,30"
End With
For Each Dn In Rng
If Dn.Value = Val(TextBox1) Then
With ListBox2
.AddItem Dn
.List(.ListCount - 1, 1) = Dn.Offset(, 1).Text
.List(.ListCount - 1, 2) = Dn.Offset(, 2).Text
.List(.ListCount - 1, 3) = Dn.Offset(, 3).Text
.List(.ListCount - 1, 4) = Dn.Offset(, 4).Text
Fd = True
End With
End If
Next Dn
If Fd = False Then MsgBox "Number " & TextBox1.Value & " is not found in the database for this period", vbInformation, "number not found"
I want to amend this slightly but I can't figure it out - I want to search for the value in column E, the data for which is a combination of text and numbers, (not sure if this makes a difference).
I still want the Listbox to display the range from A:E but using column E as the range to search initially.
I've tried amending the range line by putting in E etc but I keep getting the message that the value is not found, even though it's there.
Have I missed something really obvious?
Posted on Mr Excel too but I can't copy the hyperlink because their site seems to have gone down!
Dim Rng As Range, Dn As Range
Dim Fd As Boolean
ListBox1.Height = 100
With Sheets("6 Month Data")
Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
End With
With ListBox1
.Clear
.ColumnCount = 4
.ColumnWidths = "30,75,75,30"
End With
For Each Dn In Rng
If Dn.Value = Val(TextBox1) Then
With ListBox2
.AddItem Dn
.List(.ListCount - 1, 1) = Dn.Offset(, 1).Text
.List(.ListCount - 1, 2) = Dn.Offset(, 2).Text
.List(.ListCount - 1, 3) = Dn.Offset(, 3).Text
.List(.ListCount - 1, 4) = Dn.Offset(, 4).Text
Fd = True
End With
End If
Next Dn
If Fd = False Then MsgBox "Number " & TextBox1.Value & " is not found in the database for this period", vbInformation, "number not found"
I want to amend this slightly but I can't figure it out - I want to search for the value in column E, the data for which is a combination of text and numbers, (not sure if this makes a difference).
I still want the Listbox to display the range from A:E but using column E as the range to search initially.
I've tried amending the range line by putting in E etc but I keep getting the message that the value is not found, even though it's there.
Have I missed something really obvious?
Posted on Mr Excel too but I can't copy the hyperlink because their site seems to have gone down!