Changing Sort Options

[danesrood]

Dear All

I have an alphabetical list in Column A that contains in excess of 2000 records as I want to jump to the row where say the surnames beginning with P begin to insert a new record.

Column B contains surnames with this formula =IF(LEFT(B3,1)>LEFT(B2,1),LEFT(B3,1),"") in column A which ends up showing the first letter of the surname providing it is different from the cell above.

The result is something like this




Ambrose



Anderson


B
Bach



Brown

The only problem is that if I highlight the whole column and search for say the letter P Excel can’t find anything.

I think that I need to change the search options to:
Search by columns and Look in Values.

When I change these manually it works fine and I shoot down a few hundred rows.

Is there a piece of code that could change these options when the sheet is active and then reset it when it is not or the workbook is closed.

As ever any help is most appreciated.

[Bob Phillips]

My simple test works fine. Can you post a workbook with the data and the code?

[danesrood]

xld

It's not possible to post a workbook from the office but if you want an example I can knock one up at home and send it in a couple of hours.

There is no code in this other than the formula in column A which I have shown, everything else is data that is input manually.

[Bob Phillips]

That will be good. Just make sure your example exhibits that behaviour.

[danesrood]

xld

Sorry for delay seems to have been a lot of server problems.

Well here's a small version which should be adequate for you.

Sorry about earlier but like a lot of offices there are restrictions on what we can and can't do.

Although I've attached a 2007 file at work I'm using xls files.

Many thanks

[danesrood]

xld

Not wishing to be rude but as you have not responded I wonder if you are able to see the file?

My apologies if there is something else delaying your response.

Many thanks

[mancubus]

hi.

i hope i don't miss anyting


Sub find_box_param()

Application.Dialogs(xlDialogFormulaFind).Show "P", 2, 1, 1

'Application.Dialogs(xlDialogFormulaFind).Show _
"P", _
2, _
1, _
1, _
, _
False, _
False

'Arguments:
'Text : String in "Find What:" box
'in_num : 1- Formulas / 2- Values / 3- Comments
'at_num : Match entire cell contents box is 1- Checked / 2- Unchecked
'by_num : 1- By Rows / 2- By Columns
'dir_num : 1- (or omitted) Forward / 2- Backward
'match_case : optional True- case sensitive / False- case insensitive
'match_byte : optional True- double-byte characters match only double-byte characters
' / False- double-byte characters match their single-byte equivalents

End Sub

[Bob Phillips]

I wasn't on yesterday, I had jobs to do.

This works for me



Sub Findit()
Dim rownum As Long

With ActiveSheet

On Error Resume Next
rownum = Application.Match("E", .Columns(1), 0)
On Error GoTo 0
If rownum > 0 Then .Cells(rownum, "A").Select
End With
End Sub

[danesrood]

xld

I hope that I haven't offended you. I just wasn't sure if my attatchment could be seen.

[danesrood]

mancubus

Not sure where this code should go but it does sort of work by calling the macro but after using it the Ctrl F option doesn't - is that to be expected?

What I was hopingf for was something that could go either on the sheet or workbook that would set the Find options to columns and actual text and use the Ctrl F option without having to actually call a macro each time.

[Bob Phillips]

xld

I hope that I haven't offended you. I just wasn't sure if my attatchment could be seen.

Absolutely not mate, just explaining why I didn't respond earlier. Being polite :)

[mancubus]

mancubus

What I was hoping for was something that could go either on the sheet or workbook that would set the Find options to columns and actual text and use the Ctrl F option without having to actually call a macro each time.


danesrod,

i dont know a way that programmatically sets Find Dialog Box parameters.
and i would go with xld's suggestion. here and :
http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2007-09/msg03849.html

:rofl: :clap:


if you wish, you may try following:
ps: SendKeys method is not reliable.

workbook specific. goes to ThisWorkbook code module

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub Workbook_Open()

Application.Dialogs(xlDialogFormulaFind).Show "", 1, 1, 1 'look in formulas
Sleep 2000 'wait 2 secs
SendKeys "{ESCAPE}" ''send ESC key to close the dialog

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.Dialogs(xlDialogFormulaFind).Show "", 2, 1, 1 'look in values
Sleep 2000 'wait 2 secs
SendKeys "{ESCAPE}" 'send ESC key to close the dialog

End Sub




worksheet specific. goes to related sheet's code module

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub Worksheet_Activate()

Application.Dialogs(xlDialogFormulaFind).Show "", 1, 1, 1 'look in formulas
Sleep 2000 'wait 2 secs
SendKeys "{ESCAPE}" ''send ESC key to close the dialog

End Sub

Private Sub Worksheet_Deactivate()

Application.Dialogs(xlDialogFormulaFind).Show "", 2, 1, 1 'look in values
Sleep 2000 'wait 2 secs
SendKeys "{ESCAPE}" 'send ESC key to close the dialog

End Sub

[mikerickson]

You could select column A and then use Excel's built in search for P* (match whole word) which will bring you to the first P in column A.

[danesrood]

Mike, mancubus and xld

Thank you all for your interest.

I think that I may not have made it clear the letter P was just an example. I have many records and I just need to get down to the block of records that begin with say a P or an S or whatever letter rather than press the page down key many times.

So in support of this I have the formula =IF(LEFT(B3,1)>LEFT(B2,1),LEFT(B3,1),"") in column A which brings in the first letter of the surname in column B providing the resulting letter is not the same as the row above. This then creates a sort of index list of first letters in column A.

I must say that I had hoped that there could be a simple bit of code that I could place on the worksheet in question or in the workbook that would just change the searching method to columns and values rather than rows & formulas only. And then reset it back to the normal method upon leaving the workbook.

[mancubus]

danesrood,

a simple find macro may help...

you can assign a shortcut
(from View Tab, Macros, View Macros, Select 'macro name', Options, then enter an available letter)



Sub FindFunc()

On Error GoTo notfound
Columns("A").Find( _
What:=InputBox("Find What:=", "F I N D"), _
After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False _
).Activate
notfound:
MsgBox "Search item not found in active worksheet!", vbOKOnly + vbExclamation, "NOT FOUND"

End Sub

[danesrood]

Mancubus

Thank you this exactly is what I had hoped for.

The only slight problem is that everytime I run this it comes with the error message even though it has found what I have wanted.

[mancubus]

you're wellcome.


Sub FindFunc()

On Error GoTo notfound

Columns("A").Find( _
What:=InputBox("Find What:=", "F I N D"), _
After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False _
).Activate

Exit Sub
On Error GoTo 0

notfound:
MsgBox "Search item not found in active worksheet!", vbOKOnly + vbExclamation, "NOT FOUND"

End Sub



you may change the parameters where necessary:
LookIn:=xlFormulas
LookAt:=xlWhole
SearchOrder:=xlByColumns
SearchDirection:=xlPrevious
MatchCase:=True

[danesrood]

Mancubus

Thank you it works a treat.

Mind you I have a question about a minor quirk that is of no real consequence in fact it's probably to my benefit.

In A1 I have the text Index.

Wherever I am if I search for any of the letters i n d e the cursor moves to the appropriate position ignoring A1 completely. But if I search for x for which there are no records the cursor jumps to A1. Logically wouldn't the search for the other letters also go to A1?

As I say it is of no consequence to me but maybe you can explain what is happening.

Anyway it is doing exactly what I want so many thanks for taking the time.

[mancubus]

you're wellcome...



Columns("A").Find( _
...
...
).Activate



Find method searches the input string...
activate method selects the cell that contains the input string...

LookAt parameter is set to xlPart, partial match, and "x" is part of the "index" in A1.
you may change xlPart to xlWhole if you want to "match entire cell contents."

to exclude the header in col A:

Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find( _
...
...

[danesrood]

Mancubus

I understand what you say about the partial match but shouldn't that also work on the other letters in the word Index?

I've adjusted the code to search on the range rather than the column as below but it now doesn't find anything.


Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find( _

[mancubus]

change 1 to 2 here:

After:=Cells(2, 1)

[mancubus]

i'm using Chip Pearson's great FindAll add-in.

you can find it here:
http://www.cpearson.com/Excel/FindAllXLA.aspx

[danesrood]

mancubus

That's it, works a treat. Thank you so much for taking the time.

Could I ask for one minor tweak if possible just to make it pretty much perfect.

In the message box could you show the letter that could not be found.

Not to worry if you can't because this does precisely what I wanted.

Again sincere thanks.

[mancubus]

you're wellcome.

sure.

Sub FindFunc()

Dim lookupRng As Range
Dim findWhat As Variant

Set lookupRng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
findWhat = Application.InputBox("Find What:=", "F I N D")

On Error GoTo notfound
lookupRng.Find( _
What:=findWhat, _
After:=Cells(2, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False _
).Activate
notfound:
MsgBox Chr(39) & findWhat & Chr(39) & " not found in active worksheet!", _
vbOKOnly + vbExclamation, _
"NOT FOUND"

End Sub

[danesrood]

Mancubus

So sorry to get you to rewrite the code but after adding

Exit Sub
On Error Goto 0

after .Activate it now works perfectly.

Much appreciated as I will be using this very frequently.

Again my sincere thanks for the time you have taken.

[mancubus]

that's ok.

cheers...