Well, now I can run Excel again, I can look at this..
Bloubul, for the record you did not supply enough information for anyone else other than me to deal with this request. After all , I wrote the macro you supplied. The macro refers to a
named range on your sheet which you never mentioned. You do not tell us what lottery you are analysing (hence the logical name for the page where the results are), the existing macro assumes the results are stored on a page called "
powerball" and will only work when that page name is used. You do not say where on the 3 draws page you want to put the results.
I see you have made the following structural changes to your data set from the last version designed for powerball.
1. The results have six balls (not five) to be analysed.
2. The occurences formulas have been moved further to the right from the previous version.
3. You have more results to analyse
4. You wish the 9 last ocurrence formulas to be exactly lined up with the last 9 results.
To achieve this, the macro has had to be amended to recognise
all these changes.
Sub copy_occurrences_sixball()
Dim numrows, numcolumns, row, nr, nc, i, x As Integer
Sheets("3 Draws").Select
row = Range("B6").row
x = Range("maxdrawno").Value
Sheets("powerball").Select
Range("BG3").Select 'datum point for pasting results lower down
Selection.Offset(x - 9, 0).Select ' find first ocurrence to copy
nr = Selection.row
nc = Selection.Column
For i = 0 To 8 ' copy nine times row by row
Sheets("powerball").Select
Cells(nr + i, nc).Select 'first row of results to copy
numrows = Selection.Rows.Count
numcolumns = Selection.Columns.Count
Selection.Resize(numrows, numcolumns + 6).Select 'select all results on this row
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Cells(row + 6 * i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True 'paste the row of occurrences
Application.CutCopyMode = False
Next 'row
End Sub
Check this out and see if it does what you want it to do. Warning, if you change the name of the page where the results are, you will also have to change the name mentioned in the macro ....Sheets("powerball").Select to match the new name (in two places).
The macro will fail if you have less than 9 results in your table.
