PDA

View Full Version : Finding data in a range



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!

Aussiebear
07-06-2013, 02:51 AM
This Range
Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp)) defines the search to Column A, so tell me what would you think to do the set the column to Column E?

sharky12345
07-06-2013, 02:53 AM
Rather than searching column A I want to search column E for the value, but it still doesn't find it. I've tried changing the code to "E1" and "E" but still no luck.

Aussiebear
07-06-2013, 03:32 AM
What exactly is the value that you are searching for, and does it truely exist in column E? Remember that the value needs to be exact, no spaces ( trailing or otherwise.)

sharky12345
07-06-2013, 03:33 AM
The value is 'MDT70598'.

I have tried searching for it exactly but it is not found, despite it appearing hundreds of times.

Aussiebear
07-06-2013, 04:49 AM
You seriously need to consider attaching a sample workbook. I ilke many others cannot see over your shoulder to view to workbook you are currently working on. Click on "Go Advanced' scroll down to 'Manage Attachments" and follow the prompts from there

sharky12345
07-06-2013, 06:26 AM
I won't be attaching a sample workbook and have sent you a private message.

Disregard this, I will work around the problem another way.