Input box

  • Thread starter Thread starter inès
  • Start date Start date
I

inès

Hello

I would like to use an input box to find in my excel data base some
information.
I have been able to create the input box and the vba code to find a word or
the sentence but if i put both together it doesn't work anymore.
Could you please help me.

The code which i have create is as follow:

Sub Find()

Dim rangeCel As Range
Dim compt As Integer
Dim fin As String

fin = Application.InputBox(Prompt:="KUNDE", Type:=2)
ActiveSheet.Range("$A$15:$n$500 ").AutoFilter Field:=1, Criteria1:="=*" &
[H14].Value & "*"

End Sub

Thanks a lot

Inès
 
Hi,
The result from the inputbox is stored in the fin variable. Yous should
replace [H14].value with "fin" and it should work perfect.

ActiveSheet.Range("$A$15:$n$500 ").AutoFilter Field:=1, Criteria1:="=*" &
fin & "*"

if you have a changing number of rows in your database, you can make it more
dynamic by:
ActiveSheet.Range("$A$15).CurrentRegion.AutoFilter Field:=1, Criteria1:="=*"
& fin & "*"

this will only work, if you have no empty rows in your data. Furthermore
range("A14:N14) shoud be empty cells to avoid these to be included into the
currentregion.

br
Lazzzx
 
Hi, Inès

You can use the cod follow, to look for words and mark the same sentences
with other color:

Sub FindText()
Dim strText As String
Dim rngReturn As Range
Dim strFirst As String

strText = InputBox("Enter the texto to look for", "Find Text")
Set rngReturn = Range("a2:i3000").Find(strText, LookIn:=xlValues)

If Not rngReturn Is Nothing Then
strFirst = rngReturn.Address
Do
rngReturn.Interior.Color = vbYellow
Set rngReturn = Range("a2:i3000").FindNext(rngReturn)
Loop While Not rngReturn Is Nothing And CStr(rngReturn.Address) <>
strFirst
End If
End Sub

Sorry my english, because i don't speak very well!!!!

bye!!!
 
Hello Rodrigo F Rodrigues

Thanks for the macro. But i still have a small problem, i should sort out
all the cells with the content of the input box. It i write "Hello" in the
input box i should see all the cells with the word "Hello". But of course if
the cells text is "Louis say Hello" it should appear also.

Thanks a lot

Louis

Rodrigo F Rodrigues said:
Hi, Inès

You can use the cod follow, to look for words and mark the same sentences
with other color:

Sub FindText()
Dim strText As String
Dim rngReturn As Range
Dim strFirst As String

strText = InputBox("Enter the texto to look for", "Find Text")
Set rngReturn = Range("a2:i3000").Find(strText, LookIn:=xlValues)

If Not rngReturn Is Nothing Then
strFirst = rngReturn.Address
Do
rngReturn.Interior.Color = vbYellow
Set rngReturn = Range("a2:i3000").FindNext(rngReturn)
Loop While Not rngReturn Is Nothing And CStr(rngReturn.Address) <>
strFirst
End If
End Sub

Sorry my english, because i don't speak very well!!!!

bye!!!


inès said:
Hello

I would like to use an input box to find in my excel data base some
information.
I have been able to create the input box and the vba code to find a word or
the sentence but if i put both together it doesn't work anymore.
Could you please help me.

The code which i have create is as follow:

Sub Find()

Dim rangeCel As Range
Dim compt As Integer
Dim fin As String

fin = Application.InputBox(Prompt:="KUNDE", Type:=2)
ActiveSheet.Range("$A$15:$n$500 ").AutoFilter Field:=1, Criteria1:="=*" &
[H14].Value & "*"

End Sub

Thanks a lot

Inès
Click to expand...
Click to expand...
 
Dear Lazzzx

Thanks a lot, now it work

Lazzzx said:
Hi,
The result from the inputbox is stored in the fin variable. Yous should
replace [H14].value with "fin" and it should work perfect.

ActiveSheet.Range("$A$15:$n$500 ").AutoFilter Field:=1, Criteria1:="=*" &
fin & "*"

if you have a changing number of rows in your database, you can make it more
dynamic by:
ActiveSheet.Range("$A$15).CurrentRegion.AutoFilter Field:=1, Criteria1:="=*"
& fin & "*"

this will only work, if you have no empty rows in your data. Furthermore
range("A14:N14) shoud be empty cells to avoid these to be included into the
currentregion.

br
Lazzzx

inès said:
Hello

I would like to use an input box to find in my excel data base some
information.
I have been able to create the input box and the vba code to find a word
or
the sentence but if i put both together it doesn't work anymore.
Could you please help me.

The code which i have create is as follow:

Sub Find()

Dim rangeCel As Range
Dim compt As Integer
Dim fin As String

fin = Application.InputBox(Prompt:="KUNDE", Type:=2)
ActiveSheet.Range("$A$15:$n$500 ").AutoFilter Field:=1, Criteria1:="=*" &
[H14].Value & "*"

End Sub

Thanks a lot

Inès
Click to expand...
Click to expand...
 
Back
Top