I have what seems to be an easy problem which I am unable to solve....

I have a matrix, lets say

A B C D
E F G H
I J K L

and I want to extrat every 6th element reading from left to right, i.e. F, L
etc.....

Any ideas? I though I could put the matrix into a vertical series, i.e.
A
B
C
D
etc....
and then choose every 6th number but I can't even get that far!!!!

Thanks for any help....

Try this macro! Adjust values for mrows and mcols to suit your matrix sizes!

Sub element6()
Dim c6 As Range
mrows = 3
mcols = 4
For melem = 1 To mrows
Range(Cells(melem, 1), Cells(melem, mcols)).Select
Selection.Copy
Cells((melem - 1) * mcols + 1, mcols + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next melem
Application.CutCopyMode = False
ccounter = 0
For Each c6 In Range(Cells(1, mcols + 1), Cells(mrows * mcols, mcols + 1))
If c6.Row Mod 6 = 0 Then
ccounter = ccounter + 1
Cells(ccounter, mcols + 2).Value = c6.Value
End If
Next c6
End Sub

Regards,
Stefi


€žAlex Rauket€� ezt Ã*rta:

I have what seems to be an easy problem which I am unable to solve....

I have a matrix, lets say

A B C D
E F G H
I J K L

and I want to extrat every 6th element reading from left to right, i.e. F, L
etc.....

Any ideas? I though I could put the matrix into a vertical series, i.e.
A
B
C
D
etc....
and then choose every 6th number but I can't even get that far!!!!

Thanks for any help....

I'm sorry but I'm not familiar with using macros in excel. How would I
implement this trick?

Thanks

"Stefi" wrote:

Try this macro! Adjust values for mrows and mcols to suit your matrix sizes!

Sub element6()
Dim c6 As Range
mrows = 3
mcols = 4
For melem = 1 To mrows
Range(Cells(melem, 1), Cells(melem, mcols)).Select
Selection.Copy
Cells((melem - 1) * mcols + 1, mcols + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next melem
Application.CutCopyMode = False
ccounter = 0
For Each c6 In Range(Cells(1, mcols + 1), Cells(mrows * mcols, mcols + 1))
If c6.Row Mod 6 = 0 Then
ccounter = ccounter + 1
Cells(ccounter, mcols + 2).Value = c6.Value
End If
Next c6
End Sub

Regards,
Stefi


€žAlex Rauket€� ezt Ã*rta:

I have what seems to be an easy problem which I am unable to solve....

I have a matrix, lets say

A B C D
E F G H
I J K L

and I want to extrat every 6th element reading from left to right, i.e. F, L
etc.....

Any ideas? I though I could put the matrix into a vertical series, i.e.
A
B
C
D
etc....
and then choose every 6th number but I can't even get that far!!!!

Thanks for any help....

This is an improved version:

Sub element6_2()
Dim c6 As Range, topleft As Range, bottomright As Range
Set topleft = Range("E15") 'replace E15 by cell reference of the top
left cell of the matrix
mrows = 5 'replace 5 by No of rows of the matrix
mcols = 4 'replace 4 by No of columns of the matrix
Set bottomright = Cells(topleft.Row + mrows - 1, topleft.Column + mcols
- 1)
ccounter = 0
c6counter = 0
For Each c6 In Range(topleft, bottomright)
ccounter = ccounter + 1
If ccounter Mod 6 = 0 Then
c6counter = c6counter + 1
Cells(topleft.Row + c6counter - 1, topleft.Column + mcols).Value
= c6.Value
End If
Next c6
End Sub

To implement macro:
Press Alt+F11 (Microsoft Visual Basic window appears)
If you don't see Project window on the left, press Ctrl+R
Right click on VBAProject(yourxlsfilename)
Choose InsertModule from the local menu, yourxlsfilename - appears
Copy macro code into Module1(Code) window

To run macro:
Make sure the cursor is inside the macro code and press F5
You can also assign a hot key to the macro in Excel's Alt+F8Options.

Regards,
Stefi



€žAlex Rauket€� ezt Ã*rta:

I'm sorry but I'm not familiar with using macros in excel. How would I
implement this trick?

Thanks

"Stefi" wrote:

Try this macro! Adjust values for mrows and mcols to suit your matrix sizes!

Sub element6()
Dim c6 As Range
mrows = 3
mcols = 4
For melem = 1 To mrows
Range(Cells(melem, 1), Cells(melem, mcols)).Select
Selection.Copy
Cells((melem - 1) * mcols + 1, mcols + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next melem
Application.CutCopyMode = False
ccounter = 0
For Each c6 In Range(Cells(1, mcols + 1), Cells(mrows * mcols, mcols + 1))
If c6.Row Mod 6 = 0 Then
ccounter = ccounter + 1
Cells(ccounter, mcols + 2).Value = c6.Value
End If
Next c6
End Sub

Regards,
Stefi


€žAlex Rauket€� ezt Ã*rta:

I have what seems to be an easy problem which I am unable to solve....

I have a matrix, lets say

A B C D
E F G H
I J K L

and I want to extrat every 6th element reading from left to right, i.e. F, L
etc.....

Any ideas? I though I could put the matrix into a vertical series, i.e.
A
B
C
D
etc....
and then choose every 6th number but I can't even get that far!!!!

Thanks for any help....

I was thinking a little bit and realized that the job can be done without VBA:

Being your matrix in columns A:D, enter formula
=ROW()*6 (6 represents looking for every 6th element)
in E1, drag it down as necessary (in your example to row 3)
enter No of columns of matrix in F1 (in your example 4)
enter formula
=INDIRECT(ADDRESS(CEILING(E1/$F$1,1),IF(MOD(E1/$F$1,1)0,MOD(E1/$F$1,1),1)*4,4,1))
in G1, drag it down as necessary (in your example to row 3)
It will return F L R in G1:G3

Regards,
Stefi


€žStefi€� ezt Ã*rta:

This is an improved version:

Sub element6_2()
Dim c6 As Range, topleft As Range, bottomright As Range
Set topleft = Range("E15") 'replace E15 by cell reference of the top
left cell of the matrix
mrows = 5 'replace 5 by No of rows of the matrix
mcols = 4 'replace 4 by No of columns of the matrix
Set bottomright = Cells(topleft.Row + mrows - 1, topleft.Column + mcols
- 1)
ccounter = 0
c6counter = 0
For Each c6 In Range(topleft, bottomright)
ccounter = ccounter + 1
If ccounter Mod 6 = 0 Then
c6counter = c6counter + 1
Cells(topleft.Row + c6counter - 1, topleft.Column + mcols).Value
= c6.Value
End If
Next c6
End Sub

To implement macro:
Press Alt+F11 (Microsoft Visual Basic window appears)
If you don't see Project window on the left, press Ctrl+R
Right click on VBAProject(yourxlsfilename)
Choose InsertModule from the local menu, yourxlsfilename - appears
Copy macro code into Module1(Code) window

To run macro:
Make sure the cursor is inside the macro code and press F5
You can also assign a hot key to the macro in Excel's Alt+F8Options.

Regards,
Stefi



€žAlex Rauket€� ezt Ã*rta:

I'm sorry but I'm not familiar with using macros in excel. How would I
implement this trick?

Thanks

"Stefi" wrote:

Try this macro! Adjust values for mrows and mcols to suit your matrix sizes!

Sub element6()
Dim c6 As Range
mrows = 3
mcols = 4
For melem = 1 To mrows
Range(Cells(melem, 1), Cells(melem, mcols)).Select
Selection.Copy
Cells((melem - 1) * mcols + 1, mcols + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next melem
Application.CutCopyMode = False
ccounter = 0
For Each c6 In Range(Cells(1, mcols + 1), Cells(mrows * mcols, mcols + 1))
If c6.Row Mod 6 = 0 Then
ccounter = ccounter + 1
Cells(ccounter, mcols + 2).Value = c6.Value
End If
Next c6
End Sub

Regards,
Stefi


€žAlex Rauket€� ezt Ã*rta:

I have what seems to be an easy problem which I am unable to solve....

I have a matrix, lets say

A B C D
E F G H
I J K L

and I want to extrat every 6th element reading from left to right, i.e. F, L
etc.....

Any ideas? I though I could put the matrix into a vertical series, i.e.
A
B
C
D
etc....
and then choose every 6th number but I can't even get that far!!!!

Thanks for any help....