Welcome Guest
You last visited June 29, 2017, 11:43 am
All times shown are
Eastern Time (GMT-5:00)

Macro Lessons??

Topic closed. 8 replies. Last post 11 years ago by lottaloot.

 Page 1 of 1

United States
Member #17834
June 28, 2005
2083 Posts
Offline
 Posted: January 28, 2006, 6:04 am - IP Logged

Hello all,

I know we have had a lot of people ask excel and VB questions.

I am trying to learn macro's to make things easier with a automated task.

I'm a little confused on how to go about doing this.

Here is what I have:

In Cell A1 going down I have the folling:

0987 0986 0985 0984 0983 0982 0981 0976 0975 0974 0973 0972 0971 0965 0964 0963 0962 0961 0954 0953 0952 0951 0943 0942 0941 0932 0931 0921 0876 0875 0874 0873 0872 0871 0865 0864 0863 0862 0861 0854 0853 0852 0851 0843 0842 0841 0832 0831 0821 0765 0764 0763 0762 0761 0754 0753 0752 0751 0743 0742 0741 0732 0731 0721 0654 0653 0652 0651 0643 0642 0641 0632 0631 0621 0543 0542 0541 0532 0531 0521 0432 0431 0421 0321

In cell B1 I have the following going down:

 0789 0798 0879 0897 0987 0978 7089 7098 7890 7809 7980 7908 8079 8097 8790 8709 8907 8970 9078 9087 9780 9708 9870 9807

As you can see this is the first number in the list. The list in Cell B going down is all 24 combination that you can get in the pick4.

Now I recorded a macro already and it works but, I need to modify it so it will take each set of 4 #'s in A1,A2,A3 ect. and put it in B1 or next available cell skipping a cell block inbetween.

Example:

A1 has the list. B1 has the first 24 combo's then skip a row of cell and the next row it would start at is D1 to D24.

Well that is it. Below is the macro I wrote I just need some advise on how to change the macro to fit what I'm doing.

Sub play424waybox()
'
' play424waybox Macro
' Macro recorded 1/27/2006 by Administrator
'

'
ActiveCell.FormulaR1C1 = "0369"
RaPge("P26").Select
ActiveCell.FormulaR1C1 = "0396"
RaPge("P27").Select
ActiveCell.FormulaR1C1 = "0639"
RaPge("P28").Select
ActiveCell.FormulaR1C1 = "0693"
RaPge("P29").Select
ActiveCell.FormulaR1C1 = "0936"
RaPge("P30").Select
ActiveCell.FormulaR1C1 = "0963"
RaPge("P31").Select
ActiveCell.FormulaR1C1 = "3069"
RaPge("P32").Select
ActiveCell.FormulaR1C1 = "3096"
RaPge("P33").Select
ActiveCell.FormulaR1C1 = "3609"
RaPge("P33").Select
ActiveCell.FormulaR1C1 = "3690"
RaPge("P35").Select
ActiveCell.FormulaR1C1 = "3906"
RaPge("P36").Select
ActiveCell.FormulaR1C1 = "3960"
RaPge("P37").Select
ActiveCell.FormulaR1C1 = "6039"
RaPge("P38").Select
ActiveCell.FormulaR1C1 = "6093"
RaPge("P39").Select
ActiveCell.FormulaR1C1 = "6309"
RaPge("P40").Select
ActiveCell.FormulaR1C1 = "6390"
RaPge("P41").Select
ActiveCell.FormulaR1C1 = "6903"
RaPge("P42").Select
ActiveCell.FormulaR1C1 = "6930"
RaPge("P43").Select
ActiveCell.FormulaR1C1 = "9036"
RaPge("P44").Select
ActiveCell.FormulaR1C1 = "9063"
RaPge("P45").Select
ActiveCell.FormulaR1C1 = "9306"
RaPge("P46").Select
ActiveCell.FormulaR1C1 = "9360"
RaPge("P47").Select
ActiveCell.FormulaR1C1 = "9603"
RaPge("P48").Select
ActiveCell.FormulaR1C1 = "9630"
RaPge("P49").Select
EPd Sub

There is 10,000 numbers so a macro would be a great thing to use.

Any help would be great!!!

Thank you

PP

Good luck to everyone!!!

Switching between Fairfax, VA and Belgium
Belgium
Member #19287
July 29, 2005
2254 Posts
Offline
 Posted: January 28, 2006, 7:26 am - IP Logged

I don't think there are 10000 columns available, Powerplayer...
It will loop until it finds an empty cell in column A.
(i have this in a proceducre somewhere where an array is used to create the 24-ways, but i can't seem to locate it lol)

Private sub sbMake24Way()

Dim iRows As Integer
Dim iWCol As Integer
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim s4 As String
Dim blnEnd As Boolean

iWCol = 2
iRows = 1

Do Until blnEnd
'Dummy variable, blnEnd (not needed in this setup ofcourse... just for the loop)
If trim(Cells(iRows, 1)) = "" Then Exit Do

s1 = Mid(Cells(iRows, 1), 1, 1)
s2 = Mid(Cells(iRows, 1), 2, 1)
s3 = Mid(Cells(iRows, 1), 3, 1)
s4 = Mid(Cells(iRows, 1), 4, 1)

Cells(1, iWCol) = s1 & s2 & s3 & s4
Cells(2, iWCol) = s1 & s2 & s4 & s3
Cells(3, iWCol) = s1 & s3 & s2 & s4
Cells(4, iWCol) = s1 & s3 & s4 & s2
Cells(5, iWCol) = s1 & s4 & s2 & s3
Cells(6, iWCol) = s1 & s4 & s3 & s2

Cells(7, iWCol) = s2 & s1 & s3 & s4
Cells(8, iWCol) = s2 & s1 & s4 & s3
Cells(9, iWCol) = s2 & s3 & s1 & s4
Cells(10, iWCol) = s2 & s3 & s4 & s1
Cells(11, iWCol) = s2 & s4 & s1 & s3
Cells(12, iWCol) = s2 & s4 & s3 & s1

Cells(13, iWCol) = s3 & s1 & s2 & s4
Cells(14, iWCol) = s3 & s1 & s4 & s2
Cells(15, iWCol) = s3 & s2 & s1 & s4
Cells(16, iWCol) = s3 & s2 & s4 & s1
Cells(17, iWCol) = s3 & s4 & s1 & s2
Cells(18, iWCol) = s3 & s4 & s2 & s1

Cells(19, iWCol) = s4 & s1 & s2 & s3
Cells(20, iWCol) = s4 & s1 & s3 & s2
Cells(21, iWCol) = s4 & s2 & s1 & s3
Cells(22, iWCol) = s4 & s2 & s3 & s1
Cells(23, iWCol) = s4 & s3 & s1 & s2
Cells(24, iWCol) = s4 & s3 & s2 & s1

iWCol = iWCol + 1
iRows = iRows + 1
Loop

End Sub

Switching between Fairfax, VA and Belgium
Belgium
Member #19287
July 29, 2005
2254 Posts
Offline
 Posted: January 28, 2006, 7:34 am - IP Logged

You want it to skip a column each time?

Then change this line: iWCol = iWCol + 1

to : iWCol = iWCol + 2

Then you'll need 20 000 columns...

United States
Member #17834
June 28, 2005
2083 Posts
Offline
 Posted: January 28, 2006, 9:26 am - IP Logged

You want it to skip a column each time?

Then change this line: iWCol = iWCol + 1

to : iWCol = iWCol + 2

Then you'll need 20 000 columns...

WOW thank you Paurths..this is great!!

Something to do at work tonight...

United States
Member #17834
June 28, 2005
2083 Posts
Offline
 Posted: January 28, 2006, 9:34 am - IP Logged

Paurths,

I got another question if you can answer. can you explain what the different code means so I can undstand this more.

Trying to learn...

Like what does iw and things like this....

Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim s4 As String

the s1 what is this line doing..sorry for the question but, if I understand the code then I will be able to write it.

Thanks

PP

Chief Bottle Washer
New Jersey
United States
Member #1
May 31, 2000
23720 Posts
Online
 Posted: January 28, 2006, 9:46 am - IP Logged

I wouldn't do this kind of thing with a macro.  It's much easier (and more re-usable) to do it using formulas.  If you use formulas, then you can quickly apply the formulas to a large range of cells using copy and paste.

Many people don't realize how powerful Excel formulas can be.  You can use any VBA functions in your formula, including some pretty powerful string manipulation functions.

Why not create cells b1, c1, d1, and e1 as each of the four digits?  For example, cell b1 would contain the formula =mid(\$a1,1,1), cell c1 would contain =mid(\$a1,2,1), and so on for d1 and e1.

Then, you can create formulas in cells to the right containing allo of the different combinations:  for example, f1 could contain =b1&c1&d1&e1.

After creating your 24 columns, you simply highlight cells b1 to the last cell on the right, and "grab" the small square "handle" on the lower-right of the selection and drag it down to copy the formulas.

Macros can be a good tool, but IMHO should only be used when formulas are not feasible, because macros are harder to maintain, and formulas are much easier to use the next time (you simply plug in the new numbers, and never need to "run" anything).  It's also easier to share spreadsheets that use formulas.

BTW, that handle-dragging tip for copying the formulas is a great tool to use for a variety of copying tasks.

For example, if you want to fill a range with numbers, just type the first number, then grab and drag that handle down, holding down either no keys, or the shift, Ctrl, or Alt keys to change what happens when it copies.

Check the State Lottery Report Card

Sign the Petition for True Lottery Drawings
Help eliminate computerized drawings!

United States
Member #17834
June 28, 2005
2083 Posts
Offline
 Posted: January 28, 2006, 9:52 am - IP Logged

Awesome TODD!!! The drag trick I learn from you and use it all the time. Thank you

I will taket the advise of useing a formula instead. This was actually only to make the chart. So the macro would have only been used and save if needed again. Once it's done creating this chart of all the combinations I'm going to use my other formula that I have with the pick 3 and apply it to this....

Thank you again........work will be fun tonight..

PP

Pennsylvania
United States
Member #2218
September 1, 2003
5545 Posts
Offline
 Posted: January 30, 2006, 4:20 pm - IP Logged

Powerplayer,

Still need the Pick 4 listing??

wsls

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: January 30, 2006, 4:53 pm - IP Logged

Paurths, I love your code.  Thanks for posting it.  Excel is a pwerful thing.

Powerplayer, good luck!!

L ttaL   T

 Page 1 of 1