Bright01
asked on
Excel Macro Search Box
EE Pros,
I'm a advanced novice Excel user trying to run a substantial sales operation. I have a worksheet that has approximately 5000 Customers so, 5000+ rows and approx. 30 columns of data. Every time I have to look up a Customer, I am using the Filter to uncheck "All" and look down the list for the particular record. This is nerve racking as it can take 30 seconds or more to look up a particular record in column A.
What I'm looking for is a small piece of VBA Code that can assign a "key" such as "L" (for look up) and up pops a box that seeks the character string typed into the box focused on either a particular column, say "A" or has a option to "search all" which would be the entire sheet.
Can I get some help with this? I think it's rather simple for someone who knows VBA. I have the skills to integrate it into my worksheet...but simply need help with the lines of code, search algorithum and probably the form that needs to come up in order to perform the search (with link to the assigned key (such as Ctrl+L -- for Look Up).
Much thanks in advance,
B.
I'm a advanced novice Excel user trying to run a substantial sales operation. I have a worksheet that has approximately 5000 Customers so, 5000+ rows and approx. 30 columns of data. Every time I have to look up a Customer, I am using the Filter to uncheck "All" and look down the list for the particular record. This is nerve racking as it can take 30 seconds or more to look up a particular record in column A.
What I'm looking for is a small piece of VBA Code that can assign a "key" such as "L" (for look up) and up pops a box that seeks the character string typed into the box focused on either a particular column, say "A" or has a option to "search all" which would be the entire sheet.
Can I get some help with this? I think it's rather simple for someone who knows VBA. I have the skills to integrate it into my worksheet...but simply need help with the lines of code, search algorithum and probably the form that needs to come up in order to perform the search (with link to the assigned key (such as Ctrl+L -- for Look Up).
Much thanks in advance,
B.
There is all function key on Excel workbook,
http://office.microsoft.com/en-001/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
Hope it will assist you to solve your issue or give you some hint
Duncan
http://office.microsoft.com/en-001/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
Hope it will assist you to solve your issue or give you some hint
Duncan
ASKER
Rgonzo and Duncan,
Very helpful! Here's my question; can you guys show me how I can construct a simple "Button" that drives a macro that reflects using the "Function Keys" in Excel? Since I have users using the Worksheet, I'd like to make this very simple. If I can have a single Button that when pressed, it drives the specific Function, such as Alt. + F, then I don't have to train them on the shortcut.
Much thanks,
B.
Very helpful! Here's my question; can you guys show me how I can construct a simple "Button" that drives a macro that reflects using the "Function Keys" in Excel? Since I have users using the Worksheet, I'd like to make this very simple. If I can have a single Button that when pressed, it drives the specific Function, such as Alt. + F, then I don't have to train them on the shortcut.
Much thanks,
B.
There is three wayes to do this
1-on VBA editor, try to write your example function macro called test()
On Excel 2007, insert shape such as circule, rect,...etc.
And right click the sharp and then select test() macro from the tab of "Assign Macro"
2-You can use "Macro Recoding" tab to recode all function(atl+F) you click on the Excel sheet and
then look into the VBA editor, the record macro will save all your click, editing, draw, selecting action..etc on your Excel sheet into the VBA macro
3-take a look at onkey on VBA at http://msdn.microsoft.com/en-us/library/office/ff197461(v=office.15).aspx
Duncan
1-on VBA editor, try to write your example function macro called test()
On Excel 2007, insert shape such as circule, rect,...etc.
And right click the sharp and then select test() macro from the tab of "Assign Macro"
2-You can use "Macro Recoding" tab to recode all function(atl+F) you click on the Excel sheet and
then look into the VBA editor, the record macro will save all your click, editing, draw, selecting action..etc on your Excel sheet into the VBA macro
3-take a look at onkey on VBA at http://msdn.microsoft.com/en-us/library/office/ff197461(v=office.15).aspx
Duncan
ASKER
Duncan,
I know how to link/assign the macro. I tried approach 2, by recording a macro, selecting CTRL+F, up came the find box, I stopped the Macro record. And got nothing. Can you give me some coaching here or show me an example?
Thank you,
B.
I know how to link/assign the macro. I tried approach 2, by recording a macro, selecting CTRL+F, up came the find box, I stopped the Macro record. And got nothing. Can you give me some coaching here or show me an example?
Thank you,
B.
ASKER
Watched the tutorial.... but doesn't address a Control + F recording. Here's what I'm doing:
1.) Selected Record Macro
2.) Selected Relative Referernce
3.) Control + F (to initiate the "find function")
4.) Stopped the Macro
5.) Viewed the Macro
I get Nothing in the Macro.
B
1.) Selected Record Macro
2.) Selected Relative Referernce
3.) Control + F (to initiate the "find function")
4.) Stopped the Macro
5.) Viewed the Macro
I get Nothing in the Macro.
B
try a small and test example to start after viewing the video, work around it, you will get it
Duncan
Duncan
ASKER
I did! It didn't work. It doesn't get any smaller then two keystrokes.
Watched the tutorial.... but doesn't address a Control + F recording. Here's what I'm doing:
1.) Selected Record Macro
2.) Selected Relative Referernce
3.) Control + F (to initiate the "find function")
4.) Stopped the Macro
5.) Viewed the Macro
I get Nothing in the Macro.
Watched the tutorial.... but doesn't address a Control + F recording. Here's what I'm doing:
1.) Selected Record Macro
2.) Selected Relative Referernce
3.) Control + F (to initiate the "find function")
4.) Stopped the Macro
5.) Viewed the Macro
I get Nothing in the Macro.
ASKER
Duncan,
Here's what I tried; no progress here.
Sub Macro1()
Application.OnKey "^{F}"
End Sub
Here's what I tried; no progress here.
Sub Macro1()
Application.OnKey "^{F}"
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Duncan,
Works great! I would have never been able to figure that out.... really appreciate the help.
One question; is there a way to keep it from having to be an exact match? It works great but I have to use the wildcard to find a partial char. string.
Thanks,
B.
Works great! I would have never been able to figure that out.... really appreciate the help.
One question; is there a way to keep it from having to be an exact match? It works great but I have to use the wildcard to find a partial char. string.
Thanks,
B.
Using widcard with find is hard, I try to use Instr with find
Duncan
Duncan
For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, FindString) <> 0 Then
GoTo Final
End If
Next Cell
functionkey.xls
ASKER
Interesting on this pass.... if I try to find a record "above" where the cursor is in the worksheet, I get an error but it goes to the spot.
B.
B.
ASKER
Also, where you have "MatchCase:=False)", should that mean that it doesn't have to match cases? Right now I have to put in exactly the Case for it to find a Name. Example; FedEx vs. fedex.
B.
B.
Could you summarize what code you have and what last issue you have on new thread that
will be easier and faster for other experts to look into it ?
Duncan
will be easier and faster for other experts to look into it ?
Duncan
ASKER
Yes. Fair enough. I've already put it in my production system and am making the changes as I get your recommendations. Let me send you the code I have now in the Workbook.
B.
B.
ASKER
Module 1
Sub FunctionkeyFind()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search Value")
If Trim(FindString) <> "" Then
With Sheets("PMQ_Sales_Pipeline ").Range(" B:B")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count ), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
For Each Cell In Range("B1", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindString) <> 0 Then
GoTo final
End If
Next Cell
MsgBox "Nothing found"
Exit Sub
End If
For Each Cell In Range("B1", Range("B" & Rows.Count).End(x1up))
If InStr(Cell, FindString) <> 0 Then
GoTo final
End If
Next Cell
End With
End If
final:
Cell.Select
End Sub
This Workbook
Private Sub Workbook_Activate()
Application.OnKey "+^{F}", "functionkey"
End Sub
Sub FunctionkeyFind()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search Value")
If Trim(FindString) <> "" Then
With Sheets("PMQ_Sales_Pipeline
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
For Each Cell In Range("B1", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindString) <> 0 Then
GoTo final
End If
Next Cell
MsgBox "Nothing found"
Exit Sub
End If
For Each Cell In Range("B1", Range("B" & Rows.Count).End(x1up))
If InStr(Cell, FindString) <> 0 Then
GoTo final
End If
Next Cell
End With
End If
final:
Cell.Select
End Sub
This Workbook
Private Sub Workbook_Activate()
Application.OnKey "+^{F}", "functionkey"
End Sub
ASKER
Duncan,
How come we didn't just write a macro that brings up the Find Function as we originally had thought to do? I couldn't get the macro to record the simple CTRL+F keystroke......
B.
How come we didn't just write a macro that brings up the Find Function as we originally had thought to do? I couldn't get the macro to record the simple CTRL+F keystroke......
B.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Bright01's comment #a40206172
for the following reason:
Duncan,
Thank you very much for the work here. I'm using it as a base and have learned new things thanks to you. I left the other question open because Glenn also weighed in and I'm learning how to do the same thing a different way.
Again, much thanks and wishing you "my best"!
B.
Accepted answer: 0 points for Bright01's comment #a40206172
for the following reason:
Duncan,
Thank you very much for the work here. I'm using it as a base and have learned new things thanks to you. I left the other question open because Glenn also weighed in and I'm learning how to do the same thing a different way.
Again, much thanks and wishing you "my best"!
B.
ASKER
Duncan,
Again, thanks for the help!
B.
Again, thanks for the help!
B.
Why not use Ctrl-F
if only select Col A first, it will only lookinto the column A
Regards