PROCV - How to avoid window to select external worksheet when none exist

1

I have a planilha that uses data access of lookup of other planilhas PROCV , but they are floating formulas, that is, they change according to the entry in specific fields.

This change occurs through Macro , which simply changes the planilha target of PROCV . Replacing the path excerpt and name of planilha by the equivalent of another planilha target.

For example file:

Planilha_Geral.xlsx 

Folder:

C:\Users\usuario\Desktop

You have the following formula in one of your cells:

=procv(H1;'C:\Users\usuario\Desktop\Empresa_A_Outubro.xlsx'!$H$1:$L$300;6;falso)

I will have other cells where I will search planilhas for other months and on a certain occasion, a macro wheel recognizing and copying the initial part of the formula:

=procv(H1;'C:\Users\usuario\Desktop\Empresa_A_

Replace the address range of planilha with another excerpt, for example:

Planilha B.xlsx

And finally copying the end of the formula that was already in the cell, for example:

Dezembro.xlsx'!$H$1:$L$300;6;falso)

That's because the intention is just to shift the focus between companies and concentrate interesting data for every month (which are in separate worksheets).

Everything works perfectly, but there is a possibility that the worksheet procv will not exist, so I figured that in case of a non-existent worksheet, procv would return an error. I tried this by adding at the beginning of the equation the following:

=SEERRO(

And at the end:

, "Não tem.")

However, procv does not find a spreadsheet pointed, even before considering the error treatment opens a window asking that the spreadsheet be correctly pointed, being necessary to press ESC FOR EACH CELL to apply the SEERRO () treatment.

Considering that each worksheet is referring to one month and each month will bring 40 information from 4 sectors, we have:

40 * 4 = 160

That is, every time the macro runs and replaces the PROCV formula, for each month that there is no company spreadsheet selected, you must press ESC 160 times to complete the macro.

Any ideas how I can avoid opening this worksheet selection window?

    
asked by anonymous 23.04.2015 / 22:01

1 answer

0

Enter the line below at the beginning of the code:

Application.DisplayAlerts = False

And before End Sub , enter:

Application.DisplayAlerts = True
    
28.04.2015 / 20:55