Searching and saving rows with 2 counters on opposite directions

F

FranciscoF

New Member
Joined
Jan 23, 2011
Messages
3
Hello!

My name is Francisco and I recently started programming in VBA. I have some knowledge in C and C++, but do an application to work on Excel sheets is posing a different and difficult new challenge to me.

I’ve encountered much important information on your site and I want to thank all participants their posted knowledge that has helped me very much.

I'm doing a macro to read a long Excel sheet and identify values on TpCtrl column, starting my search on the most recent date. When a row is found, the entire row is copied to another sheet, creating is this new sheet to new columns on the right of the copied registry.
I still have to make an if cycle to prevent repeating rows, but for now the error seems to be on the first If condition.
This is my code:
Sub avalia_skip_v3()

Dim i As Integer
Dim j As Integer
Dim data_S0 As Date
Dim Row_inv As Integer
Dim Column_size As Long
Dim N1 As ListColumn
Dim N2 As ListColumn
Dim Row As Long
Dim Regra_Skip As Worksheet
Dim material As Double
Dim fornecedor As Double

Set Regra_Skip = Sheets.Add

j = 2

i = 2

'To know how long is the list:

Column_size = Sheets("Sheet1").Range("A:A")

' Cycle starting on the most recent date, on the bottom of the list, identifying the code 0101 on "TpCtrl." column

For Row = Column_size To i

If Cells(Row, "TpCtrl.").Value = "0101" Then

ActiveCell.RowRange.Select
Selection.Copy
Sheets("Regra_Skip").Activate
Sheets("Regra_Skip").Range.Row(j).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Clear

' creates a new column in the new sheet, next to the copied row, to make de time diference beetwen now and the column

Sheets("Regra_Skip").ListColumns.Add = N1
N1.Name = "Data desde S0"


Cells(j, "Data desde S0") = DateDiff("ym", Now, data fim)

' creates a new column in new sheet Regra_Skip, next to the previous column created, that will display 01 counter

Sheets("Regra_Skip").ListColumns.Add = N2
N2.Name = "Nº Series Normal"


' saves material e fornecedor columns for that specific row to compare with the new search and avoid repeting items.

material = Cells(Row, "Material")
fornecedor = Cells(Row, "Fornecedor")


' starts a counter from the date found for value 0101, going to the recent dates direction, counting value 01 for the 2 saved columns

For Row_inv = Row To Column_size

If Cells(Row_inv, "Material") = material And Cells(Row_inv, "Fornecedor") = fornecedor Then
If Not Cells(Row_inv, "Code DU") = “9” Then
Sheets("Regra_Skip").Cells(j, "Nº Series Normal").Value = Cells(j, "Nº Series Normal").Value + 1
End If
End If
j = j + 1
Next Row_inv
End If

Next Row

End Sub

This code is to be applied on the following excel sheet:


<table border="0" cellpadding="0" cellspacing="0" width="1181"><col width="106"><col width="99"><col width="338"><col width="190"><col width="33"><col width="72"><col width="45"><col width="59"><col width="89"><col width="79"><col width="71"><tbody><tr height="17"> <td class="xl65" style="height: 12.75pt; width: 80pt;" height="17" width="106">LotCtrl</td> <td class="xl65" style="width: 74pt;" width="99">Material</td> <td class="xl66" style="width: 254pt;" width="338">Texto breve do objeto de controle</td> <td class="xl66" style="width: 143pt;" width="190">Status do sistema</td> <td class="xl66" style="width: 25pt;" width="33">Skip</td> <td class="xl66" style="width: 54pt;" width="72">Fornecedor</td> <td class="xl66" style="width: 34pt;" width="45">TpCtrl.</td> <td class="xl66" style="width: 44pt;" width="59">Code DU</td> <td class="xl67" style="width: 67pt;" width="89">Qtd.lote</td> <td class="xl65" style="width: 59pt;" width="79">DtaInício</td> <td class="xl67" style="width: 53pt;" width="71">Data fim</td> </tr></tbody></table>
<table style="width: 1181px; height: 2016px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 80pt;" width="106"> <col style="width: 74pt;" width="99"> <col style="width: 254pt;" width="338"> <col style="width: 143pt;" width="190"> <col style="width: 25pt;" width="33"> <col style="width: 54pt;" width="72"> <col style="width: 34pt;" width="45"> <col style="width: 44pt;" width="59"> <col style="width: 67pt;" width="89"> <col style="width: 59pt;" width="79"> <col style="width: 53pt;" width="71"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 80pt;" height="17" width="106">010400826432</td> <td class="xl65" style="width: 74pt;" width="99">6-720-500-555</td> <td class="xl66" style="width: 254pt;" width="338">CAIXA DE CARTÃO P/ÁUT.DE GÁS</td> <td class="xl66" style="width: 143pt;" width="190">DU CNTC ESTD COIM ESAT</td> <td class="xl66" style="width: 25pt;" width="33">
</td> <td class="xl66" style="width: 54pt;" width="72">657593</td> <td class="xl66" style="width: 34pt;" width="45">0101</td> <td class="xl66" style="width: 44pt;" width="59">5</td> <td class="xl67" style="width: 67pt;" align="right" width="89">250,000</td> <td class="xl68" style="width: 59pt;" width="79">16-11-2010</td> <td class="xl69" style="width: 53pt;" align="right" width="71">16-11-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400840444</td> <td class="xl65">6-720-500-555</td> <td class="xl66">CAIXA DE CARTÃO P/ÁUT.DE GÁS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">657593</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">3,000</td> <td class="xl68">14-12-2010</td> <td class="xl69" align="right">14-12-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400813987</td> <td class="xl65">6-720-500-633</td> <td class="xl66">CAIXA CARTAO P/CAM.COMB. W325,400</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">657593</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">21-10-2010</td> <td class="xl69" align="right">21-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400814868</td> <td class="xl65">6-720-500-633</td> <td class="xl66">CAIXA CARTAO P/CAM.COMB. W325,400</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">657593</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">3,000</td> <td class="xl68">22-10-2010</td> <td class="xl69" align="right">22-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400814216</td> <td class="xl65">6-720-500-838</td> <td class="xl66">CAIXA DE CARTÃO</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">657593</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">255,000</td> <td class="xl68">21-10-2010</td> <td class="xl69" align="right">21-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400814865</td> <td class="xl65">6-720-500-838</td> <td class="xl66">CAIXA DE CARTÃO</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">657593</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">3,000</td> <td class="xl68">22-10-2010</td> <td class="xl69" align="right">22-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400800403</td> <td class="xl65">6-720-600-895</td> <td class="xl66">IM SH27/20 Sperrschalter 1998/05 de</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">900,000</td> <td class="xl68">22-09-2010</td> <td class="xl69" align="right">22-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400763536</td> <td class="xl65">6-720-602-618</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">100,000</td> <td class="xl68">01-07-2010</td> <td class="xl69" align="right">01-07-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400755151</td> <td class="xl65">6-720-605-095</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC CCES COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">16-06-2010</td> <td class="xl69" align="right">16-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400753803</td> <td class="xl65">6-720-605-097</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC CCES COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">14-06-2010</td> <td class="xl69" align="right">14-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400809930</td> <td class="xl65">6-720-605-435</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">13-10-2010</td> <td class="xl69" align="right">13-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400794849</td> <td class="xl65">6-720-605-455</td> <td class="xl66">IM gas convers.WR-1/4/5/8KB/G 1997/11 it</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">10-09-2010</td> <td class="xl69" align="right">10-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400762000</td> <td class="xl65">6-720-605-934</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">29-06-2010</td> <td class="xl69" align="right">29-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400763346</td> <td class="xl65">6-720-605-934</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">01-07-2010</td> <td class="xl69" align="right">01-07-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400811359</td> <td class="xl65">6-720-606-024</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">15-10-2010</td> <td class="xl69" align="right">15-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400825051</td> <td class="xl65">6-720-606-218</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">12-11-2010</td> <td class="xl69" align="right">12-11-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400848189</td> <td class="xl65">6-720-606-255</td> <td class="xl66">IM Fan GZT1/KE-KME 2009/03 pt</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">05-01-2011</td> <td class="xl69" align="right">05-01-2011</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400788124</td> <td class="xl65">6-720-606-507</td> <td class="xl66">IM heater convers.B/G 2001/09 en/pt/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">25-08-2010</td> <td class="xl69" align="right">25-08-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400762002</td> <td class="xl65">6-720-606-672</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">160,000</td> <td class="xl68">29-06-2010</td> <td class="xl69" align="right">29-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400762662</td> <td class="xl65">6-720-606-672</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">30-06-2010</td> <td class="xl69" align="right">30-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400762643</td> <td class="xl65">6-720-606-673</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">30-06-2010</td> <td class="xl69" align="right">30-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400762759</td> <td class="xl65">6-720-606-673</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">312,000</td> <td class="xl68">30-06-2010</td> <td class="xl69" align="right">30-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400834017</td> <td class="xl65">6-720-606-777</td> <td class="xl66">IM gas convers. WB/WRB/WRG 2002/05 pt</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">30-11-2010</td> <td class="xl69" align="right">30-11-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400755152</td> <td class="xl65">6-720-606-808</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">16-06-2010</td> <td class="xl69" align="right">16-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400807840</td> <td class="xl65">6-720-606-893</td> <td class="xl66">AD WP/WRP/WE/WRE11/14/18 2003/02 div</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">08-10-2010</td> <td class="xl69" align="right">08-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400808751</td> <td class="xl65">6-720-606-897</td> <td class="xl66">AD WRB/WRG11/14/18 2003/02 div</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">244,000</td> <td class="xl68">11-10-2010</td> <td class="xl69" align="right">11-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400794850</td> <td class="xl65">6-720-606-899</td> <td class="xl66">IM gas conver. WR11/14/18 B/G 2002/12 hu</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">10-09-2010</td> <td class="xl69" align="right">10-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400796715</td> <td class="xl65">6-720-606-949</td> <td class="xl66">IM gas conver. W11/14-2 P 2010/01 es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">15-09-2010</td> <td class="xl69" align="right">15-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400808763</td> <td class="xl65">6-720-606-987</td> <td class="xl66">IO WT/WTDAME 2010/01 div</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">300,000</td> <td class="xl68">11-10-2010</td> <td class="xl69" align="right">11-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400749972</td> <td class="xl65">6-720-607-103</td> <td class="xl66">IM WTD11/14/17 2004/06 it</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">02-06-2010</td> <td class="xl69" align="right">02-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400790857</td> <td class="xl65">6-720-607-103</td> <td class="xl66">IM WTD11/14/17 2010/08 it</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">261,000</td> <td class="xl68">02-09-2010</td> <td class="xl69" align="right">02-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400801535</td> <td class="xl65">6-720-607-105</td> <td class="xl66">IM gas conv. WT/WTD14AM1 2010/08 es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">24-09-2010</td> <td class="xl69" align="right">24-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400810631</td> <td class="xl65">6-720-607-105</td> <td class="xl66">IM gas conv. WT/WTD14AM1 2010/08 es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">779,000</td> <td class="xl68">14-10-2010</td> <td class="xl69" align="right">14-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400793886</td> <td class="xl65">6-720-607-106</td> <td class="xl66">IM WT11/14 2010/08 pt</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">09-09-2010</td> <td class="xl69" align="right">09-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400793887</td> <td class="xl65">6-720-607-106</td> <td class="xl66">IM WT11/14 2010/08 pt</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">9,000</td> <td class="xl68">09-09-2010</td> <td class="xl69" align="right">09-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400762639</td> <td class="xl65">6-720-607-113</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">30-06-2010</td> <td class="xl69" align="right">30-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400775863</td> <td class="xl65">6-720-607-277</td> <td class="xl66">IM Nr.1010 2004/03 it</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">26-07-2010</td> <td class="xl69" align="right">26-07-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400793888</td> <td class="xl65">6-720-607-402</td> <td class="xl66">IM gas conver.WT10/13 2010/08 fr</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">200,000</td> <td class="xl68">09-09-2010</td> <td class="xl69" align="right">09-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400763047</td> <td class="xl65">6-720-607-725</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">30-06-2010</td> <td class="xl69" align="right">30-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400751326</td> <td class="xl65">6-720-607-728</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">07-06-2010</td> <td class="xl69" align="right">07-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400764306</td> <td class="xl65">6-720-607-728</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">02-07-2010</td> <td class="xl69" align="right">02-07-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400790409</td> <td class="xl65">6-720-607-734</td> <td class="xl66">IM gas conv. F2500/330 2009/07 nl</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">01-09-2010</td> <td class="xl69" align="right">01-09-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400765168</td> <td class="xl65">6-720-607-735</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">05-07-2010</td> <td class="xl69" align="right">05-07-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400763048</td> <td class="xl65">6-720-607-863</td> <td class="xl66">IMPRESSOS</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">0101</td> <td class="xl66">1</td> <td class="xl67" align="right">2,000</td> <td class="xl68">30-06-2010</td> <td class="xl69" align="right">30-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400757764</td> <td class="xl65">6-720-607-984</td> <td class="xl66">IM LC11/14/17 PV 2006/02 fr</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">272,000</td> <td class="xl68">21-06-2010</td> <td class="xl69" align="right">21-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400756587</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">30,000</td> <td class="xl68">18-06-2010</td> <td class="xl69" align="right">18-06-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400782205</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">30,000</td> <td class="xl68">09-08-2010</td> <td class="xl69" align="right">09-08-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400817505</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">30,000</td> <td class="xl68">28-10-2010</td> <td class="xl69" align="right">28-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400817506</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">19,000</td> <td class="xl68">28-10-2010</td> <td class="xl69" align="right">28-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400830320</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">30,000</td> <td class="xl68">23-11-2010</td> <td class="xl69" align="right">23-11-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400844984</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">30,000</td> <td class="xl68">22-12-2010</td> <td class="xl69" align="right">22-12-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400849901</td> <td class="xl65">6-720-608-018</td> <td class="xl66">SD WR11P en/fr/es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">X</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">90,000</td> <td class="xl68">08-01-2011</td> <td class="xl69" align="right">08-01-2011</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400832460</td> <td class="xl65">6-720-608-063</td> <td class="xl66">IM WRD11/14/17-2KME 23/31 2006/05 pt</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">26-11-2010</td> <td class="xl69" align="right">26-11-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400814807</td> <td class="xl65">6-720-608-092</td> <td class="xl66">IM WR16G 2006/05 es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">22-10-2010</td> <td class="xl69" align="right">22-10-2010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400847798</td> <td class="xl65">6-720-608-092</td> <td class="xl66">IM WR16G 2006/05 es</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">04-01-2011</td> <td class="xl69" align="right">04-01-2011</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">010400759817</td> <td class="xl65">6-720-608-093</td> <td class="xl66">IM WRD11/14/17-2KME23 2006/05 pt</td> <td class="xl66">DU CNTC ESTD COIM ESAT</td> <td class="xl66">
</td> <td class="xl66">658644</td> <td class="xl66">01</td> <td class="xl66">1</td> <td class="xl67" align="right">250,000</td> <td class="xl68">24-06-2010</td> <td class="xl69" align="right">24-06-2010</td> </tr> </tbody></table>

Feel free to ask me for any doubt that arises from analyzing my code.

Cheers from Portugal to everyone.
 
Sort by date Sort by votes
Hello. I think that you need something like

Code: 
'To know how long is the list:

Column_size = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

' Cycle starting on the most recent date, on the bottom of the list, identifying the code 0101 on "TpCtrl." column

For Row = Column_size To i

If Cells(Row, "G").Value = "0101" Then ' I think G, anyway iot should be the column letter, not your heading

Rows(Row).Copy Destination:=Sheets("Regra_Skip").Range("A" & Rows.Count).End(xlUp).Offset(1)
 
Upvote 0
Hi VoG!

Thank you for your help.

The list I'm analyzing can have the "TpCtrl." in different columns, that's why I was trying to call it by the name. Is it possible in VBA?

When I tried to run the code, it created the new sheet, counted the list size according to VoG code but ends there.

Can it be that my counter in the For loop, starting on the end of the list and counting backwards is the problem?
Should I stay with the "next row" on the bottom of the code?

I think the 1st If loop is well written according with VoG help, but it doesn't copy any rows to the new sheet.

Hope you can help me!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
G
  • Question Question
copy/paster to another worksheet
Replies
5
Views
798
Fluff
Fluff
A
  • Question Question
Sum Multiple Columns Index Match Matrix
Replies
1
Views
2K
DRSteele
DRSteele
G
  • Question Question
Help
Replies
3
Views
652
JLGWhiz
JLGWhiz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top