Aplicar nomes no Excel está com defeito?

Question

Eu vejo pessoas que tiveram esse problema há 10 anos e ainda não foram corrigidas. Ou talvez eu simplesmente não saiba como usar esse recurso.

Naminhaplanilhaeutenhoessesintervalosnomeados:A1-"name1"
B1 - "name2"
C1 - "nome3"

Em algumas outras 3 células eu tenho estas fórmulas:

=A1
=A1+B1
=COUNT(A1:C1)

Depois de executar Aplicar nomes ... Espero ter isto:

=name1
=name1+name2
=COUNT(name1:name3)

Eu nunca obtenho isso, independentemente das opções que eu escolher. Além disso, geralmente minhas fórmulas ficam completamente arruinadas ...

=name1
=name1
=name1

Como posso obter o resultado de que preciso?
Eu tenho conhecimento em VBA, então tentei criar uma solução para esse problema usando o VBA, mas o código também não estava funcionando.

Estou usando o Excel 2013, de 32 bits (MS Office Professional Plus). SO - Windows 7 Enterprise, 64 bits.

    

Answer 1

Eu não posso replicar isso (com a minha versão do Excel no Windows 7) -

Seeudefinirseusnomesecriarfórmulas

Emseguida,apliqueosnomes

Euobtenhooresultadodesejado-

Comovocêestádefinindoosnomes?Euselecionoacélula,clicoemseutítuloàesquerdadabarradefórmulasedigitoonome.

Comoseu-

aplicarnomes-

Comomostradoaqui

Nenhumaalteraçãosem"mostrar fórmulas" -

aplicarnomes

Ok,aquivamosnós.Office2013,windows7

aplicarnomes

Pareceumafalhareplicada.

Ok,vamostentaroExcel2016noOSXYosemite

Vamosdefinirnossosnomesefórmulas-

Bom,bom,vamosaplicarnossosnomes

Oque?AlertaAfórmulaémuitolonga

Agora,selecionoumeucounte...oque?AlertaOMicrosoftExcelnãopodeencontrarreferênciasparasubstituir

Eéumfracassoparcial?

Ok,entãovamosfazerissomanualmente-

Estranho,nãodestacaointervalo,apenasasduascélulas?

Masfunciona?

Apenasparacomparação,umcountnormaldestacaointervalo-

    

Answer 2

Digamos que começamos com:

ejáatribuímosnomesaA2eB2.NaguiaFórmulas,suspenso:

Definirnome>Aplicarnomes...

Certifique-se de que nós iluminamos ambos os nomes e tocamos OK

e ficamos:

e assim os nomes são aplicados!

    

Answer 3

Como foi apontado em revisão de código , isso causará problemas se, por exemplo, ele estiver procurando por" A1 "e encontrar" A10 ", etc.

Ok, aqui está minha tentativa de contornar o problema. Com isso, suas fórmulas devem usar referências absolutas o tempo todo. Ele funciona em intervalos nomeados maiores que 1 célula.

Por favor, note que estou pesquisando usedrange - mas você pode restringir isso como quiser redefinindo srchRng .

Option Explicit
Sub FixNames()

Dim ClctNames As Variant
Set ClctNames = ActiveWorkbook.Names

Dim rngName As String
Dim rngNameLoc As String
Dim strFrmla As String

Dim c As Range
Dim n As Integer

'Define as needed
Dim srchRng As Range
Set srchRng = ActiveSheet.UsedRange

'For each name (n) in the collection
For n = 1 To ClctNames.Count

    'I'm storing the Named Range's name and address as strings to use below
    rngName = ClctNames(n).Name
    rngNameLoc = ClctNames(n).RefersToRange.Address

    '--Should I break this out into a function? If so, at what point?
    For Each c In srchRng
        'We only want to test cells with formulas
        If c.HasFormula = True Then
           'We have to check if the cell contains the current named range's address
           If InStr(1, c.Formula, rngNameLoc, vbTextCompare) <> 0 Then
              'Since these are perfect matches, no need to look for length or location, just replace
              strFrmla = Replace(c.Formula, rngNameLoc, rngName)
              c.Formula = strFrmla
           End If
        End If
    Next
Next

'No error handling should be needed

End Sub

Você precisa usar referências absolutas, porque quando eu extraio o intervalo RefersToRange.Address do intervalo nomeado, ele retorna um range object - não é um intervalo , então estou definindo como uma string. Eu acho que você poderia escrever uma função que remove as referências $ absoluta se você quiser.

_{que foi divertido}