Excel Soma de dados no VBA

[josequali]

Bom dia @LaerteB@Midoritenho a seguinte situação.

Na tabela dadosnf, tenho uma coluna nomeada como STATUS2. Nessa coluna (CI), tenho seis tipos de status nomeados de Análise de 1 a 6 (ao lado eu coloquei uma tabela nomeada como seria o nome desses status, mas para ficar mais fácil deixarei como análise de 1 a 6).

No VBA eu coloquei seis LABEL nomeadas como txt1 a txt2 (em preto). Meu objetivo é fazer com que o Excel some as células que estejam nomeadas como, por exemplo, análise 1 na tabela dados nf, e exiba essa informação na txt1. Some as informações da análise 2 e exiba na txt2, assim por diante.

OBS: os txt são as Label.

Não sei se fica mais fácil fazer isso na listbox1, ou se direto na tabela. No vba no campo em Sub filtrar () a label13 (TOTAL) faz a soma de todas as informações da listbox1. Não sei se teria que fazer o mesmo processo.

 

form_inconformidades.Label13.Object = Planilha9.Cells(Rows.Count, 1).End(xlUp).Row - 2 & " Registros Encontrado (s)."

 

Desde já agradeço a ajuda e disponibilidade! Obrigado 

Soma dados excel.rar

[Midori]

1 hora atrás, josequali disse:

Meu objetivo é fazer com que o Excel some as células que estejam nomeadas como, por exemplo, análise 1 na tabela dados nf, e exiba essa informação na txt1

É para contar as ocorrência na coluna CI ou fazer um SOMASE (aí qual valor deve ser somado)?

[josequali]

@Midori Deve-se soma por exemplo. Na tabela "dadosnf" deve-se somar todas as linhas escritas como análise1 na coluna CI e jogar a informação na txt1 ao carregar a listbox1. Assim como para as linhas que tenha a informação análise 2 e jogar a soma na txt2. E assim sucessivamente.

 

Assim como se for cadastrado uma nova análise, essa deverá ser somada as que já existem.

Acho só que escrevi errado, no caso não seria a soma das células,  e sim as linhas que tenham essas condições citadas, ao carregar a listbox1 do vba (tem um userform).

@Midori

[Midori]

@josequali  Para não ter que atribuir um por um você pode usar um loop para contar e fazer a atribuição com base no nome do controle,

 

Dim L       As Object
Dim Total   As Long

For Each L In Me.Controls
    If TypeName(L) = "Label" Then
        If Left(L.Name, 3) = "txt" Then
            L = WorksheetFunction.CountIf(Sheets( _
                "bancodedadosocorrencia").[CI:CI], "Análise" & Right(L.Name, 1))
            
            Total = Total + L
        End If
    End If
Next L

Label13 = Total

 

Mas se quiser atribuir um por um pode ser assim,

 

txt1 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Análise1")
txt2 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Análise2")
...

[josequali]

@Midori Funcionou perfeitamente! usei esse código

 

txt1 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Análise1")

txt2 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Análise2") ...

 

 

Agora queria tentar a seguinte situação, tenho agora a txt7 e queria que nela fosse somada apenas as análises de 1 ao 5, como poderia fazer. A análise 6  não pode ser somada. 

[Midori]

@josequali  Você pode usar um For assim,

 

Dim I       As Integer
Dim Soma    As Long

For I = 1 To 5
    Soma = Soma + WorksheetFunction.CountIf( _
        Sheets("bancodedadosocorrencia").[CI:CI], "Análise" & I)
Next I

txt7 = Soma

 

[josequali]

@Midori não funcionou  

[Midori]

@josequali  Testei aqui e deu certo.

 

Teve alguma mensagem de erro?

 

Você criou o label txt7?

 

[josequali]

@Midori sim. coloquei em initialize

esse código era o meu adaptado para a condição anterior

 

'soma status
txt1 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a análise da criticidade e definição do responsável")
txt2 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Realizar a análise da causa raiz e elaboração do plano de ação")
txt3 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a aprovação do plano de ação")
txt4 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Implementar o plano de ação")
txt5 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a avaliação da eficácia do plano de ação")
txt6 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Encerrada")
 

[Midori]

@josequali  O código é em initialize.

 

Mas você criou um label e nomeou como txt7? Se não fizer isso o VBA vai considerar txt7 como uma variável e não vai acontecer nada.

 

Se ainda não estiver conseguindo anexe a planilha.

[josequali]

@Midori sim eu fiz

21 minutos atrás, josequali disse:

@Midori sim eu fiz

Private Sub UserForm_Initialize()


Dim I       As Integer
Dim Soma    As Long

For I = 1 To 5
    Soma = Soma + WorksheetFunction.CountIf( _
        Sheets("bancodedadosocorrencia").[CI:CI], "????????????" & I)
Next I

txt7 = Soma
'soma status
txt1 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a análise da criticidade e definição do responsável")
txt2 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Realizar a análise da causa raiz e elaboração do plano de ação")
txt3 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a aprovação do plano de ação")
txt4 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Implementar o plano de ação")
txt5 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a avaliação da eficácia do plano de ação")
txt6 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Encerrada")

'soma total
form_inconformidades.Label14.Object = Planilha9.Cells(Rows.Count, 1).End(xlUp).Row - 3
'txt7 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Total de  N/C Abertas")
txt8 = WorksheetFunction.CountIf(Sheets("bancodedadosocorrencia").[CI:CI], "Encerrada")
'Para adicionar informação no combobox (caixa com selecao de lista)

 

 

Acho que em análise é onde da o bug. vou mandar a minha planilha já com as modificações.

@Midori

Soma dados excel.rar

[Midori]

@josequali  Assim não vai funcionar.

 

Você mudou todos os status, antes na coluna CI o texto era "Análise" e um número.

 

Agora o label txt7 tem que contar o que for diferente de Encerrada?

 

Se for isso tire o For e tente fazer assim,

 

txt7 = WorksheetFunction.CountIfs([CI:CI], "<>Encerrada", _
    [CI:CI], "<>") - 1 + IIf([CI1] <> "", -1, 0)

 

[josequali]

@Midori perfeito! Muito obrigado  

[josequali]

SOMA DOS DADOS POR USUÁRIO

 

boa noite @Midori  @LaerteB 

Estou tentando filtrar agora mais um pouco a minha análise. Agora preciso realizar a seguintes situação,
que na verdade é uma continuidade das etapas anteriores.

Eu preciso que a contagem agora seja realizada na seguinte condição.

Se na TEXTBOX usuário (txtperfil) o nome for JOSE, nas label txt 1 ao 6, assim como e ao txttotal, só deve-se realizar a soma das linha que tem o nome de JOSE na COLUNA U, para cada STATUS na COLUNA CI e na txt7 deve-se seguir a condição da #5 (txt7 é a soma da txt1 ao 5, sem a soma da txt6. 

 

- Aguardando a análise da criticidade e definição do responsável
- Realizar a análise da causa raiz e elaboração do plano de ação
- Aguardando a aprovação do plano de ação
- Implementar o plano de ação
- Aguardando a avaliação da eficácia do plano de ação
- Encerrada 

por  exemplo PEDRO tem:

 

- Aguardando a análise da criticidade e definição do responsável  -  1 caso
- Realizar a análise da causa raiz e elaboração do plano de ação  - 2 casos
- Aguardando a aprovação do plano de ação - 0 casos
- Implementar o plano de ação  - 1 casos
- Aguardando a avaliação da eficácia do plano de ação - 0 casos
- Encerrada  - 0 casos

 

 

obs: o nome dos usuários são alterados na célula A2, na guia USUÁRIOS e carregados na textbox (txtperfil).

 

 

Essa mesma condição deve ser aplicada para os outros usuários: ANNA, JOSEFA, GABRIELA E PEDRO.

 

 

Agora se o usuário o nome for "QUALIDADE", que será o administrador da planilha, esse tem que ver a soma de tudo, independente do nome dos usuário na coluna U, sendo que o nome "QUALIDADE" só irá aparecer na textbox1 assim como na planilha "USUÁRIO" na linha A2 (que é o campo que irá também carregar o nome dos outros usuários a célula A2 quando presentes), nunca aparecendo na coluna U, como observado.

Na textbox (txtperfil), então deve-se realizar a soma de tudo separadamente, mas sem discriminação por nome de usuário como nas condições anteriores, sendo assim deve-se aplicar por exemplo o mesmo código que já é usado (que já uso na planilha).

 

 

 

 

soma dos dados.rar

[josequali]

Bom dia @Midori @LaerteB@Edson Luiz Branco! Estou muito precisando resolver esse problema do tópico #14.  Alguém saberia resolver essa bucha? Desde já agradeço a disponibilidade  .

[Midori]

Em 29/11/2020 às 21:40, josequali disse:

Se na TEXTBOX usuário (txtperfil) o nome for JOSE, nas label txt 1 ao 6, assim como e ao txttotal, só deve-se realizar a soma das linha que tem o nome de JOSE na COLUNA U, para cada STATUS na COLUNA CI e na txt7 deve-se seguir a condição da #5 (txt7 é a soma da txt1 ao 5, sem a soma da txt6. 

Para contar com o critério do nome use CountIfs, p.ex,

 

txt1 = WorksheetFunction.CountIfs([U:U], txtperfil, Sheets("bancodedadosocorrencia").[CI:CI], "Aguardando a análise da criticidade e definição do responsável")

 

Obs: No Initialize a atribuição de txtperfil (Me.txtperfil.Value...) tem que estar antes das atribuições dos textbox.

[josequali]

 

@Midori Ficou perfeito!  

 

 

 

@Midori e para as demais condições. Soma total da txt1 a txt 6  e jogar a soma na txt20 e depois a soma da txt1 a txt5 menos a txt6 jogar na txt7, também levando em consideração o nome do usuário.

 

E a última condição é que se o usuário o nome for QUALIDADE eu queria que carrega-se todas as informações porque esse usuário será o administrador. 

 

 

Desde já agradeço a disponibilidade!  

[josequali]

@MidoriConsegui resolver os outros problemas referentes aos usuários. Só preciso mesmo agora resolver a a soma da txt1 a txt5 menos a txt6 jogar na txt7, também levando em consideração o nome do usuário.

[Midori]

@josequali Para pegar o total você pode atribuir os CountIfs a variáveis e depois somar todas e atribuir ao label da soma. Sobre a quantidade do administrador é só fazer como antes (com o CountIf apenas).

 

Mas acho que para deixar a manutenção do seu projeto mais simples você podia mudar os nomes dos controles e criar Subs para alguns procedimentos.

 

No caso das somas das análises poderia mudar os nomes dos controles de txt1, txt2... para lblAnalise1, lblAnalise2. Assim ficará mais fácil contar e somar, p.ex,

 

Sub AtribuiQuantidadeOcorrencia( _
    Formulario As Object, _
    LabelTotal As Object, _
    PlanilhaOcorrencia As Worksheet, _
    Optional Nome As String = "")
    
    Dim Controle    As Object
    Dim Total       As Long
    
    With PlanilhaOcorrencia
        For Each Controle In Formulario.Controls
            If TypeName(Controle) = "Label" And Left(Controle.Name, 10) = "lblAnalise" Then
                Dim Analise As String
                
                Analise = Replace(Controle.Name, "lblAnalise", "Análise")
                
                If WorksheetFunction.CountIf(.[CN:CN], Analise) > 0 Then
                    Dim Ocorrencia      As String
                    Dim ContaOcorrencia As Long

                    Ocorrencia = WorksheetFunction.VLookup(Analise, .[CN:CO], 2, 0)
                    
                    If Nome <> "" Then
                        ContaOcorrencia = _
                            WorksheetFunction.CountIfs(.[U:U], Nome, .[CI:CI], Ocorrencia)
                    Else
                        ContaOcorrencia = _
                            WorksheetFunction.CountIf(.[CI:CI], Ocorrencia)
                    End If
                    Total = Total + ContaOcorrencia
                    Controle.Caption = ContaOcorrencia
                End If
            End If
        Next Controle
    End With
    LabelTotal.Caption = Total
End Sub

 

 Com essa Sub é só passar os argumentos e chamar em qualquer evento, pode ser Initialize, Change, Click do botão, etc.

 

Dim PlanilhaOcorrencia  As Worksheet
...
Private Sub UserForm_Initialize()
    Set PlanilhaOcorrencia = ThisWorkbook.Worksheets("bancodedadosocorrencia")
    
    Call AtribuiQuantidadeOcorrencia(Me, Me.lblTotal, PlanilhaOcorrencia)
End Sub

Private Sub txtPerfil_Change()
    Call AtribuiQuantidadeOcorrencia(Me, Me.lblTotal, PlanilhaOcorrencia, txtPerfil.Text)
End Sub

 

Deixei o argumento Nome opcional para somar todos quando a sub não receber nenhum nome.

 

[josequali]

@Midori Muito obrigado