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
Hi MickG. Now we got this boxed off would it be possible to add another column with engine size. So it will look at A , B & C as the range. For example looking at the above table it will look at Ford Cortina then 1.6 in C and the years in D & E, and do as it does now then it changes again as it may have Ford Cortina 2.0 etc etc. Thanks.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG22Oct25
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Twn     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] ac      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] tot     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            Twn = Dn & Dn(, 2) & Dn(, 3)
                [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
                    .Add Twn, Array(Dn, Dn(, 2), Dn(, 3), Dn(, 4), Dn(, 5))
                [COLOR="Navy"]Else[/COLOR]
                    Q = .Item(Twn)
                        Q(3) = Application.Min(Q(3), Dn(, 4))
                        Q(4) = Application.max(Q(4), Dn(, 5))
                    .Item(Twn) = Q
                [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    tot = tot + (.Item(K)(4) - .Item(K)(3))
[COLOR="Navy"]Next[/COLOR] K
ReDim ray(1 To tot, 1 To 4)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
      [COLOR="Navy"]For[/COLOR] ac = .Item(K)(3) To .Item(K)(4) - 1
            c = c + 1
            ray(c, 1) = .Item(K)(0)
            ray(c, 2) = .Item(K)(1)
            ray(c, 3) = .Item(K)(2)
            ray(c, 4) = ac
        [COLOR="Navy"]Next[/COLOR] ac
    [COLOR="Navy"]Next[/COLOR] K
Range("F1").Resize(tot, 4) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick but there is a run time error 9 pointing at this line

ray(c, 1) = .Item(K)(0)
 
Upvote 0
This is the data (Plus Results Col "F") I tested it on, If yours is different please supply an example.
Code: 
[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B)  [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(E) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(F) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(G)  [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(H) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(I) [/B][/COLOR]
1.      Make    Model            SY      EY      Ford    Cortina  1.6     1963   
2.      Ford    Cortina  1.6     1973    1966    Ford    Cortina  1.6     1964   
3.      Ford    Cortina  1.6     1963    1966    Ford    Cortina  1.6     1965   
4.      Ford    Cortina  1.6     1963    1966    Ford    Cortina  1.6     1966   
5.      Ford    Cortina  1.6     1963    1966    Ford    Cortina  1.6     1967   
6.      Ford    Cortina  1.6     1967    1969    Ford    Cortina  1.6     1968   
7.      Ford    Cortina  1.6     1967    1969    Ford    Cortina  1.6     1969   
8.      Ford    Cortina  1.6     1969    1970    Ford    Cortina  1.6     1970   
9.      Ford    Cortina  1.6     1969    1970    Ford    Cortina  1.6     1971   
10.     Ford    Cortina  1.6     1967    1970    Ford    Cortina  1.6     1972   
11.     Ford    Cortina  1.6     1967    1970    Ford    Cortina  1.6     1973   
12.     Ford    Cortina  1.6     1970    1973    Ford    Cortina  1.6     1974   
13.     Ford    Cortina  1.6     1970    1975    Ford    Cortina  1.6     1975   
14.     Ford    Cortina  1.6     1976    1979    Ford    Cortina  1.6     1976   
15.     Ford    Cortina  1.6     1976    1979    Ford    Cortina  1.6     1977   
16.     Ford    Cortina  1.6     1976    1979    Ford    Cortina  1.6     1978   
17.     Ford    Cortina  2       1976    1979    Ford    Cortina  2       1970   
18.     Ford    Cortina  2       1970    1982    Ford    Cortina  2       1971   
19.     Ford    Cortina  2       1979    1982    Ford    Cortina  2       1972   
20.     Ford    Cortina  2       1973    1982    Ford    Cortina  2       1973   
21.     Ford    Cortina  2       1979    1982    Ford    Cortina  2       1974   
22.     Ford    Cortina  2       1979    1983    Ford    Cortina  2       1975   
23.     Ford    Cortina  2       1979    1983    Ford    Cortina  2       1976   
24.     moggy   minor    1000    1950    1965    Ford    Cortina  2       1977   
25.     moggy   minor    1000    1951    1966    Ford    Cortina  2       1978   
26.     moggy   minor    1000    1952    1967    Ford    Cortina  2       1979   
27.     moggy   minor    1000    1953    1968    Ford    Cortina  2       1980   
28.     moggy   minor    1000    1954    1969    Ford    Cortina  2       1981   
29.     moggy   minor    1000    1955    1970    Ford    Cortina  2       1982   
30.     moggy   minor    1000    1956    1971    moggy   minor    1000    1950   
31.     moggy   minor    1000    1957    1972    moggy   minor    1000    1951   
32.     moggy   minor    1000    1958    1973    moggy   minor    1000    1952   
33.     moggy   minor    1000    1959    1974    moggy   minor    1000    1953   
34.     moggy   minor    1000    1960    1975    moggy   minor    1000    1954   
35.     moggy   minor    1000    1961    1976    moggy   minor    1000    1955   
36.     moggy   minor    850     1962    1977    moggy   minor    1000    1956   
37.     moggy   minor    850     1963    1978    moggy   minor    1000    1957   
38.     moggy   minor    850     1964    1979    moggy   minor    1000    1958   
39.     moggy   minor    850     1965    1980    moggy   minor    1000    1959   
40.     moggy   minor    850     1966    1981    moggy   minor    1000    1960   
41.     Ford    consul   1.7     1966    1970    moggy   minor    1000    1961   
42.     Ford    consul   1.7     1967    1971    moggy   minor    1000    1962   
43.     Ford    consul   1.7     1968    1972    moggy   minor    1000    1963   
44.     Ford    consul   1.7     1969    1973    moggy   minor    1000    1964   
45.     Ford    consul   2       1970    1974    moggy   minor    1000    1965   
46.     Ford    consul   2       1971    1975    moggy   minor    1000    1966   
47.                                              moggy   minor    1000    1967   
48.                                              moggy   minor    1000    1968   
49.                                              moggy   minor    1000    1969   
50.                                              moggy   minor    1000    1970   
51.                                              moggy   minor    1000    1971   
52.                                              moggy   minor    1000    1972   
53.                                              moggy   minor    1000    1973   
54.                                              moggy   minor    1000    1974   
55.                                              moggy   minor    1000    1975   
56.                                              moggy   minor    850     1962   
57.                                              moggy   minor    850     1963   
58.                                              moggy   minor    850     1964   
59.                                              moggy   minor    850     1965   
60.                                              moggy   minor    850     1966   
61.                                              moggy   minor    850     1967   
62.                                              moggy   minor    850     1968   
63.                                              moggy   minor    850     1969   
64.                                              moggy   minor    850     1970   
65.                                              moggy   minor    850     1971   
66.                                              moggy   minor    850     1972   
67.                                              moggy   minor    850     1973   
68.                                              moggy   minor    850     1974   
69.                                              moggy   minor    850     1975   
70.                                              moggy   minor    850     1976   
71.                                              moggy   minor    850     1977   
72.                                              moggy   minor    850     1978   
73.                                              moggy   minor    850     1979   
74.                                              moggy   minor    850     1980   
75.                                              Ford    consul   1.7     1966   
76.                                              Ford    consul   1.7     1967   
77.                                              Ford    consul   1.7     1968   
78.                                              Ford    consul   1.7     1969   
79.                                              Ford    consul   1.7     1970   
80.                                              Ford    consul   1.7     1971   
81.                                              Ford    consul   1.7     1972   
82.                                              Ford    consul   2       1970   
83.                                              Ford    consul   2       1971   
84.                                              Ford    consul   2       1972   
85.                                              Ford    consul   2       1973   
86.                                              Ford    consul   2       1974   
87.                                                                              
88.
Regards Mick
 
Upvote 0
It looks the same as mine. I dont know if it makes a difference but some engines will have a D on the end i.e 1.6D, 2.0D or maybe 600D, 0.6D
 
Upvote 0
I'm sorry In did not get back to you , I'm having slight problems with my Broadband connection.
I'm pleased you seem to have fixed the problem What was it's cause??
 
Upvote 0
Don't know! I think I add some data in a row that was not in the same format as the rest.
 
Upvote 0
Hi Mick I need some further help with this please. I need the latest code but a few adjustments. Basically the new code needs to add what is in columns F & G and the output starting in column I say.

The table below may help as to what I need. 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: 65px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 60px"><COL style="WIDTH: 90px"></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><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">1958</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.2</TD><TD style="TEXT-ALIGN: right">1963</TD><TD style="TEXT-ALIGN: right">1966</TD><TD>Petrol</TD><TD>FDCOR_12A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1959</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.3</TD><TD style="TEXT-ALIGN: right">1967</TD><TD style="TEXT-ALIGN: right">1969</TD><TD>Petrol</TD><TD>FDCOR_13A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1960</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.3</TD><TD style="TEXT-ALIGN: right">1969</TD><TD style="TEXT-ALIGN: right">1970</TD><TD>Petrol</TD><TD>FDCOR_13A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1961</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.3</TD><TD style="TEXT-ALIGN: right">1976</TD><TD style="TEXT-ALIGN: right">1979</TD><TD>Petrol</TD><TD>FDCOR_13A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1962</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.3</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">1982</TD><TD>Petrol</TD><TD>FDCOR_13A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1963</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.3</TD><TD style="TEXT-ALIGN: right">1979</TD><TD style="TEXT-ALIGN: right">1982</TD><TD>Petrol</TD><TD>FDCOR_13A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1964</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.5</TD><TD style="TEXT-ALIGN: right">1963</TD><TD style="TEXT-ALIGN: right">1966</TD><TD>Petrol</TD><TD>FDCOR_15A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1965</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.5</TD><TD style="TEXT-ALIGN: right">1963</TD><TD style="TEXT-ALIGN: right">1966</TD><TD>Petrol</TD><TD>FDCOR_15A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1966</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1963</TD><TD style="TEXT-ALIGN: right">1966</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1967</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1967</TD><TD style="TEXT-ALIGN: right">1969</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1968</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1969</TD><TD style="TEXT-ALIGN: right">1970</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1969</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1967</TD><TD style="TEXT-ALIGN: right">1970</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1970</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1967</TD><TD style="TEXT-ALIGN: right">1970</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1971</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">1973</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1972</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1976</TD><TD style="TEXT-ALIGN: right">1979</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1973</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1973</TD><TD style="TEXT-ALIGN: right">1982</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1974</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD style="TEXT-ALIGN: right">1979</TD><TD style="TEXT-ALIGN: right">1983</TD><TD>Petrol</TD><TD>FDCOR_16A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1975</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">1975</TD><TD>Petrol</TD><TD>FDCOR_20A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1976</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1976</TD><TD style="TEXT-ALIGN: right">1979</TD><TD>Petrol</TD><TD>FDCOR_20A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1977</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1979</TD><TD style="TEXT-ALIGN: right">1982</TD><TD>Petrol</TD><TD>FDCOR_20A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1978</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">2.3</TD><TD style="TEXT-ALIGN: right">1976</TD><TD style="TEXT-ALIGN: right">1979</TD><TD>Petrol</TD><TD>FDCOR_23A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1979</TD><TD>Ford</TD><TD>Cortina</TD><TD style="TEXT-ALIGN: right">2.3</TD><TD style="TEXT-ALIGN: right">1979</TD><TD style="TEXT-ALIGN: right">1983</TD><TD>Petrol</TD><TD>FDCOR_23A</TD></TR></TBODY></TABLE>
 
Upvote 0
Try this:-
Results start "H2".
Input column in InputBox for destination of columns 6 and 7 (combined).
Code: 
[COLOR="Navy"]Sub[/COLOR] MG26Oct30
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Twn     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] ac      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] tot     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            Twn = Dn & Dn(, 2) & Dn(, 3)
                [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
                    .Add Twn, Array(Dn, Dn(, 2), Dn(, 3), Dn(, 4), Dn(, 5), Dn(, 6), Dn(, 7))
                [COLOR="Navy"]Else[/COLOR]
                    Q = .Item(Twn)
                        Q(3) = Application.Min(Q(3), Dn(, 4))
                        Q(4) = Application.max(Q(4), Dn(, 5))
                    .Item(Twn) = Q
                [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    tot = tot + (.Item(K)(4) - .Item(K)(3))
[COLOR="Navy"]Next[/COLOR] K
ReDim ray(1 To tot, 1 To 5)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
      [COLOR="Navy"]For[/COLOR] ac = .Item(K)(3) To .Item(K)(4) - 1
            c = c + 1
            ray(c, 1) = .Item(K)(0)
            ray(c, 2) = .Item(K)(1)
            ray(c, 3) = .Item(K)(2)
            ray(c, 4) = ac
            ray(c, 5) = .Item(K)(5) & " " & .Item(K)(6)
        [COLOR="Navy"]Next[/COLOR] ac
    [COLOR="Navy"]Next[/COLOR] K
Range("H2").Resize(tot, 4) = ray
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
col = Application.InputBox(prompt:="Input column Number ", Title:="Column Paste", Type:=2)
[COLOR="Navy"]If[/COLOR] col = 0 Or col = False [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    Cells(2, CInt(col)).Resize(tot) = Application.Index(ray, Evaluate("row(1:" & UBound(ray) & ")"), 5)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
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
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
469
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
622
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
686
John_w
J

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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