none
Looking for an Excel Event RRS feed

  • Question

  • Let's say you perform a Find/Replace operation.   A window pops up telling you the number of replacements made.

    What I'd like to know is how I get that number programmatically.   The AfterCalculate event handler is not good since it won't fire until the replacements are done. 

    Judging by how the events fire, the actual updates don't BEGIN until you click the OK button.


    • Edited by GermanEZI Thursday, March 16, 2017 9:33 PM My Image isn't there.
    Thursday, March 16, 2017 9:17 PM

Answers

  • Excel VBA doesn't offer a way to capture the number of replacements from the dialog. Using CountIf is a workaround.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by GermanEZI Monday, March 20, 2017 5:07 PM
    Monday, March 20, 2017 4:52 PM

All replies

  • You could use Application.WorksheetFunction.Countif(range, searchvalue) before Replace to retrieve the number of occurrences of searchvalue.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, March 16, 2017 10:11 PM
  • Hi GermánH,

    I think the suggestion given by the Hans Vogelaar MVP can solves your issue.

    below is an example of that.

    Sub demo()
    Dim Rng
    Set Rng = Columns("B:C")
      MsgBox Application.WorksheetFunction.CountIf(Rng, "hello")
    End Sub
    

    Reference:

    WorksheetFunction.CountIf Method (Excel)

    you had asked you want event to execute this code, but you did not mentioned that you are trying to use any control or just trying to use sheets?

    I think you can create one button and on click of that button it will show you the inputbox for enter the value that you want to search and after that you can show the msgbox.

    if it not suitable for you then try to explain your situation here, so that we can try to suggest you further.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 17, 2017 3:57 AM
    Moderator
  • Not quite what I'm looking for.  I likely wasn't clear with my explanation.

    STEPS:
    Open a Spreadsheet with some information (e.g. a table of names)
    Press "CTRL-H".  Key in your Search and Replace information and click on "Replace All"

    A dialog box pops up with the number of replacements.   I'd like my Add-In to capture that number.




    Monday, March 20, 2017 4:48 PM
  • Excel VBA doesn't offer a way to capture the number of replacements from the dialog. Using CountIf is a workaround.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by GermanEZI Monday, March 20, 2017 5:07 PM
    Monday, March 20, 2017 4:52 PM
  • Thanks for the update.
    Monday, March 20, 2017 5:07 PM