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?