Reset the counter every time you change line

Asked

Viewed 288 times

0

I have the following problem: I have two counters within my code, but I want every time I change to the next line the countLinha1 (second counter) is zeroed and starts counting again, remembering that there are 32 columns when he returns to the 1 of the next line would have to zero and start counting again... I’m breaking my head and nothing...

Sub Macro2()

    Application.ScreenUpdating = False

    'Linha/coluna
    coluna = 1
    cont = 2
    linha = 7
    contLinha = 7
    contLinha1 = 0
    
    codigoCampo = "0000"
    he = "02"
    sl = "000"
    sql = "00"
    
    dt = Format(Date, "DDMMYYYY")
    
    
    Sheets("Concluidos").Select
                                                    'sequencia'RD'SQL'CAM'SC
    Cells(1, 1).Value = "00000000001000000549755813888000000000000000009000000C"
    Cells(2, 1).Value = "0000000000200000054975581388800000000000000010829000007689002000189"
    Cells(3, 1).Value = "00000000003000000549755813888000000000000000203130000EMBRAER S A"
    Cells(4, 1).Value = "00000000004000000549755813888000000000000000304130000O"
    Cells(5, 1).Value = "00000000005000000549755813888000000000000000409030000" & dt
    Cells(6, 1).Value = "000000000060000005497558138880000000000000005091300000014"
    
        
    Sheets("BaseDados").Select
    
    While Range("a" & cont).Value <> ""
        coluna = 1
        
                While coluna <= 32
                    valor = Cells(cont, coluna).Value
                    If coluna = 20 And valor = "" Then
                       coluna = coluna + 1
                    Else
                    Sheets("Concluidos").Select
                        
                        
                        
                       If coluna = 1 Then
                          codigoCampo = "0902"
                          sql = "00"
                          End If
                          
                        If coluna = 2 Then
                          codigoCampo = "0422"
                          sql = "00"
                          End If
                              
                        If coluna = 3 Then
                          codigoCampo = "0418"
                          sql = "00"
                         End If
                        
                        If coluna = 4 Then
                          codigoCampo = "0195"
                          sql = "00"
                         End If
                        
                         If coluna = 5 Then
                          codigoCampo = "0197"
                          sql = "00"
                         End If
                                    
                         If coluna = 6 Then
                          codigoCampo = "0200"
                          sql = "00"
                         End If
                         
                         If coluna = 7 Then
                          codigoCampo = "0199"
                          sql = "00"
                         End If
                         
                         If coluna = 8 Then
                          codigoCampo = "0390"
                          sql = "00"
                         End If
                         
                         If coluna = 9 Then
                          codigoCampo = "0201"
                          sql = "00"
                         End If
                         
                                   
                         If coluna = 10 Then
                          codigoCampo = "0386"
                          sql = "00"
                         End If
                         
                         If coluna = 11 Then
                          codigoCampo = "0386"
                          sql = "01"
                         End If
                           
                         If coluna = 12 Then
                          codigoCampo = "0370"
                          sql = "00"
                         End If
                         
                         If coluna = 13 Then
                          codigoCampo = "0371"
                          sql = "00"
                         End If
                         
                         If coluna = 14 Then
                          codigoCampo = "0371"
                          sql = "01"
                         End If
                         
                         If coluna = 15 Then
                          codigoCampo = "0371"
                          sql = "02"
                         End If
                         
                         If coluna = 16 Then
                          codigoCampo = "0373"
                          sql = "00"
                         End If
                         
                         If coluna = 17 Then
                          codigoCampo = "0373"
                          sql = "01"
                         End If
                         
                         If coluna = 18 Then
                          codigoCampo = "0373"
                          sql = "02"
                         End If
                         
                         If coluna = 19 Then
                          codigoCampo = "0373"
                          sql = "03"
                         End If
                         
                         If coluna = 20 Then
                          codigoCampo = "0810"
                          sql = "00"
                         End If
                         
                         
                         If coluna = 21 Then
                          codigoCampo = "0911"
                          sql = "00"
                         End If
                         
                         If coluna = 22 Then
                          codigoCampo = "0911"
                          sql = "01"
                         End If
                                               
                         If coluna = 23 Then
                          codigoCampo = "0911"
                          sql = "02"
                         End If
                         
                         If coluna = 24 Then
                          codigoCampo = "0911"
                          sql = "03"
                         End If
                         
                         If coluna = 25 Then
                          codigoCampo = "0911"
                          sql = "04"
                         End If
                         
                         If coluna = 26 Then
                          codigoCampo = "0911"
                          sql = "05"
                         End If
                         
                         If coluna = 27 Then
                          codigoCampo = "0911"
                          sql = "06"
                         End If
                         
                         If coluna = 28 Then
                          codigoCampo = "0911"
                          sql = "07"
                         End If
                         
                         If coluna = 29 Then
                          codigoCampo = "0911"
                          sql = "08"
                         End If
                                
                         If coluna = 30 Then
                          codigoCampo = "0292"
                          sql = "00"
                         End If
                         
                         If coluna = 31 Then
                          codigoCampo = "0292"
                          sql = "01"
                         End If
                         
                         If coluna = 32 Then
                          codigoCampo = "0292"
                          sql = "02"
                         End If
                                  
                         Cells(linha, 1).Value = "00000000" & Format(CStr(contLinha), "###000") & "00000054975581388800000000000" & he & Format(CStr(contLinha1), "###000") & codigoCampo & sql & "00" & valor
                         
                         
                          
                         If contLinha >= 1000 Then
                            If contLinha1 >= 993 Then
                            
                            Cells(linha, 1).Value = "0000000" & Format(CStr(contLinha), "####0000") & "00000054975581388800000000000" & he & Format(CStr(contLinha1), "####0000") & codigoCampo & sql & "00" & valor
                            
                            End If
                         End If
                         
                                                
                         linha = linha + 1
                         coluna = coluna + 1
                         contLinha = contLinha + 1
                         contLinha1 = contLinha1 + 1
                         Sheets("BaseDados").Select
                         
                 End If
               Wend
         

      cont = cont + 1
    'contLinha = contLinha + 1
    'contLinha1 = contLinha1 + 1
    Wend
    
    Sheets("Concluidos").Select
    
    dt = Format(Date, "YYMMDD")
    
    varColuna = 1
    varLinha = 1
    varConteudo = 1
    Do While varConteudo <> Empty
    varLinha = varLinha + 1
    varConteudo = Cells(varLinha, varColuna).Value
    varLinha = varLinha + 1
    Loop
   
    
    Cells(64461, 1).Value = "0000000" & Format(CStr(contLinha), "#####00000") & "000000549755813888999999999999900009080000CADASTRONIS.D" & dt & ".S01"
    Cells(64462, 1).Value = "0000000" & Format(CStr(contLinha + 1), "#####00000") & "0000005497558138889999999999999001091200000000000" & CStr(varLinha - 1)
    
    
    
    
    Sheets("Concluidos").Select
    
    
    template_file = ActiveWorkbook.FullName
 
    fileSaveName = Application.GetSaveAsFilename( _
    InitialFileName:="CADASTRONIS" + _
    VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
    fileFilter:="Text Files (*.txt), *.txt")
     
    If fileSaveName = False Then
    Exit Sub
    End If
     
       
    Dim newBook As Workbook
    Dim plan As Worksheet
    Set newBook = Workbooks.Add
       
     
    ThisWorkbook.ActiveSheet.Copy Before:=newBook.Sheets(1)
     
       
    For Each plan In newBook.Sheets
    If plan.Name <> ActiveSheet.Name Then
    newBook.Worksheets(plan.Index).Delete
    End If
    Next
     
    
    newBook.SaveAs Filename:= _
    fileSaveName, FileFormat:=xlTextWindows, _
    CreateBackup:=False
     
    
    newBook.Close SaveChanges:=True
    Set newBook = Nothing
     
    MsgBox "O arquivo foi exportado com sucesso! ", vbInformation, "Exportar arquivos"
        
    Application.ScreenUpdating = True
    
End Sub

  • 1

    Wouldn’t it be putting a contLinha1 = 0 right after the first loop: While Range("a" & cont).Value <> ""?

  • Yes, thank you very much for your help... I hadn’t even noticed... sorry...

  • 1

    @Evert put as answer for Willgreco mark as solution.

1 answer

1


To reset the counter put the code contLinha1 = 0 right after the first loop:

[...]

While Range("a" & cont).Value <> ""

    ' Aqui!
    contLinha1 = 0

    coluna = 1

            While coluna <= 32
                valor = Cells(cont, coluna).Value
                If coluna = 20 And valor = "" Then
                   coluna = coluna + 1
                Else
                Sheets("Concluidos").Select

[...]

Browser other questions tagged

You are not signed in. Login or sign up in order to post.