Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi All,

    Attached is a flexible find and replace utillity.

    It automatically searches all your worksheets for the string you enter and also gives you some replace options.

    I appreciate feedback on this!!!
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    I've spent a little bit of time with this - though probably not enough. My current verdict is that I have no preference over find, but find and replace I still prefer the MS version.

    How about 3 radio buttons so that you can limit the find and/or replace to either current selection, current worksheet or entire workbook?

    How about having both find and replace visible at all times?

    The dialog seems wide for my taste. Is it possible to make it slightly narrower or compact it in general - if you're not just leaving that til you've got the functionality you want in place?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Brooke,

    Those are valuable comments.

    I've considered the radio buttons. I'll have a look.

    Having Find and replace visible at the same time will make it hard to get a smaller dialog. I'll have a go though <g>.

    Thanks for the comments!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Brooke,

    Is this any better?
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Just a couple of immediate thoughts - I'll carry on playing with your latest offering.

    a) I would like an option to return to the original selection when I hit end (a seperate "retreat" button is what I've used before) as well as what appears to be default behaviour of staying at the last cell selected in the find listbox.

    [img]/forums/images/smilies/cool.gif[/img] I would like a button next to the replace listbox for select all. I regularly do find and replace operations of the kind where I'm changing =vlookup(a1,sheet1!a:c,3,false) to =vlookup(a1,sheet2!a:c,3,false), where the number of target cells to change can run to thousands (though more often only hundreds, but still enough not to want to select them all individually).

    c) is there something wrong with the range radio button. It doesn't seem to want to play all the time? I'll try and come up with more specific details after a bit more use.

    d) Look in: Comments? I rarely use this on the MS find/replace but other people might.

    e) Look in: Graphs? I have the following code for changing graph titles (Well, you did call it a flexible find and replace utility!) - I only use this once a quarter (Q3 to Q4) but it does save a lot of time and it would seem to make sense to keep this type of find and replace operation in the same place as other find and replace operations.

    <pre>Sub MakeChartTitleChanges()

    Dim objChart As Chart

    Dim strChartTitleOld As String
    Dim strChartTitleNew As String
    Dim strSearch As String
    Dim strReplace As String

    Dim intReplaceStartPos As Integer
    Dim intReplaceEndPos As Integer

    strSearch = InputBox("enter search string")
    strReplace = InputBox("enter replacement string")

    For Each objChart In ActiveWorkbook.Charts

    strChartTitleOld = objChart.ChartTitle.Characters.Text
    intReplaceStartPos = InStr(strChartTitleOld, strSearch)
    intReplaceEndPos = intReplaceStartPos + Len(strSearch) - 1

    If intReplaceStartPos > 0 Then
    strChartTitleNew = Left(strChartTitleOld, intReplaceStartPos - 1) _
    & strReplace & _
    Right(strChartTitleOld, Len(strChartTitleOld) - intReplaceEndPos)

    objChart.ChartTitle.Characters.Text = strChartTitleNew
    Else
    'nothing - skipping to next chart
    End If

    Next

    Set objChart = Nothing

    End Sub
    </pre>


    f) The size and shape of the dialog feels a lot better to me but I'm instantly wanting you to put the search and the replace string textboxes next to each other. however, that may just be over-familiarity with the MS version - I'll see how I feel once I've used it more.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Brooke,

    <<a) I would like an option to return to the original selection when I hit end (a seperate "retreat" button is what I've used before) as well as what appears to be default behaviour of staying at the last cell selected in the find listbox.>>

    I've been comtemplating on this. My thought was to return to the original selection unless the user has clicked on entries in the found items box. I could add a checkbox "Return to original selection" somehwere though.

    <<[img]/forums/images/smilies/cool.gif[/img] I would like a button next to the replace listbox for select all. I regularly do find and replace operations of the kind where I'm changing =vlookup(a1,sheet1!a:c,3,false) to =vlookup(a1,sheet2!a:c,3,false), where the number of target cells to change can run to thousands (though more often only hundreds, but still enough not to want to select them all individually).>>

    No big deal to add that button I guess. But you can always click the first and shift-click the last item.

    <<c) is there something wrong with the range radio button. It doesn't seem to want to play all the time? I'll try and come up with more specific details after a bit more use.>>

    Worked for me (xl5 and xl2K).

    <<e) Look in: Graphs? I have the following code for changing graph titles (Well, you did call it a flexible find and replace utility!) - I only use this once a quarter (Q3 to Q4) but it does save a lot of time and it would seem to make sense to keep this type of find and replace operation in the same place as other find and replace operations.>>

    We could even take this a lot further. Together with Bill Manville I am working on a new verison of Findlink that has the same "look and feel" as flexfind has. Deleting the found links (replacing them with their values) is relatively easy, since that was already coded. But replacing of text in objects is a different piece of cake altogether. There are so many properties of the objects to be searched...
    Who knows, maybe I get fanatic and cook somthing up.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Jan

    This looks very useful on a first test drive. I'm not sure of your specific motivations behind writing such a utility, but one of mine would have stemmed from providing ease of access the "hidden" characters (linebreaks etc.) through the use of dropdown lists or similar.

    I tend to like the form expansion buttons used in Word for these features: More

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Alan,

    <<I'm not sure of your specific motivations behind writing such a utility>>

    Like many things I made, I was asked a question along the lines of "How can I search all worksheets in a workbook" (which doesn't work in versions prior to XL2K). From the first rudimentary code I created then , I made flexfind.
    Then I thought to add replace functionality. Well. That is where I am now <g>.

    Considering to start looking if I can add S&R of object information as well (which is quite something else as you may understand...)

    <<Hope that's useful input>>

    Most certainly so!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hello Jan --

    That is one great utility !!!
    I'm an Excel developer myself -- and FlexFind looks very useful.

    Just a couple of comments / suggestions

    1. This is in the case of a workbook with a single sheet.
    When the 'Workbk' option is selected, and I type the Text to Find,
    the *same* cell reference/s are shown multiple times in the 'Click on an entry...' list-box.
    But if I click on the 'Sheet' option button, it correctly shows only the unique cell reference/s.

    2. If the searched text is not found, the VBA Error dialog is shown
    and the FlexFind dialog box is no longer available, after I press 'End' in the VBA Error dialog box.
    Maybe you can build in some 'On Error...' code to handle this error
    and then come back to the 'Text to Find' box,
    displaying the text *before* the error occurred.

    If I think of something more, I'll post it later.

    Ciao
    Khushnood

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Khushnood,

    <<1. This is in the case of a workbook with a single sheet.
    When the 'Workbk' option is selected, and I type the Text to Find,
    the *same* cell reference/s are shown multiple times in the 'Click on an entry...' list-box.
    But if I click on the 'Sheet' option button, it correctly shows only the unique cell reference/s.>>

    I don't see these double entries. But there was a bug in the utility (shows I only tested it on itself <g>), which is fixed in the attached version.

    <<2. If the searched text is not found, the VBA Error dialog is shown
    and the FlexFind dialog box is no longer available, after I press 'End' in the VBA Error dialog box.
    Maybe you can build in some 'On Error...' code to handle this error
    and then come back to the 'Text to Find' box,
    displaying the text *before* the error occurred.
    >>

    That should not happen, but maybe had something to do with the bug I just mentioned.

    Error trapping will be included when finished.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Forgot the attachment,...
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Jan, this is great! I'm working on a workbook and searching for part numbers across multiple sheets for different customers. One suggestion I would make is to add some kind of "memory" for recent finds -- so that you can not only do your present find, but have a dropdown that lists your last four or five, so that you can repeat past finds quickly and with ease. A second idea: Would it be possible to add a minimize button?

  13. #13
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Jan --

    I was able to reproduce the VBA error in the attached file, with your latest fix, too.

    The exact search was like this -
    * Text to find - Type OA5 (at this point I get the 'Matched 1000 matched cells, continue ?'
    * Click Yes
    * Type 519
    * and the VBA Error occurs

    I've also attached a jpeg screen shot so you can check it out.


    Ok, now for a couple of suggestions -

    1. Can you make the dialog box work in vbModeless mode (if the current Excel version is 2000 or higher) ?
    That would be nice, so that one can select a found cell and edit it in the worksheet, on-the-fly, without exiting the dialog box.

    I've done this sort of thing earlier, where if the Excel version is 97 or lower, the dialog box works without the vbModeless option.


    2. The '# characters' option is good to get the utility to start searching only after the number of characters is reached.
    But it doesn't 'remember' that setting across Excel sessions.
    I've also read another post asking for a 'remember' feature in the searched text, as a drop-down list !
    I know the feeling --
    users ask for more features as they get more familiar with your work <g> !

    Another way to tackle this could be to
    give a check box button to start searching 'instantly' or
    only after the user finishes typing
    Then you won't need to loop through the check for '# characters'

    Hope this helps...
    This is now becoming quite interesting !!!

    Ciao
    Khushnood
    Attached Files Attached Files

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi everyone,

    Here is the latest version (sorry if I've flooded you all with versions <vbg>).

    Improvements:

    List of items searched for
    Shortcutkeys for form controls.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexible find & replace utility (5, 95, 97, 2000, XP)

    Hi Jan --

    I use Excel 2000 (v9.0.3821 SR-1).

    I can understand why you wouldn't want to re-code the entire thing in user-forms.
    And also about the modeless bit...

    Also, as I had myself said, users will keep asking for more !!!

    But the utility is good enough in its present form....

    Have just downloaded the latest version, and will write back if there's anything to add...

    Ciao
    Khushnood

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •