Frank or Any other help

bloubul

bloubul

Member
Frank or Any other help.
How far can an excel macro be pushed, I have created the following macro using excels macro creating facility, but there is “ONE” flaw with this macro…. And this is where I need expert help.

As the macro stands at this moment it is static…. No more use of it….

What I need to add to this macro is a function to keep the macro a live for future use.

The macro starts with “Range("BB511:BF511").Select”, which is correct.
Now for the next time it must start with “Range("BB512:BF512").Select. and increase all other Ranges with 1, so the next time increase again with 1, only on the PowerBall Sheet. The Ranges on Sheets("3 Draws").Select DOES not change what so ever that remains “STATIC”

Can it be done? Here is the full macro.

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Sheets("PowerBall").Select
Range("BB511:BF511").Select
Selection.Copy
Sheets("3 Draws").Select
Sheets("3 Draws").Name = "3 Draws"
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Range("BB512:BF512").Select
Application.CutCopyMode = False
Selection.Copy
Range("BD514").Select
Sheets("3 Draws").Select
Sheets("3 Draws").Name = "3 Draws"
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Sheets("PowerBall").Name = "PowerBall"
Range("BB513:BF513").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Range("BB514:BF514").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Range("BB515:BF515").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Range("BB516:BF516").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Range("BB517:BF517").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveWindow.SmallScroll Down:=12
Sheets("PowerBall").Select
Range("BB518:BF518").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("PowerBall").Select
Range("BB519:BF519").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3 Draws").Select
Range("B46").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("G43").Select
ActiveWindow.SmallScroll Down:=-27
End Sub

BlouBul :cool:
 
Frank

Frank

Member
Bloubul, before we get into this macro, I think we have unfinished business with the earlier macro you posted, with the absences and resume modifications for pairs triples and quads which I spent time converting and advising you on how to make it work. You have not reported back on whether you successfully got this working. I deal with one thing at a time, as my time is valuable and I prefer not to waste it dealing with repeated unfinished projects requested by the same person.

Also in your latest macro, you do not state whether the powerball data has draw numbers alongside the data. this is the KEY to making sure the macro moves down next time (presumably after youve added a new result -again you don't say.) You also don't say whether the new result is added to the top or bottom of the list, whether you insert a new row at the top to make room for a new result. Or are you looking at a block of results in the middle of a list of results? All these things affect where the macro starts off in future runs.

Thank you.
 
Frank

Frank

Member
Whilst I await more information, this version does what your recorded macro does, but its still static on the powerball page because I need more information as above.

Sub transpose_results()

Dim numrows, numcolumns, row, nr, nc, i As Integer

Sheets("3 Draws").Select
row = Range("B6").row
Sheets("powerball").Select
nr = Range("BB511").row
nc = Range("BB511").Column


For i = 0 To 8

Sheets("powerball").Select
Cells(nr + i, nc).Select 'first row of results to copy, then the second, then the third etc.
numrows = Selection.Rows.Count
numcolumns = Selection.Columns.Count
Selection.Resize(numrows, numcolumns + 4).Select 'select all the current result

Application.CutCopyMode = False
Selection.Copy

' writing a transposed copy
Sheets("3 Draws").Select
Cells(row + 5 * i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True 'paste vertically those results in incrementing blocks downwards.
Application.CutCopyMode = False
Next

End Sub
 
bloubul

bloubul

Member
Hi Frank

My apologies for not coming back to you earlier about our first macro of GillesD..
It was a bit of a problem to use the Name Define function in excel as I have
never used it before, but after a few trails I got it working.
As far as the macro of the Rèsumè is concerned I just thought that it will be a nice to have, as I have the 6/49 version which includes the Rèsumè, otherwise the macro's are performing as they should.. Thank you.

As far as the new macro is concerned, Yes there will be draws added after
each draw has taken place thus the macro must be able the adjust to the drawings of the PowerBall please...
As far as Sheets("3 Draws") everything remains STATIC

BlouBul :cool:
 
Frank

Frank

Member
I need to know .

Are you adding a new draw to the bottom of the list, so that the macro is ALWAYS reading the last 8 draws ?

I've aready asked this, I'll,ask, AGAIN . Do the results have draw numbers? Are they numbered with the highest draw number at the bottom of the list? If so what column holds the draw numbers ?
How many rows of results have you currently in your list, what row does your first result start on?
Is the rest of the spreadsheet below the results empty?

If you cannot give me some clues as to the structure of your results list then I will be unable to help you!
 
Frank

Frank

Member
I almost forgot:-
Thank you for getting back to me on the previous macro. It is very important you learn and understand about named ranges. Macros can talk to named ranges, which can follow that named range even if the cell is moved, for example by adding rows or columns to your sheet, the macro no longer needs its address, it can find it by name.
 
bloubul

bloubul

Member
Hi Frank
I hope that I express myself correctly with the following information for you.
The spreadsheet is made up like this.
Row1. A1 to DZ1 contains some headers.
Row2. A2 to DZ2 contains headers.
Column A3 to A510 is draw number. (Oldest to latest)
Column B3 to B510 is the Draw Date and Day.
Column C3 to H510 contains the draw numbers and powerball number.
Column I3 to BA510 contains the last draw the number was drawn.
Column BB3 to BF510 is the Occurrences formula of a number in 3 draws and that values must be copied to the 3 Draws sheet.
A New PowerBall draw is added all the time. Yes draws are added, every Tuesday and Friday.
Now the 8 Occurrence values must be copied to the 3 Draws sheet.

The previous macro's results are spot on. Thank You

I hope I have addressed the Info you required
BTW what is your websites name please

BlouBul :cool:
 
Frank

Frank

Member
Having read all the information, at the time of writing, the latest draw number is draw number 508 located in cell A510. Can you confirm that the macro you supplied still wants to begin with access to a row of occurences located in cells BB511 to BF511, or has this moved down since this thread started ?

basically it appears to me that your copying of ocurrences begins on the row after the row containing highest draw number. Can you confirm this?
 
bloubul

bloubul

Member
Hi Frank

Yes you are correct.. nothing has moved. The last draw number 508 is cell A510

Thanks for your website.

BlouBul :cool:
 
Frank

Frank

Member
Ok, the first thing you need is a cell on the powerball sheet which always contains the value of the most recent draw number. It doesn't matter where it is as long as its out of the way of existing used cell ranges and isnt going to be inside the range of a formula, so nothing is adding it in or counting it, it therefore cannot be in column A. On my mock up of your sheet, I used cell EA3 and filled in a header above it labelling it "most recent draw". I'm assuming column A only contains draw numbers and there is nothing lower down the column (below the draw numbers) containing numbers.

Inside that cell I typed in the formula =MAX(A:A).

You have already had a go at naming a range so I'm going to ask you to do it again. Name this cell "maxdrawno" (you don't need the quotes). The macro needs to find this cell by name.

Now all you need is this updated version of the macro I gave you earlier, use it instead of the old one. It contains a variable x which knows how far down the powerball sheet to start copying data. x works this out from the highest draw number. It will therefore move all the copying down by one row when you add a new draw and enter its number.

Sub copy_occurrences()

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("BB3").Select
Selection.Offset(x, 0).Select

nr = Selection.row
nc = Selection.Column


For i = 0 To 8

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 + 4).Select 'select all the occurrences in the row.

Application.CutCopyMode = False
Selection.Copy

Sheets("3 Draws").Select
Cells(row + 5 * i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Next

End Sub

Let me know how you get on. Good luck !
 
bloubul

bloubul

Member
Frank

Thank you so much, I had a run with the macro and it looks fine, but I will have a full look over the weekend.

Thanks for your web address I will also visit it over the weekend as well.

BlouBul :cool:
 
bloubul

bloubul

Member
Hi Frank

Linking buttons to the macro... that's very easy have done it many times before. Thanks again for your help.

BlouBul :cool:
 
Frank

Frank

Member
On the home page, below the title banner, a message appears " about lotterygen", then it fades, then reappears. There's your link.;)
 
You must log in or register to reply here.

Sidebar

Top