Code Required Please

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,783
Office Version
  1. 365
Platform
  1. Windows
I need a code that will firstly look at column A and column B. If the data in those are the same then look at the very first start year in column C and the very last year in column D and insert a row for the missing years. I hope the table explains better.



<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 99px"><COL style="WIDTH: 129px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left">Make</TD><TD style="TEXT-ALIGN: left">Model</TD><TD style="TEXT-ALIGN: left">SY</TD><TD style="TEXT-ALIGN: left">EY</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1969</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1969</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1969</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1969</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD><TD style="TEXT-ALIGN: left">1973</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD><TD style="TEXT-ALIGN: left">1975</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1973</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1983</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1983</TD></TR></TBODY></TABLE>

As you can see in the example all the data in each row in column A & B are the same. It needs to look at the lowest year in all the rows in column C which is 1963 and the latest year in column D which is 1983 and then make sure there is an entry for every year in between these 2 years. Then when the data changes in column A & B do the same again. Then if possible delete column D at the very end of the code as it is not needed So this is what it will look after. I have coloured what the code needs to add.

The cells not coloured were the ones that were already in column C!

I hope this makes sense! Thanks.


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 99px"><COL style="WIDTH: 129px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left">Make</TD><TD style="TEXT-ALIGN: left">Model</TD><TD style="TEXT-ALIGN: left">SY</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1964</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1965</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1968</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1969</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1971</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1972</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1973</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1974</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1975</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1977</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1978</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1980</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1981</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1983</TD></TR></TBODY></TABLE>
 
Last edited:
Sort by date Sort by votes
This is the before


Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 112px"><COL style="WIDTH: 118px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Make</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Model</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Engine cc</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">SY</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">EY</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">FuelType</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">CatCode</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">SysCode</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Asia Motors</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Rocsta</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1.8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1994</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1997</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Petrol</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AAROC18 001</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AAROC_18A</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Asia Motors</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Rocsta</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">2.2D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1994</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1997</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Diesel</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AAROC22D 001</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AAROC_22D</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Aixam</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Aixam</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0.4D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">2000</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">2003</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Diesel</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AM40004D 001</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AM400_04D</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Aixam</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Aixam</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0.5D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1999</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">2003</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Diesel</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AM50005D 001</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AM500_05D</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Austin</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Allegro</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1.1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1973</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">1981</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Petrol</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">ANALL11 001</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">ANALL_11A</TD></TR></TBODY></TABLE>

<A style="BACKGROUND-COLOR: #800040; FONT-FAMILY: Arial; COLOR: #fcf507; FONT-SIZE: 9pt; FONT-WEIGHT: bold" p href<>This is the after
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 77px"><COL style="WIDTH: 48px"><COL style="WIDTH: 65px"><COL style="WIDTH: 35px"><COL style="WIDTH: 60px"><COL style="WIDTH: 102px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Make</TD><TD>Model</TD><TD style="TEXT-ALIGN: center">Engine cc</TD><TD>SY</TD><TD style="TEXT-ALIGN: center">FuelType</TD><TD>CatCode</TD><TD>SysCode</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD style="TEXT-ALIGN: right">1.8</TD><TD style="TEXT-ALIGN: right">1994</TD><TD>Petrol</TD><TD>AAROC18 001</TD><TD>AAROC_18A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD style="TEXT-ALIGN: right">1.8</TD><TD style="TEXT-ALIGN: right">1995</TD><TD>Petrol</TD><TD>AAROC18 001</TD><TD>AAROC_18A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD style="TEXT-ALIGN: right">1.8</TD><TD style="TEXT-ALIGN: right">1996</TD><TD>Petrol</TD><TD>AAROC18 001</TD><TD>AAROC_18A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD style="TEXT-ALIGN: right">1.8</TD><TD style="TEXT-ALIGN: right">1997</TD><TD>Petrol</TD><TD>AAROC18 001</TD><TD>AAROC_18A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD>2.2D</TD><TD style="TEXT-ALIGN: right">1994</TD><TD>Diesel</TD><TD>AAROC22D 001</TD><TD>AAROC_22D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD>2.2D</TD><TD style="TEXT-ALIGN: right">1995</TD><TD>Diesel</TD><TD>AAROC22D 001</TD><TD>AAROC_22D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD>2.2D</TD><TD style="TEXT-ALIGN: right">1996</TD><TD>Diesel</TD><TD>AAROC22D 001</TD><TD>AAROC_22D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Asia Motors</TD><TD>Rocsta</TD><TD>2.2D</TD><TD style="TEXT-ALIGN: right">1997</TD><TD>Diesel</TD><TD>AAROC22D 001</TD><TD>AAROC_22D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.4D</TD><TD style="TEXT-ALIGN: right">2000</TD><TD>Diesel</TD><TD>AM40004D 001</TD><TD>AM400_04D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.4D</TD><TD style="TEXT-ALIGN: right">2001</TD><TD>Diesel</TD><TD>AM40004D 001</TD><TD>AM400_04D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.4D</TD><TD style="TEXT-ALIGN: right">2002</TD><TD>Diesel</TD><TD>AM40004D 001</TD><TD>AM400_04D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.4D</TD><TD style="TEXT-ALIGN: right">2003</TD><TD>Diesel</TD><TD>AM40004D 001</TD><TD>AM400_04D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.5D</TD><TD style="TEXT-ALIGN: right">1999</TD><TD>Diesel</TD><TD>AM50005D 001</TD><TD>AM500_05D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.5D</TD><TD style="TEXT-ALIGN: right">2000</TD><TD>Diesel</TD><TD>AM50005D 001</TD><TD>AM500_05D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.5D</TD><TD style="TEXT-ALIGN: right">2001</TD><TD>Diesel</TD><TD>AM50005D 001</TD><TD>AM500_05D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.5D</TD><TD style="TEXT-ALIGN: right">2002</TD><TD>Diesel</TD><TD>AM50005D 001</TD><TD>AM500_05D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>Aixam</TD><TD>Aixam</TD><TD>0.5D</TD><TD style="TEXT-ALIGN: right">2003</TD><TD>Diesel</TD><TD>AM50005D 001</TD><TD>AM500_05D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1973</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1974</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1975</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1976</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1977</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1978</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1979</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1980</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD>Austin</TD><TD>Allegro</TD><TD style="TEXT-ALIGN: right">1.1</TD><TD style="TEXT-ALIGN: right">1981</TD><TD>Petrol</TD><TD>ANALL11 001</TD><TD>ANALL_11A</TD></TR></TBODY></TABLE>

Thanks a lot.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try this :-
Results start "J1".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG25Jan31
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ray
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
c = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Range("J1").Resize(, 7) = Application.Index(Rng(1).Offset(-1).Resize(, 8), 1, Array(1, 2, 3, 4, 6, 7, 8))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = Dn.Offset(, 3) To Dn.Offset(, 4)
        c = c + 1
        ray = Application.Index(Dn.Resize(, 8), 1, Array(1, 2, 3, 4, 6, 7, 8))
        ray(4) = n
        Range("J" & c).Resize(, 7) = ray
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick, it seems to go a certain way down the file then stops and comes up with an error - type mismatch?
 
Upvote 0
Have you any idea what line of DATA it fails on ???
If work OK on your example.
After row 1, You should only have Years (1997 etc) in columns "SY" & "EY", else ther will be a mismatch error.
 
Last edited:
Upvote 0
I think I know what the problem is, watch this space!
 
Upvote 0
Try this:-
Your problem was that you Had Blank rows in col "EY" and some of those had spaces in them.
The code now assumes that if there is a Blank or space in a row in "EY" then that is the same value as Column "SY."
Just thouight I could Just have Trimmed the values, anyway see how it goes !!!!
Code: 
[COLOR=navy]Sub[/COLOR] MG27Jan25
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ray
[COLOR=navy]Dim[/COLOR] Dup
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
c = 1
[COLOR=navy]Dim[/COLOR] t
t = Timer
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Range("J1").Resize(, 7) = Application.Index(Rng(1).Offset(-1).Resize(, 8), 1, Array(1, 2, 3, 4, 6, 7, 8))
Application.ScreenUpdating = False
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
 
    [COLOR=navy]For[/COLOR] n = Dn.Offset(, 3) To IIf(Dn.Offset(, 4) = vbNullString Or Dn.Offset(, 4) = " ", Dn.Offset(, 3), Dn.Offset(, 4))
        c = c + 1
        Ray = Application.Index(Dn.Resize(, 8), 1, Array(1, 2, 3, 4, 6, 7, 8))
        Ray(4) = n
        Range("J" & c).Resize(, 7) = Ray
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Dn
Application.ScreenUpdating = True
 MsgBox Timer - t
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
Sorry for the trouble Mick but I need it tweaked a little. If the vehicle has no end year e.g it starts 2010 I need a row for 2012 also.

so it would be

2010
2011
2012

Thanks.
 
Upvote 0
I think I must have had a bit of a moment. After a little thought all I had to do was put 2012 as an end year and it worked!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
571
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
645
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
688
John_w
J

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

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