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
Wouldn’t it be putting a
contLinha1 = 0right after the first loop:While Range("a" & cont).Value <> ""?– Evert
Yes, thank you very much for your help... I hadn’t even noticed... sorry...
– WillGreco
@Evert put as answer for Willgreco mark as solution.
– Marco