EXCELer

Há dias, num newsgroup, colocaram a questão de saber como se poderia ordenar um range dinâmico em termos de linhas, com o tipo de ordenação na horizontal (por linha) e da direita para a esquerda.

Tomemos o seguinte exemplo demonstrativo da pretensão e do consequente resultado:

O Código:

Sub SortRow()
'JRod
'
'Copyright 2007
   Dim R, RowNum As Long
   RowNum = ActiveSheet.UsedRange.Rows.Count
   For R = 2 To RowNum + 1
    Range("A" & R & ":E" & R).Sort key1:=Range("A" & R), _
        Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlLeftToRight
   Next R
End Sub

Tópicos relacionados:

• Determine the "real" UsedRange

• Lastcell, Reset Last Used Cell (by David McRitchie)

• UsedRange Property [Excel 2003 VBA Language Reference]

• Reset UsedRange in Excel (by Mark Jerzykowski)

• Some notes on sorting in Excel (by David McRitchie)

• VB Helper (by Rod Stephens)

Por mail, colocaram-me a seguinte questão (adaptada):

 "Estou a criar um registo de membros... contudo, dado que o seu número facilmente poderá chegar aos 50, corro o risco de criar entradas duplicadas.
Assim, e depois de mais uma visita ao Exceler encontrei um post sobre o assunto [post de 2004-12-16]. Mas, a solução apresentada não me pareceu funcionar com texto... Agradeço, se possível, a informação de se será possível aplicar ou não a texto... "

Várias soluções se podem apresentar.

Uma delas, por exemplo, será a utilização de "Data Validation". No exemplo seguinte, sempre que se escrever numa das células do Range um conteúdo duplicado, vai dar uma mensagem:

Como fazer:

Outra possibilidade, é utilizar uma fórmula [com o mesmo Range de exemplo (D1:D10)] - na célula E1: =IF(MAX(COUNTIF($D$1:$D$10;$D$1:$D$10))>1;"Duplicado";"")
e copiando até ao fim do range [no exemplo,E1:E10] - Neste caso, vai dar TODAS as entradas duplicadas no range D1:D10, ou seja, considera entrada duplicada as duas entradas:.

Outra possibilidade ainda será, se se pretender que apenas as entradas duplicadas sejam consideradas, então teremos, no exemplo, em F1:
=IF(D1="";"";IF(MATCH(D1;D$1:D$10;0)<ROW(D1);"Duplicado!";""))
e copiando de F1 até F10:

Tópicos relacionados:

• Data Validation

• Data Validation (II)

• A Função Max()

• A Função Row()

• A Função CountIf()

Para uma possível solução e de modo a tornarmos a apresentação um pouco mais agradável (utilizando uma TextBox em vez de uma célula, para apresentar o resultado), tomemos, então, o seguinte exemplo:

O que se pretende será:

• Digitar o Código Postal em B10

• Pesquisar nas Colunas "E" e "G" pelo Código Postal

• Se existir, mostrar a cidade correspondente numa TextBox

No exemplo, a primeira acção a tomar, será criar a TextBox:

E, depois, para efectuar a pesquisa, por coluna, do Código Postal, digitar o seguinte, por exemplo em:

N5: =IF(ISNA(INDEX($F$3:$F$9;MATCH(B10;$E$3:$E$9;0)));"";INDEX($F$3:$F$9;MATCH(B10;$E$3:$E$9;0)))

e em N6: =IF(ISNA(INDEX($H$3:$H$9;MATCH (B10;$G$3:$G$9;0)));"";INDEX($H$3:$H$9;MATCH(B10;$G$3:$G$9;0)))

Para concatenar as duas células e obtermos o resultado apenas numa, digitaremos então,

em N8: =N5&N6

Tópicos relacionados:

• Funções Index() e Match()

• Index() e Match() ou VLookUp()?

• Combinar as Funções Index() e Match()

• A Função ISNA()

• VLOOKUP() e ISNA()

 Há dias, por mail, fizeram-me a seguinte pergunta:

"Como hei-de fazer para que, partindo do seguinte conteúdo em duas células: 20+500- e 18+200, tenha como resultado numa terceira célula, o seguinte: 2+300 e, sempre que altere um destes valores parcelares, no mesmo formato, o resultado reflicta essa alteração?"

O exemplo:

A fórmula:

=LEFT(A1;2)-LEFT(B1;2)&"+"&MID(A1;4;3)-RIGHT(B1;3)

Tópicos relacionados:

• Left(), Right() & Mid() in VBA
• Save time by using Excel's Left, Right, and Mid string functions
• Using Excel's Find and Mid to extract a substring when you don't know the start point

Já reparou que pode ver, alterar, apagar ou fazer merge de estilos, no Excel?

Vá a FORMAT | STYLE:

• Tópicos relacionados:

Using Styles in Excel (I)

Using Styles in Excel (II)

Style Object

Styles Collection

Normalmente, nas nossas Macros, quando pretendemos seleccionar uma Sheet, ou, por exemplo, limitar a uma área de scroll, utilizamos o seguinte Código:

Sheets("INICIO").Select
Sheets("INICIO").ScrollArea = "A1:Q31"

O que pode acontecer é que um utilizador, pelo facto de não ser possível proteger o tabulador que dá o nome à Sheet [que, no nosso exemplo, tem o nome "INICIO"], resolve alterar o nome do tabulador, para "FIM".

Resultado: uma mensagem de erro, porque o que o código diz, é que a folha se denomina "INICIO" e, na realidade, o que lá se encontra é o nome "FIM".

Uma maneira de obviar o problema, é resolvê-lo pela via mais simples, ou seja, "obrigar" que a folha seja seleccionada e faça o scroll limitado, independentemente do nome que o tabulador tenha. Assim, se a folha com o nome "INICIO" ou com o nome "FIM" for efectivamente a 1ª folha, então podemos utilizar, em vez de indicar o nome da folha como está acima, o seguinte [o mesmo será para as outras folhas, mudando apenas o algarismo]:

Sheet1.Select
Sheet1.ScrollArea = "A1:Q31"

Outra maneira, é  "obrigar" que a folha, ao ser aberta, venha a adquirir o nome que nós pretendemos inicialmente, ou seja, que fique com o nome "INICIO":

Dim tabName

Sheet1.Select
tabName = "INICIO"
ActiveSheet.Name = tabName

Sheets("INICIO").ScrollArea = "A1:Q31"

Por último, porque não esconder, pura e simplesmente, o(s) tabulador(es)? Assim, de certeza que já não haverá "tendências" para fazer alterações inconvenientes ;-)

O Código para pôr a barra dos tabuladores na situação de "hidden":

Private Sub Workbook_Open()
    'Tira a barra de tabuladores com os nomes das folhas...
    ActiveWindow.DisplayWorkbookTabs = False
End Sub

Se pretendermos apagar linhas inteiras a partir de células vazias num determinado range, incluindo uma mensagem de alerta se, nesse range, não houver nenhuma célula vazia, podemos utilizar o seguinte código:

Sub FindAndDelete()

    Dim myRange As Range

    On Error Resume Next
    Set myRange = Range("A1:A100")

    If Application.CountA(myRange) = 100 Then
        MsgBox "Não existem células vazias no Range!"
    Else
        myRange.SpecialCells(xlBlanks).EntireRow.Delete
    End If

End Sub

• Tópicos relacionados:

SpecialCells Method

Função CountA()

O TechEd  Developers  é a maior Conferência europeia anual da Microsoft para Programadores e Arquitectos. São 5 dias de formação técnica avançada, organizados por 13 core tracks e inúmeras Breakouts,  Whiteboard Discussions, Demo Extravaganzas, Self-Paced Hands-on Labs, Painéis de Discussão, Apresentações das Comunidades e muito mais.

Datas importantes para registo:

Super Early Bird - até 31 Julho 2007: €1,945 – desconto de €300 sobre Full Price, MAIS convite para assistir a sessões exclusivas privadas com os Top Speakers da Microsoft.

Utilize o código TED11200.

Early Bird - até 28 Setembro 2007: €1,945 - €300 sobre Full Price

Full Price – a partir de 29 Setembro: €2,245

Aceda ao Web site para informação mais detalhada sobre o registo.

www.microsoft.com/europe/teched-developers/

Por mail, recebi a seguinte pergunta: "Tenho o office 2003 instalado e necessito de colocar numa fórmula mais de sete "IF's" consecutivos, pois estou a comparar valores do mês actual com valores de meses anteriores. Como fazer?"

Como é sabido, o Excel apenas permite até 7 IF's [Função IF()] aninhados.

Uma das maneiras de obviar este pequeno problema, será utilizar uma UDF [User Defined Function] que permita estabelecer mais do que 7 critérios.

A UDF que apresento a seguir [a qual deverá ser copiada para um módulo de VBA, utilizando ALT + F11, para a ceder ao Editor de VBA e criando um novo módulo - Insert > Module -], possibilita a utilização de tantos critérios, quantos os que sejam necessários, embora se verifique que, quantos mais critérios, mais lento se tornará o cálculo, como é evidente.

O Código (créditos para Harlan Grove - ver perfil):

E, agora, apresento a máscara de utilização da função:

=multi_if (condition_a;value_a;condition_b;value_b;condition_c;value_c;
condition_d;value_d;condition_e;value_e;condition_f;value_f;
condition_g;value_g;condition_h,value_h;value_else)

O exemplo:

  =multi_if(AP4=0;0;$D4<>0;AP4/$D4;$J4<>0;AP4/$J4;$N4<>0;AP4/$N4;$R4<>0;AP4/$R4;$V4<>0;AP4/$V4;$Z4<>0;AP4/$Z4;$AD4<>0;AP4/$AD4;$AH4<>0;AP4/$AH4;$AL4<>0;AP4/$AL4;1)

• Tópicos relacionados:

Nested IF's - Chip Pearson

How to avoid nested If's in Excel - Mark Kelly

Há dias, perguntaram-me, por mail, como seria possível construir uma fórmula que, numa determinada sheet, apontasse para uma célula de outra sheet, de forma a que, se na primeira fosse adicionada uma nova coluna, a fórmula não sofresse alteração, ficando, deste modo a apontar sempre para a mesma célula. Vejamos então o exemplo:

Na sheet1, na célula B8, temos determinado conteúdo, no caso, o texto "teste":

Por sua vez, na sheet2, temos em D5, a referência à célula B8, da sheet1:

Vamos então inserir, na sheet1, uma nova coluna, passando, deste modo, a antiga coluna B para coluna C:

O que vai acontecer, é que a referência, na sheet2, para a sheet1 passa a ser para a célula C8:

Agora, vamos inserir na nova coluna B, em B8, o texto "teste1":

E, na sheet2, construimos s seguinte fórmula, em B10, com referência à célula B8, da Sheet1. O resultado será:

O resultado será:

O que resulta, é que, com a função INDIRECT(), a referência à célula B8 da sheet1 é uma referência que se mantém, mesmo que se adicionem colunas nessa sheet: