There has to be any easier way!!

Hi
any suggestions how to improve the macro listed below. Its an absolute pain
having to change the macro each week because the data and positions alter
after updating the data.

Any help much appreciated ----- Mully
____________________________________________________________

Sub Button2_Click()

Range("B3:L3").Select
Selection.Copy
Range("B24").Select
ActiveSheet.Paste
Range("B4:L4").Select
Application.CutCopyMode = False
Selection.Copy
Range("B46").Select
ActiveSheet.Paste
Range("B5:L5").Select
Application.CutCopyMode = False
Selection.Copy
Range("B25").Select
ActiveSheet.Paste
Range("B6:L7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B47").Select
ActiveSheet.Paste
Range("B8:L8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B26").Select
ActiveSheet.Paste
Range("B9:L9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B35").Select
ActiveSheet.Paste
Range("B10:L10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B49").Select
ActiveSheet.Paste
Range("B11:L11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B27").Select
ActiveSheet.Paste
Range("B12:L12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B50").Select
ActiveSheet.Paste
Range("B13:L13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B28").Select
ActiveSheet.Paste
Range("B14:L17").Select
Application.CutCopyMode = False
Selection.Copy
Range("B29").Select
ActiveSheet.Paste
Range("B18:L18").Select
Application.CutCopyMode = False
Selection.Copy
Range("B36").Select
ActiveSheet.Paste
Range("L20").Select
Range("B19:L19").Select
Application.CutCopyMode = False
Selection.Copy
Range("B33").Select
ActiveSheet.Paste
Range("B22:L22").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Range("A2").Select
End Sub

Sub Button2_Click()

Range("B3:L3").Copy Range("B24")
Range("B4:L4").Copy Range("B46")
Range("B5:L5").Copy Range("B25")
Range("B6:L7").Copy Range("B47")
Range("B8:L8").Copy Range("B26")
Range("B9:L9").Copy Range("B35")
Range("B10:L10").Copy Range("B49")
Range("B11:L11").Copy Range("B27")
Range("B12:L12").Copy Range("B50")
Range("B13:L13").Copy Range("B28")
Range("B14:L17").Copy Range("B29")
Range("B18:L18").Copy Range("B36")
Range("B19:L19").Copy Range("B33")
Range("B22:L22").Copy Range("B51")
Range("A2").Select
Application.CutCopyMode = False
End Sub


--
HTH

Bob Phillips

"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi
> any suggestions how to improve the macro listed below. Its an absolute
pain
> having to change the macro each week because the data and positions alter
> after updating the data.
>
> Any help much appreciated ----- Mully
> ____________________________________________________________
>
> Sub Button2_Click()
>
> Range("B3:L3").Select
> Selection.Copy
> Range("B24").Select
> ActiveSheet.Paste
> Range("B4:L4").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B46").Select
> ActiveSheet.Paste
> Range("B5:L5").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B25").Select
> ActiveSheet.Paste
> Range("B6:L7").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B47").Select
> ActiveSheet.Paste
> Range("B8:L8").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B26").Select
> ActiveSheet.Paste
> Range("B9:L9").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B35").Select
> ActiveSheet.Paste
> Range("B10:L10").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B49").Select
> ActiveSheet.Paste
> Range("B11:L11").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B27").Select
> ActiveSheet.Paste
> Range("B12:L12").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B50").Select
> ActiveSheet.Paste
> Range("B13:L13").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B28").Select
> ActiveSheet.Paste
> Range("B14:L17").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B29").Select
> ActiveSheet.Paste
> Range("B18:L18").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B36").Select
> ActiveSheet.Paste
> Range("L20").Select
> Range("B19:L19").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B33").Select
> ActiveSheet.Paste
> Range("B22:L22").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B51").Select
> ActiveSheet.Paste
> Range("A2").Select
> End Sub
>

Hi Bob

So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not in the
same class in fact to use an old adage he's not good enough to lace his boots
- and if he's an of International class Gawd 'elp us all.

Now back to businessas you know I've used this community an awful lot in
recent weeks and thanks to everyone they've taught me a lot - yourself
especially!!! just tried and used the revised macro worked spot on first time
great & thanks - can I ask another question regards this worksheet and its a
damn long sheet I shortened the macro to 20 row entries from 260 rows - If I
gave each row in column B a code number as follows A1 to A10 and through the
alphabet to Z1:10 (thats why we have 260 rows) through the week after
sorting the data each day they obviously end up in different rows on the
sheet. is it possible to place say A1:A10 in order in row 265- 274 then
B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I would
use Column B for the Identifier the other data would then be in Columns C - M.

Thanks again ------ Mully

"Bob Phillips" wrote:

> Sub Button2_Click()
>
> Range("B3:L3").Copy Range("B24")
> Range("B4:L4").Copy Range("B46")
> Range("B5:L5").Copy Range("B25")
> Range("B6:L7").Copy Range("B47")
> Range("B8:L8").Copy Range("B26")
> Range("B9:L9").Copy Range("B35")
> Range("B10:L10").Copy Range("B49")
> Range("B11:L11").Copy Range("B27")
> Range("B12:L12").Copy Range("B50")
> Range("B13:L13").Copy Range("B28")
> Range("B14:L17").Copy Range("B29")
> Range("B18:L18").Copy Range("B36")
> Range("B19:L19").Copy Range("B33")
> Range("B22:L22").Copy Range("B51")
> Range("A2").Select
> Application.CutCopyMode = False
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "mully" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> > any suggestions how to improve the macro listed below. Its an absolute
> pain
> > having to change the macro each week because the data and positions alter
> > after updating the data.
> >
> > Any help much appreciated ----- Mully
> > ____________________________________________________________
> >
> > Sub Button2_Click()
> >
> > Range("B3:L3").Select
> > Selection.Copy
> > Range("B24").Select
> > ActiveSheet.Paste
> > Range("B4:L4").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B46").Select
> > ActiveSheet.Paste
> > Range("B5:L5").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B25").Select
> > ActiveSheet.Paste
> > Range("B6:L7").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B47").Select
> > ActiveSheet.Paste
> > Range("B8:L8").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B26").Select
> > ActiveSheet.Paste
> > Range("B9:L9").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B35").Select
> > ActiveSheet.Paste
> > Range("B10:L10").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B49").Select
> > ActiveSheet.Paste
> > Range("B11:L11").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B27").Select
> > ActiveSheet.Paste
> > Range("B12:L12").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B50").Select
> > ActiveSheet.Paste
> > Range("B13:L13").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B28").Select
> > ActiveSheet.Paste
> > Range("B14:L17").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B29").Select
> > ActiveSheet.Paste
> > Range("B18:L18").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B36").Select
> > ActiveSheet.Paste
> > Range("L20").Select
> > Range("B19:L19").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B33").Select
> > ActiveSheet.Paste
> > Range("B22:L22").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Range("B51").Select
> > ActiveSheet.Paste
> > Range("A2").Select
> > End Sub
> >
>
>
>

Sorry Mully, didn't notice it was you.

Comments inline.
"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not in
the
> same class in fact to use an old adage he's not good enough to lace his
boots
> - and if he's an of International class Gawd 'elp us all.

To be prefectly honest, I think we made a mistake not letting him go last
year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not been
the same since his injury, not sure if he is in decline or still moping,
either way it is time to cash him in, so I think Wenger is correct.

I think the Jenas story is a Newcastle plant to entive your boys in. I would
put money on Jenas going to Tampa Bay Disunited than the Gooners.

We do need a strong midfield man though, to support Silva (Jenas ain't him
though).

> Now back to businessas you know I've used this community an awful lot in
> recent weeks and thanks to everyone they've taught me a lot - yourself
> especially!!! just tried and used the revised macro worked spot on first
time
> great & thanks

Pleasure as always.

> can I ask another question regards this worksheet and its a
> damn long sheet I shortened the macro to 20 row entries from 260 rows -
If I
> gave each row in column B a code number as follows A1 to A10 and through
the
> alphabet to Z1:10 (thats why we have 260 rows) through the week after
> sorting the data each day they obviously end up in different rows on the
> sheet. is it possible to place say A1:A10 in order in row 265- 274 then
> B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I would
> use Column B for the Identifier the other data would then be in Columns
C - M.

Not sure I get the question, but here is a stab

Sub Mully()
Dim i As Long
Dim j As Long
For j = 1 To 26
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
j).Address(False, False)
Next i
Next j
End Sub

Hi Bob

Now I'm on my own (Wife gone spending) had a good go with the previous and
got it working ok but because I didn't explain it properly - I have set up a
test sheet with 30 rows - all using columns B to L filled with data. In
column B on each row I inserted a code number A1:A10 / B1:B10 / C1:C10and
mixed them all up so say for example A6 could by the end of the week be on
row 17. I have jigged around with your previous code and it now will place
the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 - 53 /
C1:cC10 on rows 55-64 all in the correct order how would i get it to grab the
data in Columns C:L and put it on the same row that say A6 has moved to which
at the moment is row 38. I was wasting so much time copying and pasting just
trying to keep up to date.

Thanks and all the best for the new season it looks like we are both going
to need it
Cheers Mully

"Bob Phillips" wrote:

> Sorry Mully, didn't notice it was you.
>
> Comments inline.
> "mully" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not in
> the
> > same class in fact to use an old adage he's not good enough to lace his
> boots
> > - and if he's an of International class Gawd 'elp us all.
>
> To be prefectly honest, I think we made a mistake not letting him go last
> year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not been
> the same since his injury, not sure if he is in decline or still moping,
> either way it is time to cash him in, so I think Wenger is correct.
>
> I think the Jenas story is a Newcastle plant to entive your boys in. I would
> put money on Jenas going to Tampa Bay Disunited than the Gooners.
>
> We do need a strong midfield man though, to support Silva (Jenas ain't him
> though).
>
> > Now back to businessas you know I've used this community an awful lot in
> > recent weeks and thanks to everyone they've taught me a lot - yourself
> > especially!!! just tried and used the revised macro worked spot on first
> time
> > great & thanks
>
> Pleasure as always.
>
> > can I ask another question regards this worksheet and its a
> > damn long sheet I shortened the macro to 20 row entries from 260 rows -
> If I
> > gave each row in column B a code number as follows A1 to A10 and through
> the
> > alphabet to Z1:10 (thats why we have 260 rows) through the week after
> > sorting the data each day they obviously end up in different rows on the
> > sheet. is it possible to place say A1:A10 in order in row 265- 274 then
> > B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I would
> > use Column B for the Identifier the other data would then be in Columns
> C - M.
>
> Not sure I get the question, but here is a stab
>
> Sub Mully()
> Dim i As Long
> Dim j As Long
> For j = 1 To 26
> For i = 1 To 10
> Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> j).Address(False, False)
> Next i
> Next j
> End Sub
>
>
>
>

Is this it?

Sub Mully()
Dim i As Long
Dim j As Long
For j = 1 To 26
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = _
Cells(i, j).Address(False, False)
Next i
Next j

For i = 1 To 17
j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
Next i
End Sub


--
HTH

Bob Phillips

"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Now I'm on my own (Wife gone spending) had a good go with the previous and
> got it working ok but because I didn't explain it properly - I have set up
a
> test sheet with 30 rows - all using columns B to L filled with data. In
> column B on each row I inserted a code number A1:A10 / B1:B10 / C1:C10and
> mixed them all up so say for example A6 could by the end of the week be on
> row 17. I have jigged around with your previous code and it now will place
> the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 - 53 /
> C1:cC10 on rows 55-64 all in the correct order how would i get it to grab
the
> data in Columns C:L and put it on the same row that say A6 has moved to
which
> at the moment is row 38. I was wasting so much time copying and pasting
just
> trying to keep up to date.
>
> Thanks and all the best for the new season it looks like we are both going
> to need it
> Cheers Mully
>
> "Bob Phillips" wrote:
>
> > Sorry Mully, didn't notice it was you.
> >
> > Comments inline.
> > "mully" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob
> > >
> > > So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not
in
> > the
> > > same class in fact to use an old adage he's not good enough to lace
his
> > boots
> > > - and if he's an of International class Gawd 'elp us all.
> >
> > To be prefectly honest, I think we made a mistake not letting him go
last
> > year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not
been
> > the same since his injury, not sure if he is in decline or still moping,
> > either way it is time to cash him in, so I think Wenger is correct.
> >
> > I think the Jenas story is a Newcastle plant to entive your boys in. I
would
> > put money on Jenas going to Tampa Bay Disunited than the Gooners.
> >
> > We do need a strong midfield man though, to support Silva (Jenas ain't
him
> > though).
> >
> > > Now back to businessas you know I've used this community an awful lot
in
> > > recent weeks and thanks to everyone they've taught me a lot -
yourself
> > > especially!!! just tried and used the revised macro worked spot on
first
> > time
> > > great & thanks
> >
> > Pleasure as always.
> >
> > > can I ask another question regards this worksheet and its a
> > > damn long sheet I shortened the macro to 20 row entries from 260
rows -
> > If I
> > > gave each row in column B a code number as follows A1 to A10 and
through
> > the
> > > alphabet to Z1:10 (thats why we have 260 rows) through the week after
> > > sorting the data each day they obviously end up in different rows on
the
> > > sheet. is it possible to place say A1:A10 in order in row 265- 274
then
> > > B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I
would
> > > use Column B for the Identifier the other data would then be in
Columns
> > C - M.
> >
> > Not sure I get the question, but here is a stab
> >
> > Sub Mully()
> > Dim i As Long
> > Dim j As Long
> > For j = 1 To 26
> > For i = 1 To 10
> > Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> > j).Address(False, False)
> > Next i
> > Next j
> > End Sub
> >
> >
> >
> >

Hi Bob

Getting this error message when running code ---

Run-time error '13' Type Mis Match
VBA picks out the line below

j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)

When this runs correctly will it also copy each rows cells C to L
along with the code number say A6 ??


Looks like you are right according to the press this morning Owen & Jenas
are on Tampa Bay United's shopping list don't mind Owen But Jenas ARGH!!!!!
Glad I went fishing before I read that.

Cheers ----- Mully









"Bob Phillips" wrote:

> Is this it?
>
> Sub Mully()
> Dim i As Long
> Dim j As Long
> For j = 1 To 26
> For i = 1 To 10
> Cells(264 + (j - 1) * 10 + i, "A").Value = _
> Cells(i, j).Address(False, False)
> Next i
> Next j
>
> For i = 1 To 17
> j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
> Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> Next i
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "mully" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > Now I'm on my own (Wife gone spending) had a good go with the previous and
> > got it working ok but because I didn't explain it properly - I have set up
> a
> > test sheet with 30 rows - all using columns B to L filled with data. In
> > column B on each row I inserted a code number A1:A10 / B1:B10 / C1:C10and
> > mixed them all up so say for example A6 could by the end of the week be on
> > row 17. I have jigged around with your previous code and it now will place
> > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 - 53 /
> > C1:cC10 on rows 55-64 all in the correct order how would i get it to grab
> the
> > data in Columns C:L and put it on the same row that say A6 has moved to
> which
> > at the moment is row 38. I was wasting so much time copying and pasting
> just
> > trying to keep up to date.
> >
> > Thanks and all the best for the new season it looks like we are both going
> > to need it
> > Cheers Mully
> >
> > "Bob Phillips" wrote:
> >
> > > Sorry Mully, didn't notice it was you.
> > >
> > > Comments inline.
> > > "mully" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob
> > > >
> > > > So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas not
> in
> > > the
> > > > same class in fact to use an old adage he's not good enough to lace
> his
> > > boots
> > > > - and if he's an of International class Gawd 'elp us all.
> > >
> > > To be prefectly honest, I think we made a mistake not letting him go
> last
> > > year for Euro 40M to Madrid. As a consequence, Viera sulked. He has not
> been
> > > the same since his injury, not sure if he is in decline or still moping,
> > > either way it is time to cash him in, so I think Wenger is correct.
> > >
> > > I think the Jenas story is a Newcastle plant to entive your boys in. I
> would
> > > put money on Jenas going to Tampa Bay Disunited than the Gooners.
> > >
> > > We do need a strong midfield man though, to support Silva (Jenas ain't
> him
> > > though).
> > >
> > > > Now back to businessas you know I've used this community an awful lot
> in
> > > > recent weeks and thanks to everyone they've taught me a lot -
> yourself
> > > > especially!!! just tried and used the revised macro worked spot on
> first
> > > time
> > > > great & thanks
> > >
> > > Pleasure as always.
> > >
> > > > can I ask another question regards this worksheet and its a
> > > > damn long sheet I shortened the macro to 20 row entries from 260
> rows -
> > > If I
> > > > gave each row in column B a code number as follows A1 to A10 and
> through
> > > the
> > > > alphabet to Z1:10 (thats why we have 260 rows) through the week after
> > > > sorting the data each day they obviously end up in different rows on
> the
> > > > sheet. is it possible to place say A1:A10 in order in row 265- 274
> then
> > > > B1:B10 in rows 275 - 284 and so so on through to letter Z. Because I
> would
> > > > use Column B for the Identifier the other data would then be in
> Columns
> > > C - M.
> > >
> > > Not sure I get the question, but here is a stab
> > >
> > > Sub Mully()
> > > Dim i As Long
> > > Dim j As Long
> > > For j = 1 To 26
> > > For i = 1 To 10
> > > Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> > > j).Address(False, False)
> > > Next i
> > > Next j
> > > End Sub
> > >
> > >
> > >
> > >
>
>
>

Hi Mully,

That suggests that the value in Bn is not found in the newly generated
A1-A10, B1-B10, etc. I assumed that the values in B1:B30 would be within
those ranges of values.

You don't have a value in B1:B30 that isn't one of those do you?

BTW, the line

For i = 1 To 17

should read

For i = 1 To 30

and it will copy B-L from the rows 1:30 to the equivalent cells in rows
265:524

Owen and Jenas, you can have them both, I don't rate Jenas, and feel the
team has to play for Owen rather than vice versa.

--
HTH

Bob Phillips

"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Getting this error message when running code ---
>
> Run-time error '13' Type Mis Match
> VBA picks out the line below
>
> j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
>
> When this runs correctly will it also copy each rows cells C to L
> along with the code number say A6 ??
>
>
> Looks like you are right according to the press this morning Owen & Jenas
> are on Tampa Bay United's shopping list don't mind Owen But Jenas
ARGH!!!!!
> Glad I went fishing before I read that.
>
> Cheers ----- Mully
>
>
>
>
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > Is this it?
> >
> > Sub Mully()
> > Dim i As Long
> > Dim j As Long
> > For j = 1 To 26
> > For i = 1 To 10
> > Cells(264 + (j - 1) * 10 + i, "A").Value = _
> > Cells(i, j).Address(False, False)
> > Next i
> > Next j
> >
> > For i = 1 To 17
> > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"),
0)
> > Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> > Next i
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "mully" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob
> > >
> > > Now I'm on my own (Wife gone spending) had a good go with the previous
and
> > > got it working ok but because I didn't explain it properly - I have
set up
> > a
> > > test sheet with 30 rows - all using columns B to L filled with data.
In
> > > column B on each row I inserted a code number A1:A10 / B1:B10 /
C1:C10and
> > > mixed them all up so say for example A6 could by the end of the week
be on
> > > row 17. I have jigged around with your previous code and it now will
place
> > > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 -
53 /
> > > C1:cC10 on rows 55-64 all in the correct order how would i get it to
grab
> > the
> > > data in Columns C:L and put it on the same row that say A6 has moved
to
> > which
> > > at the moment is row 38. I was wasting so much time copying and
pasting
> > just
> > > trying to keep up to date.
> > >
> > > Thanks and all the best for the new season it looks like we are both
going
> > > to need it
> > > Cheers Mully
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Sorry Mully, didn't notice it was you.
> > > >
> > > > Comments inline.
> > > > "mully" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob
> > > > >
> > > > > So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas
not
> > in
> > > > the
> > > > > same class in fact to use an old adage he's not good enough to
lace
> > his
> > > > boots
> > > > > - and if he's an of International class Gawd 'elp us all.
> > > >
> > > > To be prefectly honest, I think we made a mistake not letting him go
> > last
> > > > year for Euro 40M to Madrid. As a consequence, Viera sulked. He has
not
> > been
> > > > the same since his injury, not sure if he is in decline or still
moping,
> > > > either way it is time to cash him in, so I think Wenger is correct.
> > > >
> > > > I think the Jenas story is a Newcastle plant to entive your boys in.
I
> > would
> > > > put money on Jenas going to Tampa Bay Disunited than the Gooners.
> > > >
> > > > We do need a strong midfield man though, to support Silva (Jenas
ain't
> > him
> > > > though).
> > > >
> > > > > Now back to businessas you know I've used this community an awful
lot
> > in
> > > > > recent weeks and thanks to everyone they've taught me a lot -
> > yourself
> > > > > especially!!! just tried and used the revised macro worked spot on
> > first
> > > > time
> > > > > great & thanks
> > > >
> > > > Pleasure as always.
> > > >
> > > > > can I ask another question regards this worksheet and its a
> > > > > damn long sheet I shortened the macro to 20 row entries from 260
> > rows -
> > > > If I
> > > > > gave each row in column B a code number as follows A1 to A10 and
> > through
> > > > the
> > > > > alphabet to Z1:10 (thats why we have 260 rows) through the week
after
> > > > > sorting the data each day they obviously end up in different rows
on
> > the
> > > > > sheet. is it possible to place say A1:A10 in order in row 265-
274
> > then
> > > > > B1:B10 in rows 275 - 284 and so so on through to letter Z. Because
I
> > would
> > > > > use Column B for the Identifier the other data would then be in
> > Columns
> > > > C - M.
> > > >
> > > > Not sure I get the question, but here is a stab
> > > >
> > > > Sub Mully()
> > > > Dim i As Long
> > > > Dim j As Long
> > > > For j = 1 To 26
> > > > For i = 1 To 10
> > > > Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> > > > j).Address(False, False)
> > > > Next i
> > > > Next j
> > > > End Sub
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Bob

Re - jigged the sheet I had forgotten about the header row - worked
absolutely spot on when I took it out and I inserted the adjustments you
suggested .Once again thank you for all your help - I'm sure I'll be here
again sometime in the future and maybe we will link up again.

All the best

Mully ----- Come on you Reds !!!!! Tampa

"Bob Phillips" wrote:

> Hi Mully,
>
> That suggests that the value in Bn is not found in the newly generated
> A1-A10, B1-B10, etc. I assumed that the values in B1:B30 would be within
> those ranges of values.
>
> You don't have a value in B1:B30 that isn't one of those do you?
>
> BTW, the line
>
> For i = 1 To 17
>
> should read
>
> For i = 1 To 30
>
> and it will copy B-L from the rows 1:30 to the equivalent cells in rows
> 265:524
>
> Owen and Jenas, you can have them both, I don't rate Jenas, and feel the
> team has to play for Owen rather than vice versa.
>
> --
> HTH
>
> Bob Phillips
>
> "mully" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > Getting this error message when running code ---
> >
> > Run-time error '13' Type Mis Match
> > VBA picks out the line below
> >
> > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
> >
> > When this runs correctly will it also copy each rows cells C to L
> > along with the code number say A6 ??
> >
> >
> > Looks like you are right according to the press this morning Owen & Jenas
> > are on Tampa Bay United's shopping list don't mind Owen But Jenas
> ARGH!!!!!
> > Glad I went fishing before I read that.
> >
> > Cheers ----- Mully
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Is this it?
> > >
> > > Sub Mully()
> > > Dim i As Long
> > > Dim j As Long
> > > For j = 1 To 26
> > > For i = 1 To 10
> > > Cells(264 + (j - 1) * 10 + i, "A").Value = _
> > > Cells(i, j).Address(False, False)
> > > Next i
> > > Next j
> > >
> > > For i = 1 To 17
> > > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"),
> 0)
> > > Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> > > Next i
> > > End Sub
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "mully" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob
> > > >
> > > > Now I'm on my own (Wife gone spending) had a good go with the previous
> and
> > > > got it working ok but because I didn't explain it properly - I have
> set up
> > > a
> > > > test sheet with 30 rows - all using columns B to L filled with data.
> In
> > > > column B on each row I inserted a code number A1:A10 / B1:B10 /
> C1:C10and
> > > > mixed them all up so say for example A6 could by the end of the week
> be on
> > > > row 17. I have jigged around with your previous code and it now will
> place
> > > > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows 44 -
> 53 /
> > > > C1:cC10 on rows 55-64 all in the correct order how would i get it to
> grab
> > > the
> > > > data in Columns C:L and put it on the same row that say A6 has moved
> to
> > > which
> > > > at the moment is row 38. I was wasting so much time copying and
> pasting
> > > just
> > > > trying to keep up to date.
> > > >
> > > > Thanks and all the best for the new season it looks like we are both
> going
> > > > to need it
> > > > Cheers Mully
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Sorry Mully, didn't notice it was you.
> > > > >
> > > > > Comments inline.
> > > > > "mully" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob
> > > > > >
> > > > > > So Viera's gone and Wenger wants Jenas to replace him eh!! Jenas
> not
> > > in
> > > > > the
> > > > > > same class in fact to use an old adage he's not good enough to
> lace
> > > his
> > > > > boots
> > > > > > - and if he's an of International class Gawd 'elp us all.
> > > > >
> > > > > To be prefectly honest, I think we made a mistake not letting him go
> > > last
> > > > > year for Euro 40M to Madrid. As a consequence, Viera sulked. He has
> not
> > > been
> > > > > the same since his injury, not sure if he is in decline or still
> moping,
> > > > > either way it is time to cash him in, so I think Wenger is correct.
> > > > >
> > > > > I think the Jenas story is a Newcastle plant to entive your boys in.
> I
> > > would
> > > > > put money on Jenas going to Tampa Bay Disunited than the Gooners.
> > > > >
> > > > > We do need a strong midfield man though, to support Silva (Jenas
> ain't
> > > him
> > > > > though).
> > > > >
> > > > > > Now back to businessas you know I've used this community an awful
> lot
> > > in
> > > > > > recent weeks and thanks to everyone they've taught me a lot -
> > > yourself
> > > > > > especially!!! just tried and used the revised macro worked spot on
> > > first
> > > > > time
> > > > > > great & thanks
> > > > >
> > > > > Pleasure as always.
> > > > >
> > > > > > can I ask another question regards this worksheet and its a
> > > > > > damn long sheet I shortened the macro to 20 row entries from 260
> > > rows -
> > > > > If I
> > > > > > gave each row in column B a code number as follows A1 to A10 and
> > > through
> > > > > the
> > > > > > alphabet to Z1:10 (thats why we have 260 rows) through the week
> after
> > > > > > sorting the data each day they obviously end up in different rows
> on
> > > the
> > > > > > sheet. is it possible to place say A1:A10 in order in row 265-
> 274
> > > then
> > > > > > B1:B10 in rows 275 - 284 and so so on through to letter Z. Because
> I
> > > would
> > > > > > use Column B for the Identifier the other data would then be in
> > > Columns
> > > > > C - M.
> > > > >
> > > > > Not sure I get the question, but here is a stab
> > > > >
> > > > > Sub Mully()
> > > > > Dim i As Long
> > > > > Dim j As Long
> > > > > For j = 1 To 26
> > > > > For i = 1 To 10
> > > > > Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> > > > > j).Address(False, False)
> > > > > Next i
> > > > > Next j
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Look forward to it Mully.

By the time we next meet, I am sure that Arsenal will be top, and Tampa will
be floundering in the bottom half. Yeah!

Bob

"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Re - jigged the sheet I had forgotten about the header row - worked
> absolutely spot on when I took it out and I inserted the adjustments you
> suggested .Once again thank you for all your help - I'm sure I'll be here
> again sometime in the future and maybe we will link up again.
>
> All the best
>
> Mully ----- Come on you Reds !!!!! Tampa
>
> "Bob Phillips" wrote:
>
> > Hi Mully,
> >
> > That suggests that the value in Bn is not found in the newly generated
> > A1-A10, B1-B10, etc. I assumed that the values in B1:B30 would be within
> > those ranges of values.
> >
> > You don't have a value in B1:B30 that isn't one of those do you?
> >
> > BTW, the line
> >
> > For i = 1 To 17
> >
> > should read
> >
> > For i = 1 To 30
> >
> > and it will copy B-L from the rows 1:30 to the equivalent cells in rows
> > 265:524
> >
> > Owen and Jenas, you can have them both, I don't rate Jenas, and feel the
> > team has to play for Owen rather than vice versa.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "mully" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob
> > >
> > > Getting this error message when running code ---
> > >
> > > Run-time error '13' Type Mis Match
> > > VBA picks out the line below
> > >
> > > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
> > >
> > > When this runs correctly will it also copy each rows cells C to L
> > > along with the code number say A6 ??
> > >
> > >
> > > Looks like you are right according to the press this morning Owen &
Jenas
> > > are on Tampa Bay United's shopping list don't mind Owen But Jenas
> > ARGH!!!!!
> > > Glad I went fishing before I read that.
> > >
> > > Cheers ----- Mully
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Is this it?
> > > >
> > > > Sub Mully()
> > > > Dim i As Long
> > > > Dim j As Long
> > > > For j = 1 To 26
> > > > For i = 1 To 10
> > > > Cells(264 + (j - 1) * 10 + i, "A").Value = _
> > > > Cells(i, j).Address(False, False)
> > > > Next i
> > > > Next j
> > > >
> > > > For i = 1 To 17
> > > > j = Application.Match(Cells(i, "B").Value,
Range("A265:A524"),
> > 0)
> > > > Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> > > > Next i
> > > > End Sub
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "mully" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob
> > > > >
> > > > > Now I'm on my own (Wife gone spending) had a good go with the
previous
> > and
> > > > > got it working ok but because I didn't explain it properly - I
have
> > set up
> > > > a
> > > > > test sheet with 30 rows - all using columns B to L filled with
data.
> > In
> > > > > column B on each row I inserted a code number A1:A10 / B1:B10 /
> > C1:C10and
> > > > > mixed them all up so say for example A6 could by the end of the
week
> > be on
> > > > > row 17. I have jigged around with your previous code and it now
will
> > place
> > > > > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows
44 -
> > 53 /
> > > > > C1:cC10 on rows 55-64 all in the correct order how would i get it
to
> > grab
> > > > the
> > > > > data in Columns C:L and put it on the same row that say A6 has
moved
> > to
> > > > which
> > > > > at the moment is row 38. I was wasting so much time copying and
> > pasting
> > > > just
> > > > > trying to keep up to date.
> > > > >
> > > > > Thanks and all the best for the new season it looks like we are
both
> > going
> > > > > to need it
> > > > > Cheers Mully
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Sorry Mully, didn't notice it was you.
> > > > > >
> > > > > > Comments inline.
> > > > > > "mully" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Bob
> > > > > > >
> > > > > > > So Viera's gone and Wenger wants Jenas to replace him eh!!
Jenas
> > not
> > > > in
> > > > > > the
> > > > > > > same class in fact to use an old adage he's not good enough to
> > lace
> > > > his
> > > > > > boots
> > > > > > > - and if he's an of International class Gawd 'elp us all.
> > > > > >
> > > > > > To be prefectly honest, I think we made a mistake not letting
him go
> > > > last
> > > > > > year for Euro 40M to Madrid. As a consequence, Viera sulked. He
has
> > not
> > > > been
> > > > > > the same since his injury, not sure if he is in decline or still
> > moping,
> > > > > > either way it is time to cash him in, so I think Wenger is
correct.
> > > > > >
> > > > > > I think the Jenas story is a Newcastle plant to entive your boys
in.
> > I
> > > > would
> > > > > > put money on Jenas going to Tampa Bay Disunited than the
Gooners.
> > > > > >
> > > > > > We do need a strong midfield man though, to support Silva (Jenas
> > ain't
> > > > him
> > > > > > though).
> > > > > >
> > > > > > > Now back to businessas you know I've used this community an
awful
> > lot
> > > > in
> > > > > > > recent weeks and thanks to everyone they've taught me a lot -
> > > > yourself
> > > > > > > especially!!! just tried and used the revised macro worked
spot on
> > > > first
> > > > > > time
> > > > > > > great & thanks
> > > > > >
> > > > > > Pleasure as always.
> > > > > >
> > > > > > > can I ask another question regards this worksheet and its a
> > > > > > > damn long sheet I shortened the macro to 20 row entries from
260
> > > > rows -
> > > > > > If I
> > > > > > > gave each row in column B a code number as follows A1 to A10
and
> > > > through
> > > > > > the
> > > > > > > alphabet to Z1:10 (thats why we have 260 rows) through the
week
> > after
> > > > > > > sorting the data each day they obviously end up in different
rows
> > on
> > > > the
> > > > > > > sheet. is it possible to place say A1:A10 in order in row
265-
> > 274
> > > > then
> > > > > > > B1:B10 in rows 275 - 284 and so so on through to letter Z.
Because
> > I
> > > > would
> > > > > > > use Column B for the Identifier the other data would then be
in
> > > > Columns
> > > > > > C - M.
> > > > > >
> > > > > > Not sure I get the question, but here is a stab
> > > > > >
> > > > > > Sub Mully()
> > > > > > Dim i As Long
> > > > > > Dim j As Long
> > > > > > For j = 1 To 26
> > > > > > For i = 1 To 10
> > > > > > Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> > > > > > j).Address(False, False)
> > > > > > Next i
> > > > > > Next j
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Bob

You're livin' in a dream world there my friend ( I bloody 'ope so )

Anyway I can use what you have sent me over the weekend on another sheet
which would only use 3 letters as part of the ID e.g. K / T / S in the line
below

For j = 1 To 26 ( this picks up A - Z ???)

How would I change it to pick up these three letters K/T/S instead of the
whole Alphabet

Cheers and 'appy dreamin' ----- Mully


"Bob Phillips" wrote:

> Look forward to it Mully.
>
> By the time we next meet, I am sure that Arsenal will be top, and Tampa will
> be floundering in the bottom half. Yeah!
>
> Bob
>
> "mully" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > Re - jigged the sheet I had forgotten about the header row - worked
> > absolutely spot on when I took it out and I inserted the adjustments you
> > suggested .Once again thank you for all your help - I'm sure I'll be here
> > again sometime in the future and maybe we will link up again.
> >
> > All the best
> >
> > Mully ----- Come on you Reds !!!!! Tampa
> >
> > "Bob Phillips" wrote:
> >
> > > Hi Mully,
> > >
> > > That suggests that the value in Bn is not found in the newly generated
> > > A1-A10, B1-B10, etc. I assumed that the values in B1:B30 would be within
> > > those ranges of values.
> > >
> > > You don't have a value in B1:B30 that isn't one of those do you?
> > >
> > > BTW, the line
> > >
> > > For i = 1 To 17
> > >
> > > should read
> > >
> > > For i = 1 To 30
> > >
> > > and it will copy B-L from the rows 1:30 to the equivalent cells in rows
> > > 265:524
> > >
> > > Owen and Jenas, you can have them both, I don't rate Jenas, and feel the
> > > team has to play for Owen rather than vice versa.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "mully" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob
> > > >
> > > > Getting this error message when running code ---
> > > >
> > > > Run-time error '13' Type Mis Match
> > > > VBA picks out the line below
> > > >
> > > > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
> > > >
> > > > When this runs correctly will it also copy each rows cells C to L
> > > > along with the code number say A6 ??
> > > >
> > > >
> > > > Looks like you are right according to the press this morning Owen &
> Jenas
> > > > are on Tampa Bay United's shopping list don't mind Owen But Jenas
> > > ARGH!!!!!
> > > > Glad I went fishing before I read that.
> > > >
> > > > Cheers ----- Mully
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Is this it?
> > > > >
> > > > > Sub Mully()
> > > > > Dim i As Long
> > > > > Dim j As Long
> > > > > For j = 1 To 26
> > > > > For i = 1 To 10
> > > > > Cells(264 + (j - 1) * 10 + i, "A").Value = _
> > > > > Cells(i, j).Address(False, False)
> > > > > Next i
> > > > > Next j
> > > > >
> > > > > For i = 1 To 17
> > > > > j = Application.Match(Cells(i, "B").Value,
> Range("A265:A524"),
> > > 0)
> > > > > Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> > > > > Next i
> > > > > End Sub
> > > > >
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "mully" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob
> > > > > >
> > > > > > Now I'm on my own (Wife gone spending) had a good go with the
> previous
> > > and
> > > > > > got it working ok but because I didn't explain it properly - I
> have
> > > set up
> > > > > a
> > > > > > test sheet with 30 rows - all using columns B to L filled with
> data.
> > > In
> > > > > > column B on each row I inserted a code number A1:A10 / B1:B10 /
> > > C1:C10and
> > > > > > mixed them all up so say for example A6 could by the end of the
> week
> > > be on
> > > > > > row 17. I have jigged around with your previous code and it now
> will
> > > place
> > > > > > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on rows
> 44 -
> > > 53 /
> > > > > > C1:cC10 on rows 55-64 all in the correct order how would i get it
> to
> > > grab
> > > > > the
> > > > > > data in Columns C:L and put it on the same row that say A6 has
> moved
> > > to
> > > > > which
> > > > > > at the moment is row 38. I was wasting so much time copying and
> > > pasting
> > > > > just
> > > > > > trying to keep up to date.
> > > > > >
> > > > > > Thanks and all the best for the new season it looks like we are
> both
> > > going
> > > > > > to need it
> > > > > > Cheers Mully
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > Sorry Mully, didn't notice it was you.
> > > > > > >
> > > > > > > Comments inline.
> > > > > > > "mully" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hi Bob
> > > > > > > >
> > > > > > > > So Viera's gone and Wenger wants Jenas to replace him eh!!
> Jenas
> > > not
> > > > > in
> > > > > > > the
> > > > > > > > same class in fact to use an old adage he's not good enough to
> > > lace
> > > > > his
> > > > > > > boots
> > > > > > > > - and if he's an of International class Gawd 'elp us all.
> > > > > > >
> > > > > > > To be prefectly honest, I think we made a mistake not letting
> him go
> > > > > last
> > > > > > > year for Euro 40M to Madrid. As a consequence, Viera sulked. He
> has
> > > not
> > > > > been
> > > > > > > the same since his injury, not sure if he is in decline or still
> > > moping,
> > > > > > > either way it is time to cash him in, so I think Wenger is
> correct.
> > > > > > >
> > > > > > > I think the Jenas story is a Newcastle plant to entive your boys
> in.
> > > I
> > > > > would
> > > > > > > put money on Jenas going to Tampa Bay Disunited than the
> Gooners.
> > > > > > >
> > > > > > > We do need a strong midfield man though, to support Silva (Jenas
> > > ain't
> > > > > him
> > > > > > > though).
> > > > > > >
> > > > > > > > Now back to businessas you know I've used this community an
> awful
> > > lot
> > > > > in
> > > > > > > > recent weeks and thanks to everyone they've taught me a lot -
> > > > > yourself
> > > > > > > > especially!!! just tried and used the revised macro worked
> spot on
> > > > > first
> > > > > > > time
> > > > > > > > great & thanks
> > > > > > >
> > > > > > > Pleasure as always.
> > > > > > >
> > > > > > > > can I ask another question regards this worksheet and its a
> > > > > > > > damn long sheet I shortened the macro to 20 row entries from
> 260
> > > > > rows -
> > > > > > > If I
> > > > > > > > gave each row in column B a code number as follows A1 to A10
> and
> > > > > through
> > > > > > > the
> > > > > > > > alphabet to Z1:10 (thats why we have 260 rows) through the
> week
> > > after
> > > > > > > > sorting the data each day they obviously end up in different
> rows
> > > on
> > > > > the
> > > > > > > > sheet. is it possible to place say A1:A10 in order in row
> 265-
> > > 274
> > > > > then
> > > > > > > > B1:B10 in rows 275 - 284 and so so on through to letter Z.
> Because
> > > I
> > > > > would
> > > > > > > > use Column B for the Identifier the other data would then be
> in
> > > > > Columns
> > > > > > > C - M.
> > > > > > >
> > > > > > > Not sure I get the question, but here is a stab
> > > > > > >
> > > > > > > Sub Mully()
> > > > > > > Dim i As Long
> > > > > > > Dim j As Long
> > > > > > > For j = 1 To 26
> > > > > > > For i = 1 To 10
> > > > > > > Cells(264 + (j - 1) * 10 + i, "A").Value = Cells(i,
> > > > > > > j).Address(False, False)
> > > > > > > Next i
> > > > > > > Next j
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Mully,

This is just the code generation bit

Sub Mully()
Dim i As Long
Dim j As Long
Dim ary

ary = Array(0, 11, 19, 20)
For j = 1 To 3
For i = 1 To 10
Cells(264 + (j - 1) * 10 + i, "A").Value = _
Cells(i, ary(j)).Address(False, False)
Next i
If j = 11 Then j = 18
Next j
End Sub



--
HTH

Bob Phillips

"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> You're livin' in a dream world there my friend ( I bloody 'ope so )
>
> Anyway I can use what you have sent me over the weekend on another sheet
> which would only use 3 letters as part of the ID e.g. K / T / S in the
line
> below
>
> For j = 1 To 26 ( this picks up A - Z ???)
>
> How would I change it to pick up these three letters K/T/S instead of the
> whole Alphabet
>
> Cheers and 'appy dreamin' ----- Mully
>
>
> "Bob Phillips" wrote:
>
> > Look forward to it Mully.
> >
> > By the time we next meet, I am sure that Arsenal will be top, and Tampa
will
> > be floundering in the bottom half. Yeah!
> >
> > Bob
> >
> > "mully" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob
> > >
> > > Re - jigged the sheet I had forgotten about the header row - worked
> > > absolutely spot on when I took it out and I inserted the adjustments
you
> > > suggested .Once again thank you for all your help - I'm sure I'll be
here
> > > again sometime in the future and maybe we will link up again.
> > >
> > > All the best
> > >
> > > Mully ----- Come on you Reds !!!!! Tampa
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Hi Mully,
> > > >
> > > > That suggests that the value in Bn is not found in the newly
generated
> > > > A1-A10, B1-B10, etc. I assumed that the values in B1:B30 would be
within
> > > > those ranges of values.
> > > >
> > > > You don't have a value in B1:B30 that isn't one of those do you?
> > > >
> > > > BTW, the line
> > > >
> > > > For i = 1 To 17
> > > >
> > > > should read
> > > >
> > > > For i = 1 To 30
> > > >
> > > > and it will copy B-L from the rows 1:30 to the equivalent cells in
rows
> > > > 265:524
> > > >
> > > > Owen and Jenas, you can have them both, I don't rate Jenas, and feel
the
> > > > team has to play for Owen rather than vice versa.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "mully" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob
> > > > >
> > > > > Getting this error message when running code ---
> > > > >
> > > > > Run-time error '13' Type Mis Match
> > > > > VBA picks out the line below
> > > > >
> > > > > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
> > > > >
> > > > > When this runs correctly will it also copy each rows cells C to L
> > > > > along with the code number say A6 ??
> > > > >
> > > > >
> > > > > Looks like you are right according to the press this morning Owen
&
> > Jenas
> > > > > are on Tampa Bay United's shopping list don't mind Owen But Jenas
> > > > ARGH!!!!!
> > > > > Glad I went fishing before I read that.
> > > > >
> > > > > Cheers ----- Mully
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Is this it?
> > > > > >
> > > > > > Sub Mully()
> > > > > > Dim i As Long
> > > > > > Dim j As Long
> > > > > > For j = 1 To 26
> > > > > > For i = 1 To 10
> > > > > > Cells(264 + (j - 1) * 10 + i, "A").Value = _
> > > > > > Cells(i, j).Address(False, False)
> > > > > > Next i
> > > > > > Next j
> > > > > >
> > > > > > For i = 1 To 17
> > > > > > j = Application.Match(Cells(i, "B").Value,
> > Range("A265:A524"),
> > > > 0)
> > > > > > Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> > > > > > Next i
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "mully" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Bob
> > > > > > >
> > > > > > > Now I'm on my own (Wife gone spending) had a good go with the
> > previous
> > > > and
> > > > > > > got it working ok but because I didn't explain it properly - I
> > have
> > > > set up
> > > > > > a
> > > > > > > test sheet with 30 rows - all using columns B to L filled
with
> > data.
> > > > In
> > > > > > > column B on each row I inserted a code number A1:A10 / B1:B10
/
> > > > C1:C10and
> > > > > > > mixed them all up so say for example A6 could by the end of
the
> > week
> > > > be on
> > > > > > > row 17. I have jigged around with your previous code and it
now
> > will
> > > > place
> > > > > > > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on
rows
> > 44 -
> > > > 53 /
> > > > > > > C1:cC10 on rows 55-64 all in the correct order how would i get
it
> > to
> > > > grab
> > > > > > the
> > > > > > > data in Columns C:L and put it on the same row that say A6 has
> > moved
> > > > to
> > > > > > which
> > > > > > > at the moment is row 38. I was wasting so much time copying
and
> > > > pasting
> > > > > > just
> > > > > > > trying to keep up to date.
> > > > > > >
> > > > > > > Thanks and all the best for the new season it looks like we
are
> > both
> > > > going
> > > > > > > to need it
> > > > > > > Cheers Mully
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > Sorry Mully, didn't notice it was you.
> > > > > > > >
> > > > > > > > Comments inline.
> > > > > > > > "mully" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > Hi Bob
> > > > > > > > >
> > > > > > > > > So Viera's gone and Wenger wants Jenas to replace him eh!!
> > Jenas
> > > > not
> > > > > > in
> > > > > > > > the
> > > > > > > > > same class in fact to use an old adage he's not good
enough to
> > > > lace
> > > > > > his
> > > > > > > > boots
> > > > > > > > > - and if he's an of International class Gawd 'elp us all.
> > > > > > > >
> > > > > > > > To be prefectly honest, I think we made a mistake not
letting
> > him go
> > > > > > last
> > > > > > > > year for Euro 40M to Madrid. As a consequence, Viera sulked.
He
> > has
> > > > not
> > > > > > been
> > > > > > > > the same since his injury, not sure if he is in decline or
still
> > > > moping,
> > > > > > > > either way it is time to cash him in, so I think Wenger is
> > correct.
> > > > > > > >
> > > > > > > > I think the Jenas story is a Newcastle plant to entive your
boys
> > in.
> > > > I
> > > > > > would
> > > > > > > > put money on Jenas going to Tampa Bay Disunited than the
> > Gooners.
> > > > > > > >
> > > > > > > > We do need a strong midfield man though, to support Silva
(Jenas
> > > > ain't
> > > > > > him
> > > > > > > > though).
> > > > > > > >
> > > > > > > > > Now back to businessas you know I've used this community
an
> > awful
> > > > lot
> > > > > > in
> > > > > > > > > recent weeks and thanks to everyone they've taught me a
lot -
> > > > > > yourself
> > > > > > > > > especially!!! just tried and used the revised macro worked
> > spot on
> > > > > > first
> > > > > > > > time
> > > > > > > > > great & thanks
> > > > > > > >
> > > > > > > > Pleasure as always.
> > > > > > > >
> > > > > > > > > can I ask another question regards this worksheet and its
a
> > > > > > > > > damn long sheet I shortened the macro to 20 row entries
from
> > 260
> > > > > > rows -
> > > > > > > > If I
> > > > > > > > > gave each row in column B a code number as follows A1 to
A10
> > and
> > > > > > through
> > > > > > > > the
> > > > > > > > > alphabet to Z1:10 (thats why we have 260 rows) through
the
> > week
> > > > after
> > > > > > > > > sorting the data each day they obviously end up in
different
> > rows
> > > > on
> > > > > > the
> > > > > > > > > sheet. is it possible to place say A1:A10 in order in row
> > 265-
> > > > 274
> > > > > > then
> > > > > > > > > B1:B10 in rows 275 - 284 and so so on through to letter Z.
> > Because
> > > > I
> > > > > > would
> > > > > > > > > use Column B for the Identifier the other data would then
be
> > in
> > > > > > Columns
> > > > > > > > C - M.
> > > > > > > >
> > > > > > > > Not sure I get the question, but here is a stab
> > > > > > > >
> > > > > > > > Sub Mully()
> > > > > > > > Dim i As Long
> > > > > > > > Dim j As Long
> > > > > > > > For j = 1 To 26
> > > > > > > > For i = 1 To 10
> > > > > > > > Cells(264 + (j - 1) * 10 + i, "A").Value =
Cells(i,
> > > > > > > > j).Address(False, False)
> > > > > > > > Next i
> > > > > > > > Next j
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Bob

The following is the code as entered - before put new code in was working
perfectly
using A/B/C

Sub Button3_Click()


Dim i As Long
Dim j As Long
Dim ary

ary = Array(0, 5, 14, 23)
For j = 1 To 3
For i = 1 To 10
Cells(33 + (j - 1) * 10 + i, "A").Value = _
Cells(i, ary(j)).Address(False, False)
Next i
If j = 11 Then j = 18
Next j
For i = 3 To 32

j = Application.Match(Cells(i, "B").Value, Range("A34:A63"), 0)
Cells(i, "B").Resize(, 11).Copy Cells(33 + j, "B")
Next i

End Sub

where VBA shows error on line below

j = Application.Match(Cells(i, "B").Value, Range("A34:A63"), 0)

What 'am I missing tried changing everything still throws Type Mis match
"error 13"

Cheers Mully

"Bob Phillips" wrote:

> Mully,
>
> This is just the code generation bit
>
> Sub Mully()
> Dim i As Long
> Dim j As Long
> Dim ary
>
> ary = Array(0, 11, 19, 20)
> For j = 1 To 3
> For i = 1 To 10
> Cells(264 + (j - 1) * 10 + i, "A").Value = _
> Cells(i, ary(j)).Address(False, False)
> Next i
> If j = 11 Then j = 18
> Next j
> End Sub
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "mully" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > You're livin' in a dream world there my friend ( I bloody 'ope so )
> >
> > Anyway I can use what you have sent me over the weekend on another sheet
> > which would only use 3 letters as part of the ID e.g. K / T / S in the
> line
> > below
> >
> > For j = 1 To 26 ( this picks up A - Z ???)
> >
> > How would I change it to pick up these three letters K/T/S instead of the
> > whole Alphabet
> >
> > Cheers and 'appy dreamin' ----- Mully
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Look forward to it Mully.
> > >
> > > By the time we next meet, I am sure that Arsenal will be top, and Tampa
> will
> > > be floundering in the bottom half. Yeah!
> > >
> > > Bob
> > >
> > > "mully" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob
> > > >
> > > > Re - jigged the sheet I had forgotten about the header row - worked
> > > > absolutely spot on when I took it out and I inserted the adjustments
> you
> > > > suggested .Once again thank you for all your help - I'm sure I'll be
> here
> > > > again sometime in the future and maybe we will link up again.
> > > >
> > > > All the best
> > > >
> > > > Mully ----- Come on you Reds !!!!! Tampa
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Hi Mully,
> > > > >
> > > > > That suggests that the value in Bn is not found in the newly
> generated
> > > > > A1-A10, B1-B10, etc. I assumed that the values in B1:B30 would be
> within
> > > > > those ranges of values.
> > > > >
> > > > > You don't have a value in B1:B30 that isn't one of those do you?
> > > > >
> > > > > BTW, the line
> > > > >
> > > > > For i = 1 To 17
> > > > >
> > > > > should read
> > > > >
> > > > > For i = 1 To 30
> > > > >
> > > > > and it will copy B-L from the rows 1:30 to the equivalent cells in
> rows
> > > > > 265:524
> > > > >
> > > > > Owen and Jenas, you can have them both, I don't rate Jenas, and feel
> the
> > > > > team has to play for Owen rather than vice versa.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "mully" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob
> > > > > >
> > > > > > Getting this error message when running code ---
> > > > > >
> > > > > > Run-time error '13' Type Mis Match
> > > > > > VBA picks out the line below
> > > > > >
> > > > > > j = Application.Match(Cells(i, "B").Value, Range("A265:A524"), 0)
> > > > > >
> > > > > > When this runs correctly will it also copy each rows cells C to L
> > > > > > along with the code number say A6 ??
> > > > > >
> > > > > >
> > > > > > Looks like you are right according to the press this morning Owen
> &
> > > Jenas
> > > > > > are on Tampa Bay United's shopping list don't mind Owen But Jenas
> > > > > ARGH!!!!!
> > > > > > Glad I went fishing before I read that.
> > > > > >
> > > > > > Cheers ----- Mully
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > Is this it?
> > > > > > >
> > > > > > > Sub Mully()
> > > > > > > Dim i As Long
> > > > > > > Dim j As Long
> > > > > > > For j = 1 To 26
> > > > > > > For i = 1 To 10
> > > > > > > Cells(264 + (j - 1) * 10 + i, "A").Value = _
> > > > > > > Cells(i, j).Address(False, False)
> > > > > > > Next i
> > > > > > > Next j
> > > > > > >
> > > > > > > For i = 1 To 17
> > > > > > > j = Application.Match(Cells(i, "B").Value,
> > > Range("A265:A524"),
> > > > > 0)
> > > > > > > Cells(i, "B").Resize(, 11).Copy Cells(264 + j, "B")
> > > > > > > Next i
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > "mully" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hi Bob
> > > > > > > >
> > > > > > > > Now I'm on my own (Wife gone spending) had a good go with the
> > > previous
> > > > > and
> > > > > > > > got it working ok but because I didn't explain it properly - I
> > > have
> > > > > set up
> > > > > > > a
> > > > > > > > test sheet with 30 rows - all using columns B to L filled
> with
> > > data.
> > > > > In
> > > > > > > > column B on each row I inserted a code number A1:A10 / B1:B10
> /
> > > > > C1:C10and
> > > > > > > > mixed them all up so say for example A6 could by the end of
> the
> > > week
> > > > > be on
> > > > > > > > row 17. I have jigged around with your previous code and it
> now
> > > will
> > > > > place
> > > > > > > > the code numbers in order A1:A10 on rows 33-42 / B1:B10 on
> rows
> > > 44 -
> > > > > 53 /
> > > > > > > > C1:cC10 on rows 55-64 all in the correct order how would i get
> it
> > > to
> > > > > grab
> > > > > > > the
> > > > > > > > data in Columns C:L and put it on the same row that say A6 has
> > > moved
> > > > > to
> > > > > > > which
> > > > > > > > at the moment is row 38. I was wasting so much time copying
> and
> > > > > pasting
> > > > > > > just
> > > > > > > > trying to keep up to date.
> > > > > > > >
> > > > > > > > Thanks and all the best for the new season it looks like we
> are
> > > both
> > > > > going
> > > > > > > > to need it
> > > > > > > > Cheers Mully
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > > Sorry Mully, didn't notice it was you.
> > > > > > > > >
> > > > > > > > > Comments inline.
> > > > > > > > > "mully" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > Hi Bob
> > > > > > > > > >
> > > > > > > > > > So Viera's gone and Wenger wants Jenas to replace him eh!!
> > > Jenas
> > > > > not
> > > > > > > in
> > > > > > > > > the
> > > > > > > > > > same class in fact to use an old adage he's not good
> enough to
> > > > > lace
> > > > > > > his
> > > > > > > > > boots
> > > > > > > > > > - and if he's an of International class Gawd 'elp us all.
> > > > > > > > >
> > > > > > > > > To be prefectly honest, I think we made a mistake not
> letting
> > > him go
> > > > > > > last
> > > > > > > > > year for Euro 40M to Madrid. As a consequence, Viera sulked.
> He
> > > has
> > > > > not
> > > > > > > been
> > > > > > > > > the same since his injury, not sure if he is in decline or
> still
> > > > > moping,
> > > > > > > > > either way it is time to cash him in, so I think Wenger is
> > > correct.
> > > > > > > > >
> > > > > > > > > I think the Jenas story is a Newcastle plant to entive your
> boys
> > > in.
> > > > > I
> > > > > > > would
> > > > > > > > > put money on Jenas going to Tampa Bay Disunited than the
> > > Gooners.
> > > > > > > > >
> > > > > > > > > We do need a strong midfield man though, to support Silva
> (Jenas
> > > > > ain't
> > > > > > > him
> > > > > > > > > though).
> > > > > > > > >
> > > > > > > > > > Now back to businessas you know I've used this community
> an
> > > awful
> > > > > lot
> > > > > > > in
> > > > > > > > > > recent weeks and thanks to everyone they've taught me a
> lot -
> > > > > > > yourself
> > > > > > > > > > especially!!! just tried and used the revised macro worked
> > > spot on
> > > > > > > first
> > > > > > > > > time
> > > > > > > > > > great & thanks
> > > > > > > > >
> > > > > > > > > Pleasure as always.
> > > > > > > > >
> > > > > > > > > > can I ask another question regards this worksheet and its
> a
> > > > > > > > > > damn long sheet I shortened the macro to 20 row entries
> from
> > > 260
> > > > > > > rows -
> > > > > > > > > If I
> > > > > > > > > > gave each row in column B a code number as follows A1 to
> A10
> > > and
> > > > > > > through
> > > > > > > > > the
> > > > > > > > > > alphabet to Z1:10 (thats why we have 260 rows) through
> the
> > > week
> > > > > after
> > > > > > > > > > sorting the data each day they obviously end up in
> different
> > > rows

Come on lads!

Its it really necessary for all exchanging posters to resend all the preceding
posts *every* time. Yeah I know you want things in context, but when it's just
re-re-re-repeating what all have just read, or just chat (not about excel) isn't
it a bit wasteful of bandwidth? How about editing a bit before replying? This is
the best newsgroup; let's keep the standard up!

> > > > > > >
> > > > > > > >
> > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > >

!!

Mully,

You have to have an empty cell somewhere in B3:B32

--
HTH

Bob Phillips

"mully" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> The following is the code as entered - before put new code in was working
> perfectly
> using A/B/C
>
> Sub Button3_Click()
>
>
> Dim i As Long
> Dim j As Long
> Dim ary
>
> ary = Array(0, 5, 14, 23)
> For j = 1 To 3
> For i = 1 To 10
> Cells(33 + (j - 1) * 10 + i, "A").Value = _
> Cells(i, ary(j)).Address(False, False)
> Next i
> If j = 11 Then j = 18
> Next j
> For i = 3 To 32
>
> j = Application.Match(Cells(i, "B").Value, Range("A34:A63"), 0)
> Cells(i, "B").Resize(, 11).Copy Cells(33 + j, "B")
> Next i
>
> End Sub
>
> where VBA shows error on line below
>
> j = Application.Match(Cells(i, "B").Value, Range("A34:A63"), 0)
>
> What 'am I missing tried changing everything still throws Type Mis match
> "error 13"
>
> Cheers Mully

[email protected] shared this with us in microsoft.public.excel.newusers:

> Come on lads!
>
> Its it really necessary for all exchanging posters to resend all the
> preceding posts every time. Yeah I know you want things in context,
> but when it's just re-re-re-repeating what all have just read, or
> just chat (not about excel) isn't it a bit wasteful of bandwidth?
> How about editing a bit before replying? This is the best newsgroup;
> let's keep the standard up!
>
> > > > > > > >
> > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > >
>
> !!

And how about the top-quoting/bottom-quoting discussion.
http://www.netmeister.org/news/learn2quote.html

--
Amedee Van Gasse

G'day there Amadee,

> And how about the top-quoting/bottom-quoting discussion.
> http://www.netmeister.org/news/learn2quote.html

Hey!! That sounds familiar. I've often been accused of quoting
from my bottom =)


See ya
--
Ken McLennan
Qld, Australia

Ken McLennan shared this with us in microsoft.public.excel.newusers:

> G'day there Amadee,

It's AmEdee with an E in the middle, not AmAdee ;-)

--
Amedee Van Gasse

G'day there AmEdee,

> It's AmEdee with an E in the middle, not AmAdee ;-)

Sorry about that. It's the result of typing with my eyes closed =)

See ya
--
Ken McLennan
Qld, Australia