Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
Subscrito Fora do I...
 
Notifications
Clear all

Subscrito Fora do Intervalo (Erro de execução '9')

 
2024 - VBA & Macros
2 Posts
2 Usuários
0 Reactions
814 Visualizações
RSS
 paulochen
(@paulochen)
Posts: 0
New Member
Topic starter
 

Olá Pessoal,

Desenvolvi uma macro que transforma uma linha da planilha em 35 linhas e ela funciona em diversos notebooks.

A versão do excel é o office 2016 365 de 32 bits e a arquitetura dos notebooks é de 64 bits.

Só que em alguns não funcionam e aparecem este erro
Subscrito Fora do Intervalo (Erro de execução '9').

E é o mesmo codigo, mesma maquinas. Tudo igual.

Poderiam me ajudar a descobrir o problema?

Segue abaixo o codigo em vba:

'GUIA GERAL
Sub GERAL()
   Dim x As LongPtr
   Dim y As LongPtr
   Dim cont2 As LongPtr
   cont2 = 1
   x = 5
   If Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("A7") <> "" Then
            y = 7
            y = Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("A" + CStr(y)).End(xlDown).Row + 1
    Else
            y = 7
    End If
   Range("B4").Select
   Do Until IsEmpty(ActiveCell)
      Do Until cont2 > 35
      'Colunas Padrões
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("A" + CStr(y)) = "modify"
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("B" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("B" + CStr(x))
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("C" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("C" + CStr(x))
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("D" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("D" + CStr(x))
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("E" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("E" + CStr(x))
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("F" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("F" + CStr(x))
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("G" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("G" + CStr(x))
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("L" + CStr(y)) = "KG"
            Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("P" + CStr(y)) = "=CONCATENATE(RC[-15],""~"",RC[-14],""~"",RC[-13],""~"",RC[-12],""~"",RC[-11],""~"",RC[-10],""~"",RC[-9],""~"",RC[-8],""~"",RC[-7],""~"",RC[-6],""~"",RC[-5],""~"",RC[-4],""~"",RC[-3],""~"",RC[-2])"
            'Verificao de range do centro de distribuição
            If cont2 > 0 And cont2 < 6 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("H3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("M" + CStr(x))
            ElseIf cont2 > 5 And cont2 < 11 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("N3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("S" + CStr(x))
            ElseIf cont2 > 10 And cont2 < 16 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("T3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("Y" + CStr(x))
            ElseIf cont2 > 15 And cont2 < 21 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("Z3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AE" + CStr(x))
            ElseIf cont2 > 20 And cont2 < 26 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AF3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AK" + CStr(x))
            ElseIf cont2 > 25 And cont2 < 31 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AL3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AQ" + CStr(x))
            ElseIf cont2 > 30 And cont2 < 36 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("H" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AR3")
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("M" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AW" + CStr(x))
            End If
            
            'Faixa de Quantidade 1 - 1374
           If cont2 = 1 Or cont2 = 6 Or cont2 = 11 Or cont2 = 16 Or cont2 = 21 Or cont2 = 26 Or cont2 = 31 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("I" + CStr(y)) = 1
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("J" + CStr(y)) = 1374
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("H4")
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("L" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("I4")
               If cont2 = 1 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("H" + CStr(x))
               ElseIf cont2 = 6 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("N" + CStr(x))
               ElseIf cont2 = 11 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("T" + CStr(x))
               ElseIf cont2 = 16 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("Z" + CStr(x))
               ElseIf cont2 = 21 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AF" + CStr(x))
               ElseIf cont2 = 26 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AL" + CStr(x))
               ElseIf cont2 = 31 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AR" + CStr(x))
               End If
           End If
            
            'Faixa de Quantidade 1375 - 1375
            If cont2 = 2 Or cont2 = 7 Or cont2 = 12 Or cont2 = 17 Or cont2 = 22 Or cont2 = 27 Or cont2 = 32 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("I" + CStr(y)) = 1375
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("J" + CStr(y)) = 1375
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("I4")
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("L" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("J4")
               If cont2 = 2 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("I" + CStr(x))
               ElseIf cont2 = 7 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("O" + CStr(x))
               ElseIf cont2 = 12 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("U" + CStr(x))
               ElseIf cont2 = 17 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AA" + CStr(x))
               ElseIf cont2 = 22 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AG" + CStr(x))
               ElseIf cont2 = 27 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AM" + CStr(x))
               ElseIf cont2 = 32 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AS" + CStr(x))
               End If
           End If
            
            'Faixa de Quantidade 1376 - 2750
            If cont2 = 3 Or cont2 = 8 Or cont2 = 13 Or cont2 = 18 Or cont2 = 23 Or cont2 = 28 Or cont2 = 33 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("I" + CStr(y)) = 1376
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("J" + CStr(y)) = 2750
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("J4")
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("L" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("K4")
               If cont2 = 3 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("J" + CStr(x))
               ElseIf cont2 = 8 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("P" + CStr(x))
               ElseIf cont2 = 13 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("V" + CStr(x))
               ElseIf cont2 = 18 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AB" + CStr(x))
               ElseIf cont2 = 23 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AH" + CStr(x))
               ElseIf cont2 = 28 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AN" + CStr(x))
                ElseIf cont2 = 33 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AT" + CStr(x))
               End If
           End If
            
            'Faixa de Quantidade 2751 - 5500
            If cont2 = 4 Or cont2 = 9 Or cont2 = 14 Or cont2 = 19 Or cont2 = 24 Or cont2 = 29 Or cont2 = 34 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("I" + CStr(y)) = 2751
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("J" + CStr(y)) = 5500
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("K4")
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("L" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("L4")
               If cont2 = 4 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("K" + CStr(x))
               ElseIf cont2 = 9 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("Q" + CStr(x))
               ElseIf cont2 = 14 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("W" + CStr(x))
               ElseIf cont2 = 19 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AC" + CStr(x))
               ElseIf cont2 = 24 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AI" + CStr(x))
               ElseIf cont2 = 29 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AO" + CStr(x))
               ElseIf cont2 = 34 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AU" + CStr(x))
               End If
           End If
            
            'Faixa de Quantidade 5501 - 999999999
            If cont2 = 5 Or cont2 = 10 Or cont2 = 15 Or cont2 = 20 Or cont2 = 25 Or cont2 = 30 Or cont2 = 35 Then
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("I" + CStr(y)) = 5501
               Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("J" + CStr(y)) = 999999999
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("L4")
               'Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("L" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("L4")
               If cont2 = 5 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("L" + CStr(x))
               ElseIf cont2 = 10 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("R" + CStr(x))
               ElseIf cont2 = 15 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("X" + CStr(x))
               ElseIf cont2 = 20 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AD" + CStr(x))
               ElseIf cont2 = 25 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AJ" + CStr(x))
               ElseIf cont2 = 30 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AP" + CStr(x))
               ElseIf cont2 = 35 Then
                  Workbooks.Item(1).Worksheets.Item("COMM_CSV").Range("K" + CStr(y)) = Workbooks.Item(1).Worksheets.Item("GERAL").Range("AV" + CStr(x))
               End If
           End If
            
        cont2 = cont2 + 1
            y = y + 1
        
      Loop
      cont2 = 1
      x = x + 1
      ActiveCell.Offset(1, 0).Select
   Loop
   

End Sub



Private Sub CommandButton1_Click()
   Call Planilha1.GERAL
        Call Planilha5.QuickCull
    
End Sub

Obrigado!

 
Postado : 24/11/2017 9:19 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
 

Sabe dizer em que linha exatamente ocorre o erro ?

Geralmente este erro está associado a tentar acessar alguma área fora do limite, ou algum erro no nome da aba, nome do arquivo, até onde sei não tem problema de versão 32 ou 64bits, mas se for versão de excel, por exemplo o 2003 tem menos linhas que as versões mais nova.
Procure postar a linha que gera o erro,clique em depurar e a linha com erro ficará em amarelo.
Mas antes, verifique as possibilidades citadas.

[]s

 
Postado : 24/11/2017 11:06 am
Forum Jump:
  Tópico Anterior
Próximo Tópico