5 Replies - 836 Views - Last Post: 01 April 2013 - 12:15 PM Rate Topic: -----

#1 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Search an excel file for string entered in textbox

Posted 29 March 2013 - 01:35 PM

Dim book1 As Range
Dim x, y, lookinrow As String
Dim nrow As Integer

x = TextBox1.Text
nrow = 1


         Range("A1").Select

Do While ActiveCell.Value <> x
         nrow = nrow + 1
         ActiveCell.Offset(1, 0).Select

Loop
  
         Range("A" & nrow).Select
         If ActiveCell.Value = x Then
         Range("B" & nrow).Select
         y = ActiveCell.Value
         ListBox1.ColumnCount = 1
         ListBox1.AddItem Range("B" & nrow).Value

End Sub

Sorry if I have broken the rules. What I am trying to do is step trough all the cells in column A and find all the cell that contain the same thing as entered in textbox1. The above codes works in as far that it only finds the first match. Any ideas

This post has been edited by Atli: 29 March 2013 - 02:36 PM
Reason for edit:: Use [code] tags when posting code.


Is This A Good Question/Topic? 0
  • +

Replies To: Search an excel file for string entered in textbox

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3519
  • View blog
  • Posts: 12,018
  • Joined: 12-December 12

Re: Search an excel file for string entered in textbox

Posted 29 March 2013 - 02:51 PM

I assume you keyed this in Excel VBA?

Yes, it will only check the first item as that is what you have told it to do..!

You could study the following code:

Dim aCell As Range

ListBox1.ColumnCount = 1

For Each aCell In Range("A:A")
    'don't want to check EVERY cell in the column..
    If IsEmpty(aCell.Value) Or aCell.Value = "" Then
        Exit For
    ElseIf aCell.Row > 1000 Then
        Exit For
    End If
    If aCell.Value = x Then
        ListBox1.AddItem acell.Offset(0, 1).Value
    End If
Next aCell

Was This Post Helpful? 0
  • +
  • -

#3 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: Search an excel file for string entered in textbox

Posted 31 March 2013 - 04:29 AM

Thanks andrewsw
I tried that code you gave me and it works perfect. Does not write any thing to the listbox I'll see if i did some thing stupid. Just in relation to the code I posted first I got it working by putting the loop command at the bottom, but this gives me a problem that it does not stop when it meets at blank cell. I tried putting in the code line[If activecell.value="" then
exit if.] Any ides where I went wrong
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Search an excel file for string entered in textbox

Posted 31 March 2013 - 08:35 PM

not exit if, exit do.
Was This Post Helpful? 0
  • +
  • -

#5 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: Search an excel file for string entered in textbox

Posted 01 April 2013 - 11:55 AM

Hi Bob I tried that line and I keep getting a complie error. Expected line number or end of statement. Does it matter that I am writing this through VB excel.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3519
  • View blog
  • Posts: 12,018
  • Joined: 12-December 12

Re: Search an excel file for string entered in textbox

Posted 01 April 2013 - 12:15 PM

View Postnocando, on 01 April 2013 - 06:55 PM, said:

Hi Bob I tried that line and I keep getting a complie error. Expected line number or end of statement. Does it matter that I am writing this through VB excel.

It doesn't matter what language you are using: a syntax error is a syntax error.

Post the code that the error message refers to, indicating which line in your code it refers to.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1