CountIfs com datas "dd/mm/aa"

Visual Basic for Aplication e macros no Excel.
Regras do fórum
Sua dúvida foi respondida? Marque como RESOLVIDO em seus tópicos, usando o botão com marca verde. Imagem

CountIfs com datas "dd/mm/aa"

Mensagempor ravargon » Seg Jun 11, 2018 2:45 pm

Boa tarde pessoal,
Fiz uma macro para gerar uns relatórios com dados importados de outras planilhas (uma para cada região). A parte de importação dos dados funcionou perfeitamente. Quando comecei a cruzar esses dados, filtrando-os por data, para gerar índices e comparar uma região com outra, o meu código simplesmente não conseguia entender as datas. Mais estranho que isso, para a data inicial ele entendia no formato "mm/dd/aa" e na data final no formato "dd/mm/aa".
Após muita pesquisa para tentar encontrar a falha, num momento de desespero coloquei a data inicial de uma forma e a final de outra e passou a funcionar. Apesar disso, queria entender o porque de não poder configurá-las da mesma forma.
Fiz em VBA para poder incluir outras regiões posteriormente sem grandes modificações (espero que meu código consiga fazer isso adequadamente).
Obrigado desde já
Ramon Varela

Segue o código e o arquivo anexo:

Código: Selecionar todos
Sub At_Rel()
Dim NL, n, R As Integer
Dim NR, NA As String
Dim dDI, dDF As Date
Dim lDI, lDF As Long

R = MsgBox("Atualizar Resumo?", vbYesNo + vbQuestion, "Atualizar Resumo")

If R = vbYes Then

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sheets(4).Unprotect

'Limpar dados antigos
NL = WorksheetFunction.CountIf(Sheets(4).Range("B4:Z4"), "<>" & "") 'Regiões preenchidas
Sheets(4).Range(Cells(4, 2), Cells(26, NL + 1)).ClearContents 'Apaga dados

'Definir valores
NL = WorksheetFunction.CountIf(Sheets(3).Range("D2:D200"), "<>" & "") 'numero de regiões a ler
dDI = Format(DateSerial(Year(Cells(1, 3)), Month(Cells(1, 3)), Day(Cells(1, 3))), "mm/dd/yy")
dDF = DateSerial(Year(Cells(1, 5)), Month(Cells(1, 5)), Day(Cells(1, 5)))
lDI = dDI
lDF = dDF

For n = 2 To NL + 1 'repetir para as regiões a serem lidas
    'Regiões
    Sheets(4).Cells(4, n).Value = Sheets(3).Cells(n, 4).Value 'nome da região a ser lida
    'Qtde de Visitas
    Sheets(4).Cells(5, n).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, _
    Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, Sheets(1).Columns(8 + (n - 2) * 11), "<>" & 0)
    'Qtde Ativos
    Sheets(4).Cells(6, n).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, _
    Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, Sheets(1).Columns(8 + (n - 2) * 11), "ATIVO")
    'Qtde Inativos
    Sheets(4).Cells(8, n).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, _
    Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, Sheets(1).Columns(8 + (n - 2) * 11), "INATIVO")
    'Qtde Prospecção
    Sheets(4).Cells(10, n).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, _
    Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, Sheets(1).Columns(8 + (n - 2) * 11), "PROSPECÇÃO")
    'Qtde Novos
    Sheets(4).Cells(12, n).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, _
    Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, Sheets(1).Columns(8 + (n - 2) * 11), "NOVO")
    'KM Rodados
    Sheets(4).Cells(14, n).Value = WorksheetFunction.SumIfs(Sheets(2).Columns(3 + (n - 2) * 9), _
    Sheets(2).Columns(1 + (n - 2) * 9), ">=" & lDI, Sheets(2).Columns(1 + (n - 2) * 9), "<=" & lDF) _
    - WorksheetFunction.SumIfs(Sheets(2).Columns(2 + (n - 2) * 9), Sheets(2).Columns(1 + (n - 2) * 9), ">=" & lDI, _
    Sheets(2).Columns(1 + (n - 2) * 9), "<=" & lDF)
    'Valor Abastecimento
    Sheets(4).Cells(15, n).Value = WorksheetFunction.SumIfs(Sheets(2).Columns(4 + (n - 2) * 9), _
    Sheets(2).Columns(1 + (n - 2) * 9), ">=" & lDI, Sheets(2).Columns(1 + (n - 2) * 9), "<=" & lDF)
    'Valor Pedágio
    Sheets(4).Cells(16, n).Value = WorksheetFunction.SumIfs(Sheets(2).Columns(5 + (n - 2) * 9), _
    Sheets(2).Columns(1 + (n - 2) * 9), ">=" & lDI, Sheets(2).Columns(1 + (n - 2) * 9), "<=" & lDF)
    'Valor Hospedagem
    Sheets(4).Cells(17, n).Value = WorksheetFunction.SumIfs(Sheets(2).Columns(6 + (n - 2) * 9), _
    Sheets(2).Columns(1 + (n - 2) * 9), ">=" & lDI, Sheets(2).Columns(1 + (n - 2) * 9), "<=" & lDF)
    'Valor Alimentação
    Sheets(4).Cells(18, n).Value = WorksheetFunction.SumIfs(Sheets(2).Columns(7 + (n - 2) * 9), _
    Sheets(2).Columns(1 + (n - 2) * 9), ">=" & lDI, Sheets(2).Columns(1 + (n - 2) * 9), "<=" & lDF)
    'Custo total
    Sheets(4).Cells(19, n).Value = WorksheetFunction.Sum(Sheets(4).Range(Cells(15, n), Cells(18, n)))
    'Valor Pedidos Ativos
    Sheets(4).Cells(22, n).Value = WorksheetFunction.SumIfs(Sheets(1).Columns(9 + (n - 2) * 11), _
    Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, _
    Sheets(1).Columns(8 + (n - 2) * 11), "ATIVO")
    'Valor Pedidos Inativos
    Sheets(4).Cells(23, n).Value = WorksheetFunction.SumIfs(Sheets(1).Columns(9 + (n - 2) * 11), _
    Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, _
    Sheets(1).Columns(8 + (n - 2) * 11), "INATIVO")
    'Valor Pedidos Novos
    Sheets(4).Cells(24, n).Value = WorksheetFunction.SumIfs(Sheets(1).Columns(9 + (n - 2) * 11), _
    Sheets(1).Columns(2 + (n - 2) * 11), ">=" & lDI, Sheets(1).Columns(2 + (n - 2) * 11), "<=" & lDF, _
    Sheets(1).Columns(8 + (n - 2) * 11), "NOVO")
    'Valor Total Pedidos
    Sheets(4).Cells(25, n).Value = WorksheetFunction.Sum(Sheets(4).Range(Cells(22, n), Cells(24, n)))
   
    'Verificar se houveram visitas
    If Sheets(4).Cells(5, n).Value > 0 Then
        '% Ativos
        Sheets(4).Cells(7, n).Value = Sheets(4).Cells(6, n).Value / Sheets(4).Cells(5, n).Value
        '% Inativos
        Sheets(4).Cells(9, n).Value = Sheets(4).Cells(8, n).Value / Sheets(4).Cells(5, n).Value
        '% Prospecção
        Sheets(4).Cells(11, n).Value = Sheets(4).Cells(10, n).Value / Sheets(4).Cells(5, n).Value
        '% Novos
        Sheets(4).Cells(13, n).Value = Sheets(4).Cells(12, n).Value / Sheets(4).Cells(5, n).Value
        'Custo por visita
        Sheets(4).Cells(21, n).Value = Sheets(4).Cells(19, n).Value / Sheets(4).Cells(5, n).Value

    Else
        '% Ativos
        Sheets(4).Cells(7, n).Value = 0
        '% Inativos
        Sheets(4).Cells(9, n).Value = 0
        '% Prospecção
        Sheets(4).Cells(11, n).Value = 0
        '% Novos
        Sheets(4).Cells(13, n).Value = 0
        'Custo por visita
        Sheets(4).Cells(21, n).Value = 0
    End If
   
    'Verificar se há KM rodados
    If Sheets(4).Cells(14, n) > 0 Then
        'Custo por KM Rodado
        Sheets(4).Cells(20, n).Value = Sheets(4).Cells(19, n).Value / Sheets(4).Cells(14, n).Value
    Else
        Sheets(4).Cells(20, n).Value = 0
    End If

    'Verificar se houveram custos
    If Sheets(4).Cells(19, n) > 0 Then
        'Pedidos/Custo Total
        Sheets(4).Cells(26, n).Value = Sheets(4).Cells(25, n).Value / Sheets(4).Cells(19, n).Value
    Else
        Sheets(4).Cells(26, n).Value = 0
    End If
   
    'Verificar se houveram pedidos
    If Sheets(4).Cells(25, n) > 0 Then
        'Custo Total por Pedidos
        Sheets(4).Cells(27, n) = Sheets(4).Cells(19, n) / Sheets(4).Cells(25, n)
    Else
        Sheets(4).Cells(27, n) = 0
    End If
           
Next
       
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Sheets(4).Protect

'Mensagem
MsgBox "Dados Atualizados com Sucesso" & vbCrLf & vbCrLf & Now(), , ThisWorkbook.Name

Else
MsgBox "Atualização Cancelada", , ThisWorkbook.Name
End If
End Sub
Você não está autorizado a ver ou baixar esse anexo.
ravargon
Membro
Membro
 
Mensagens: 15
Registrado em: Qui Fev 19, 2015 10:43 am
Has thanked: 2 times
Have thanks: 3 times

{ SO_SELECT }

Re: CountIfs com datas "dd/mm/aa"

Mensagempor wagner » Seg Jun 11, 2018 7:54 pm

Ramon,

Boa noite!

Fiz pequena alteração na rotina descrita acima. Como não entendi a lógica da programação que você usou, não testei. A alteração que fiz foi apenas quanto a modo de pegar as datas que estão em C1 e E1 da aba Resumo. Talvez isso resolva o problema relatado por você.

Teste e veja se é isso.
Você não está autorizado a ver ou baixar esse anexo.
Se a resposta foi útil para você, por gentileza, Amigo, clique na mãozinha ao lado direito da ferramenta CITAR, no canto superior direito.

Atenciosamente
Wagner Morel
Avatar do usuário
wagner
Moderador
Moderador
 
Mensagens: 4294
Registrado em: Sáb Out 20, 2012 12:49 pm
Localização: Fortaleza - CE
Has thanked: 71 times
Have thanks: 1858 times

Re: CountIfs com datas "dd/mm/aa"

Mensagempor ravargon » Qui Jun 14, 2018 4:16 pm

Boa tarde Wagner,

Obrigado pelo retorno. Eu já havia testado dessa forma, sem sucesso.
O que achei estranho é o fato de a data em C1 ser entendida no padrão "mm/dd/aa" e a data em E1 ser entendida como "dd/mm/aa".

Abraço
Ramon
ravargon
Membro
Membro
 
Mensagens: 15
Registrado em: Qui Fev 19, 2015 10:43 am
Has thanked: 2 times
Have thanks: 3 times


Voltar para VBA & Macros

Quem está online

Usuários navegando neste fórum: Google Adsense [Bot] e 1 visitante