Hi. I am using this piece of code - kindly supplied by DaveP - to search
for cells containg certain names:

:::::START:::::::::::::::::::::::::::::::
Option Explicit
Sub SearchForName()

Dim FoundCell As Range
Dim CellWithName As Range


With Worksheets("sheet1")
Set CellWithName = .Range("A1")
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

:::::END::::::::::::::::::::::::::::::

Can i use a textbox in my worksheet as the datainput instead? (And
how). If i fx. make a TextBox and call it sString, how do I get the
value from whatevers in it, into the script above?

Cheers...


---
Message posted from http://www.ExcelForum.com/

I assume you mean a worksheet textbox off of the control toolbox.

Replace this

With Worksheets("sheet1")
Set CellWithName = .Range("A1")
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If


with this

With Worksheets("sheet1")
Set CellWithName = .Textbox.Text
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kandinsky " wrote in message
...
Hi. I am using this piece of code - kindly supplied by DaveP - to search
for cells containg certain names:

:::::START:::::::::::::::::::::::::::::::
Option Explicit
Sub SearchForName()

Dim FoundCell As Range
Dim CellWithName As Range


With Worksheets("sheet1")
Set CellWithName = .Range("A1")
If Trim(CellWithName.Value) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

:::::END::::::::::::::::::::::::::::::

Can i use a textbox in my worksheet as the datainput instead? (And
how). If i fx. make a TextBox and call it sString, how do I get the
value from whatevers in it, into the script above?

Cheers...


---
Message posted from http://www.ExcelForum.com/

I do mean that yes. But when I try the .TextBox.Text as you suggest
then get the following:

"Runtime error

Object required"

And it's highlighting the line:

Set CellWithName = .TextBox1.Text


Obviously i'm not a programmer, he he

--
Message posted from http://www.ExcelForum.com

Sorry,

I left some of the code that assumed a range in there. Try this lot instead

Sub SearchForName()
Dim FoundCell As Range
Dim CellWithName as String

With Worksheets("sheet1")
CellWithName = .TextBox1.Text
If Trim(CellWithName) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kandinsky " wrote in message
...
I do mean that yes. But when I try the .TextBox.Text as you suggest I
then get the following:

"Runtime error

Object required"

And it's highlighting the line:

Set CellWithName = .TextBox1.Text


Obviously i'm not a programmer, he he)


---
Message posted from http://www.ExcelForum.com/

Absolutely b-e-a utiful!

Thanx a lot mate. Just make the whole feel of the worksheet that mor
intuitive, than having people typing search criterias into cells...

Now, your next assignment, should you choose to accept it...

Can you make it do the search again, from the result position (Fin
Next)?

(I'll try and figure it out myself, but that will take a couple o
years, he he. I used to do a lot of Jscripting and a little Java, bu
this vba is very confusing to me...)


--
Message posted from http://www.ExcelForum.com

How about this

Sub SearchForName()
Dim FoundCell As Range
Dim FoundFirst As Range
Dim CellWithName As String
Dim sFirst As String

With Worksheets("sheet1")
CellWithName = .TextBox1.Text
If Trim(CellWithName) = "" Then
MsgBox "Please type something"
Exit Sub
End If

With .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not FoundCell Is Nothing Then
Set FoundFirst = FoundCell
Set FoundCell = .FindNext(FoundCell)
If FoundCell Is Nothing Then FoundCell = FoundFirst
End If

End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kandinsky " wrote in message
...
Absolutely b-e-a utiful!

Thanx a lot mate. Just make the whole feel of the worksheet that more
intuitive, than having people typing search criterias into cells...

Now, your next assignment, should you choose to accept it...

Can you make it do the search again, from the result position (Find
Next)?

(I'll try and figure it out myself, but that will take a couple of
years, he he. I used to do a lot of Jscripting and a little Java, but
this vba is very confusing to me...)



---
Message posted from http://www.ExcelForum.com/

It doesn't seem to change anything for me. It still searches from th
top when i click it again..

--
Message posted from http://www.ExcelForum.com

Do you mean run the routine again? That is not what I thought you meant.

Please describe the sequence of events that you want to follow, and what
will happen.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kandinsky " wrote in message
...
It doesn't seem to change anything for me. It still searches from the
top when i click it again...


---
Message posted from http://www.ExcelForum.com/

Oh yeah, I can see that it might be a good idea if i told you what
wanted before I asked you to help with it, he he

Iv'e got a customerlist with several thousand names, and I have m
sales rep's names with them etc.

I am trying to make it easy to type in part of a customer name, an
then just click the search button until the right one comes up.

If I fx know that the customers name is something with the word
"Xtreme" in it, but don't quite remember the whole name, I'll just typ
"xtreme" and then click search until I recognize the right record
(Like a "find next" thing)

Right now i can click several times, and it will only go to the firs
recognized record...


Did that make sence?

Regards,
Jørgen Lindegaar

--
Message posted from http://www.ExcelForum.com

Hi Jørgen,

Why should you be any different to other users?

Version 3 and counting!

Sub SearchForName()
Dim FoundCell As Range
Dim FoundFirst As Range
Dim StartAt As Range
Dim CellWithName As String
Dim sFirst As String

With Worksheets("Sheet1")
CellWithName = .TextBox1.Text
If Trim(CellWithName) = "" Then
MsgBox "Please type something"
Exit Sub
End If

If LCase(ActiveCell.Value) Like LCase(CellWithName & "*") Then
Set StartAt = ActiveCell.Offset(1, 0)
Else
Set StartAt = Range("A2")
End If

With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = .Cells.Find(What:=CellWithName, _
After:=StartAt, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not there"
Else
Application.Goto reference:=FoundCell, Scroll:=True
End If
End With

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kandinsky " wrote in message
...
Oh yeah, I can see that it might be a good idea if i told you what I
wanted before I asked you to help with it, he he

Iv'e got a customerlist with several thousand names, and I have my
sales rep's names with them etc.

I am trying to make it easy to type in part of a customer name, and
then just click the search button until the right one comes up.

If I fx know that the customers name is something with the word
"Xtreme" in it, but don't quite remember the whole name, I'll just type
"xtreme" and then click search until I recognize the right record.
(Like a "find next" thing)

Right now i can click several times, and it will only go to the first
recognized record...


Did that make sence?

Regards,
Jørgen Lindegaard


---
Message posted from http://www.ExcelForum.com/

That seems to do the trick. Absolutely great.

I'm even beginning to se a pattern in the code. It's almost as if it'
starting to make sence to me, so not only did you make my worklif
easier, you also managed to insert a little knowledge in my poor littl
brain....

But don't worry, I'll be back online sooner or later, with yet anothe
I-have-tried-for-five-minutes-now-and-now-I-give-up-so-please-do-it-for-m
problem..

Ya'll behave now, ya' hear...

Jørge

--
Message posted from http://www.ExcelForum.com

"kandinsky " wrote in message
...
That seems to do the trick. Absolutely great.


Great,. you've been a pain in the neck, but it's always nice to get rid of
pain (only kidding).

I'm even beginning to se a pattern in the code. It's almost as if it's
starting to make sence to me, so not only did you make my worklife
easier, you also managed to insert a little knowledge in my poor little
brain....


Double-whammy!

But don't worry, I'll be back online sooner or later, with yet another
I-have-tried-for-five-minutes-now-and-now-I-give-up-so-please-do-it-for-me
problem..


Well we all look forward to that with fear and trepidation<vbg.

Ya'll behave now, ya' hear...


Does this indicate that you are from Texas?