Full Version: Hold Variable In Input Box - Excel Macro Vba Queston
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
bakersburg9
I have a macro that selects a range in column a and b, and searches for a string of characters - in the example below, it's searching for sales how do I alter this code so I don't have to change the coding, and also is there a way to handle if nothing is found ?

Application.Goto Reference:="R1C1:R55C2"
Selection.Find(What:="sales", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ipisors
/>
dim strSearch as String
strSearch =InputBox("Enter the search term")

CODE
Application.Goto Reference:="R1C1:R55C2"
Selection.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


I believe this will work.
ipisors
also bob recently had a post with a suggestion here, in regard to error handling on Find, may want to check it out it may help
bakersburg9
Isaac,
that worked GREAT ! Thanks cool.gif

I checked out that link as well

thanks again !!!!!!

Steve
bakersburg9
hmm - I tried running the query from a button on the quick access toolbar, and it's not working !



if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd default/confused.gif" style="vertical-align:middle" emoid=":confused:" border="0" alt="confused.gif" />

if I run it from a code window, it works fine - very odd <
norie
Steve

If you use Selection in code called from a command button, there's a good chance that selection is actually the command button.
CODE
dim strSearch as String
strSearch =InputBox("Enter the search term")


Set rngFnd = Range("A1:B55").Find(What:=strSearch, After:=Range("A1:B55"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Not rngFnd Is Nothing Then

Applcation.Goto rngFnd
' any other code
End If
ipisors
Thanks Norie. I have benefitted greatly from your advice about the use of Select and Selection, but sometimes I get lazy about mentioning it each time.
bakersburg9
not sure I understand.

CODE
dim strSearch as String
strSearch =InputBox("Enter the search term")

Set rngFnd = Range("A1:B55").Find(What:=strSearch, After:=Range("A1:B55"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Not rngFnd Is Nothing Then

Applcation.Goto rngFnd
' any other code
End If


didn't work - you left off the .Activate but that wasn't the issue - it didn't run either way - and I didn't use the button
ipisors
What happens if you change
After:=Range("A1:B55")

to

After:=Range("A1")
norie
My mistake, it should be After:=Range("A1").

What I mean is that the command button is selected, so when you have Selection.Find, Selection is the command button.

Pretty sure you can't search a command button, or maybe you can.dazed.gif

The reason I left Activate off was because if the find doesn't work there's nothing to activate, so the code will fail.

The following If checks if the Find has worked and if it has move to the found range/cell using Application.Goto.
bakersburg9
Norie,
Nope - didn't work

CODE
Dim strSearch As String
strSearch = InputBox("Enter the search term")


Set rngFnd = Range("A1:B55").Find(What:=strSearch, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


If Not rngFnd Is Nothing Then

Applcation.Goto rngFnd
' any other code

End If
bakersburg9
I'm not following this.
norie
Why did you add Activate back in?

There's another mistake in the code though, it should be Application not Applcation.

PS Don't you have Intellisense here?<
bakersburg9
I took activate back out - and I fixed the typo - I should've used intellisense shocked.gif

Steve
norie
Steve

No problem.

About the Selection thing, in Excel Selection could be anything, within reason, including a button.

So when you use Selection in the code it could be referring to the button, not the range you want to search.

I don't know how you can empirically test it but try playing about with TypeName(Selection).

Make any sense now.<
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.