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/