Macro putting data in wrong column

Question

I have a Macro and its putting the data on the next sheet in the wrong column, can someone tell me why and how to fix it. 

If you look at the sheet “Lotto” you will see after the last draw I have colored rows BG1171:BL1179, that’s what the code must copy and transpose to sheet “3 Draws” in column B6:B54, see the color numbers in column D6:D54, but instead it copies rows BG1162:BL1172

.

The code must always copy the last 9 rows and transpose to 3 Draw sheet.

DT Testing

Thank You

Here is the Macro:

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("Lotto").Select

Range("BG5").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("Lotto").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

Answer 1

Just as a thought, and to save the complication of a macro, could the data you need in the sheet '3 Draws' be achieved with the use of a formula?

To do this copy the following formula to cell B6 in the '3 Draws' sheet and then copy down the table, the count of occurrences that the number in column A appears in the last three draws is calculated.

=COUNTIF(INDIRECT("Lotto!$C$"&Maxdrawno&":$H$"&Maxdrawno + 2),A6)

As more rows of draws get added, so the range returned for the last three rows will move dynamically. This is because the last draw number (in the named range Maxdrawno) is coincidentally the row number required for the start of the range returned in the INDIRECT part of the formula.

This would also mean that the numbers 1 through 49 at the bottom of the table could be romoved.

Just a thought...

Answer 2

Just as a thought, and to save the complication of a macro, could the data you need in the sheet '3 Draws' be achieved with the use of a formula?

To do this copy the following formula to cell B6 in the '3 Draws' sheet and then copy down the table, the count of occurrences that the number in column A appears in the last three draws is calculated.

=COUNTIF(INDIRECT("Lotto!$C$"&Maxdrawno&":$H$"&Maxdrawno + 2),A6)

As more rows of draws get added, so the range returned for the last three rows will move dynamically. This is because the last draw number (in the named range Maxdrawno) is coincidentally the row number required for the start of the range returned in the INDIRECT part of the formula.

This would also mean that the numbers 1 through 49 at the bottom of the table could be romoved.

Just a thought...

Thank you and an excellent idea!

Answer 3

Hi

The reason it appears not to be copying the last 9 rows is that there is a reference range called 'maxdrawno' which is a formula looking for the MAX number in column A.

You could offset this (if numbers 1 to 49 are always in the data with no draw number at the bottom) by adding seven to the variable x - this is the difference from the row number of the last draw and the last row number of ball data, less the 2 header rows.

x = Range("maxdrawno").Value + 7

You would also need to consider the formulae in the Lotto table to work on the last three draws, not the last three rows as it does at the moment.

HTH