Input box

[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

 

[Lazzzx]

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

 

[Rodrigo F Rodrigues]

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!!!

 

[Louis]

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

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

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

 

[Louis]

Dear Lazzzx

Thanks a lot, now it work

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

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