Macros in excel

Reply #62

time*treat — Wed, 01 Feb 2006 00:56:08 GMT

in the visual basic editor, under the file menu

Reply #61

lottaloot — Tue, 31 Jan 2006 15:49:13 GMT

Thanks Time*treat

Where is export located in excel

Reply #60

time*treat — Tue, 31 Jan 2006 05:30:20 GMT

Some of you have figured out by now that with minor changes, a macro used in one place can be useful in another. So that you don't have to open every workbook you've ever worked on to find that one that did that thing, use the export feature to create a *.bas file (a copy of just the code). You can then import it into your new workbook (again, just a copy). Give it a descriptive name. Add comments to the top of the code. Spare yourself some grief.

Reply #59

time*treat — Wed, 25 Jan 2006 07:00:35 GMT

Is anyone thinking about gathering all this code we've been creating and putting it all in one spot? Seems we'll have enough for a book shortly. Be useful to have it organized. Not much of an html person myself.

Reply #58

powerplayer — Thu, 12 Jan 2006 12:11:55 GMT

Hi Hyper,I have a question about this formula.After I put the formula in the cell it doesn't give me a answer...it just says there in the cell (=INDEX(Q$1:Z$1,MATCH(AA3,Q3:Z3,0)I know you have to hit ctrl + shift + enter after putting in the formula but, it won't do anything.I have been having this problem for the last week or so on my spreedsheet.Any help advice from anyone would be great!!Thanks in advancePP

Reply #57

lottaloot — Thu, 05 Jan 2006 03:17:27 GMT

I am getting better. This will allow you to insert a row (or rows depending upon what you change the digit in red to) into your spreadsheet. Works well for me as I have to add 20 rows for midday 36 for the evening. Public Sub macro1()Dim lngRow As LongDim intCount As Integer lngRow = 1Do While Cells(lngRow, 1).Value <> intCount = 0 Do While intCount < 1 Rows(lngRow + 1 : lngRow + 1).Insert intCount = intCount + 1 Loop lngRow = lngRow + 4Lo... [ More ]

Reply #56

lottaloot — Tue, 27 Dec 2005 20:30:51 GMT

Just catching up on what I missed while I was away. Pretty neat formulas, Hyper.

Reply #55

hypersoniq — Tue, 27 Dec 2005 01:36:24 GMT

you'll see in the blog that the PA pick4 is done backtesting... 11 straight hits in 25 yearsmight not sound like alot, but at 5,000 a pop, it beats the system by over 40 grand!your MASS data is next in the cue, because I need to test the system on other data anyway, why not make it Mass.? I tend to get a feeling from the nature of some posts that Mass is one tough nut to crack.

Reply #54

retxx — Tue, 27 Dec 2005 01:32:49 GMT

hyper I am waiting with baited breath. Please give priority to it and inform me while I check your blog for progress. thanks again.

Reply #53

hypersoniq — Mon, 26 Dec 2005 19:02:11 GMT

you already sent me that sheet, I sent it back to you adding Even/Odd,v-trac and High/Low.I am developing a straight shooter single combo system for PA pick 3 and pick 4. Pick 3 backtesting is currently PASSING on all but the cyclic hit rate, once that is solved I will be moving it to PA big4, then I will apply it to that MASS sheet you sent. It will analyze ALL draws and give you 1 and only 1 number to play for the next night. All you will have to do is add draws as they happen to the bottom of... [ More ]

Reply #52

hypersoniq — Mon, 26 Dec 2005 19:01:48 GMT

Reply #51

retxx — Sun, 25 Dec 2005 02:58:03 GMT

hyper can i send you my pick 4 list of past draws for Mass. to see what you can do with it in an excel spreadsheet. It would be interesting to watch you progress along .thanksp s it goes back to 1975.

Reply #50

hypersoniq — Sat, 24 Dec 2005 18:47:39 GMT

sometimes I forget things I knew in excel, but didn't use in ages...the unsorted lookup works when used differently, I didn't need VBA at all yet for this new sheethere is the formula=INDEX(Q$1:Z$1,MATCH(AA3,Q3:Z3,0))Q1:Z1 =digits 0 thru 9Q3:Z3 equals the number of times a digit (in Q1:Z1) followed a particular precursor/draw combo at that point in historyAA3 = LARGE(Q3:Z3,1)so, the index formula finds the largets number of announcer occurrences AT THAT POINT from each of the 10 digits and ret... [ More ]

Reply #49

hypersoniq — Thu, 22 Dec 2005 11:01:56 GMT

here's a challenge I am faced with, a pick3 sheet has a list of the digits across 10 columns (0-9), underneath it has a count of how many times the numbers in each of those columns followed the previous 2 draws. I need to do a lookup in the row for each draw to return the digit most frequent, but LOOKUP() returns bad data because the rows underneath are not sorted.I will most likely need to go to vba to solve this one, I will post the code after I figure out the logic.

Reply #48

powerplayer — Thu, 22 Dec 2005 10:02:07 GMT

lottaloot,WTG....You have blown my mind away with all this new Excel and VB stuff.I'm still trying to catch up on my lesson I was doing last week.WOW this stuff just keeps on going...there is no end.....I'm not sure what the coding does?? Can u give a brief description of what this does or should do??Powerplayer

Reply #47

lottaloot — Tue, 20 Dec 2005 03:44:13 GMT

Retxx, I did not create the spreadsheet. You asked if anyone had a sheet that you could put your pick 4 in. I sent you one. I explained a few posts back how I was informed to add rows for draw results to a sheet that was graciously given to me.All I want to do right now is pack my bags, drink my beer, and chill right here in this spot surfing the net. Please allow me to do that. You will have to create you a back-up copy fiddle around with it like I do.

Reply #46

retxx — Tue, 20 Dec 2005 03:28:14 GMT

lottaloot I am sending you the excel program we talked about to see if you recall anywhere on it that allows for possible plays,and could you answer my question as to moving the entire row down so it gives me the top row blank to add current draw. thanks

Reply #45

retxx — Tue, 20 Dec 2005 03:26:32 GMT

Reply #44

lottaloot — Tue, 20 Dec 2005 03:24:17 GMT

http://www.mindspring.com/~tflynn/excelvba.html

Reply #43

hypersoniq — Tue, 20 Dec 2005 00:23:48 GMT

looks like it takes as input a starting date and an ending date, then counts the days (rows) between the start and stop date...

Reply #42

time*treat — Tue, 20 Dec 2005 00:08:26 GMT

lotta,where did this one come from

Reply #41

lottaloot — Mon, 19 Dec 2005 16:36:16 GMT

New VBA of the day. If anyone can find a reason for adding this into their spreadsheet,please let me know how it can be used.Sub EnterDates()Columns(3).ClearDim startDate As String, stopDate As String, startCel As Integer, stopCel As Integer, dateRange As RangeOn Error Resume NextDo startDate = InputBox( Please enter Start Date: Format(mm/dd/yy) , START DATE ) If startDate = Then EndLoop Until startDate = Format(startDate, mm/dd/yy ) _ Or startDate = Format(startDate, m/d/yy )Do... [ More ]

Reply #40

hypersoniq — Mon, 19 Dec 2005 03:30:05 GMT

I always sort ascending so the oldest draws are on top and I just add new ones to the bottom of the sheet, I can usually just autofill the formulas down and as long as I anchor them properly they just update along with the new data

Reply #39

retxx — Mon, 19 Dec 2005 03:19:58 GMT

would you like me to mail it to you so you can see it

Reply #38

lottaloot — Mon, 19 Dec 2005 03:12:13 GMT

Are you refering to the one that I sent you???If so, Sirmetro set it up to where the formulas were housed only on row 2. In order to move them down, you must click on A2, hit (Ctrl+Shift+the right arrow key) then (Ctrl+ Shift+down arrow key)Right click-copy move down only one row at a time. After a while, you will need to copy the sheet with the exception of row 2 special paste only the values.There is a macro that you can run, that will create some sort of recommended plays--I think; but I... [ More ]

Reply #37

retxx — Mon, 19 Dec 2005 02:46:16 GMT

lottaloot knowing you are versed on excel please help with this question. On my spreadsheet which is filled with past draw numbers I don't know how to continue adding current numbers so they line up at the top cell. need step by step if you would. want to add the date and draw. will need to do it each day. i have excel 2000. Thanks

Reply #36

time*treat — Mon, 19 Dec 2005 00:37:00 GMT

matrices and their inverse are used, among other things, to solve simultaneous equations like6x + 3y = 66 while 7x - 2y = 11. These two equations each form a line, but intersect at one point ~ the solution.this one is easy enough to do on paper but things get tough with, say 5 variables. If all of the equations in the set don't 'fit' then the error message is a way of saying one or more of the 'lines' don't intersect with the others. for those of you playing at home, x = 5 y = 12... [ More ]

Reply #35

lottaloot — Sun, 18 Dec 2005 22:32:23 GMT

MINVERSE function returns the inverse matrix of matrix.If matrix cannot be inverted, MINVERSE returns #NUM! error.If matrix does not contain equal number of columns and rows, MINVERSE returns #VALUE! error.This function is Excel compatible. More indepth explaination of this function, anyone

Reply #34

lottaloot — Sun, 18 Dec 2005 14:51:02 GMT

I am down with tutorial classes being listed on the homepage.

Reply #33

time*treat — Sun, 18 Dec 2005 12:51:36 GMT

These syntax errors can be really subtle to the human eye. Logic errors are even worse. I once spent the better part of 24hrs trying to figure out why I was getting an error in (previously perfectly working) macro B after I made a change in macro A. Answer; I had misspelled the argument name by one letter, so the value being picked up was 0, the default, not the value I was trying to pass.Get used to making intermediate printouts as these things get more complex.Heh, after awhile the homepage

Reply #32

hypersoniq — Sun, 18 Dec 2005 03:01:39 GMT

retxx: I have nothing that actually worked yet. I am getting more and more into the 3 and 4 digit arena so maybe if I find something with PA's data that looks promising... but so far they either fail backtesting or the backtest formulas get so complex that it takes the sheet 20 minutes to recalculate on a Pentium4 :-(if you want some filters, you could send the sheet and I could whip up v-tracs or Odd/Even or any other filter you want, but as far as prediction goes, I am not very good at that...... [ More ]

Reply #31

lottaloot — Sun, 18 Dec 2005 02:56:28 GMT

It worked this time.Thanks Hyper

Reply #30

lottaloot — Sun, 18 Dec 2005 02:52:39 GMT

It seemed to me that when I tried that way earlier, I got some sort of error code had to remove that--but I will try again. Thanks

Reply #29

lottaloot — Sun, 18 Dec 2005 02:51:26 GMT

Sorry Retxx, I don't deal with the Pick 4 too much. I have a worksheet that was given to me by SirMetro a while back--but that's it.

Reply #28

retxx — Sun, 18 Dec 2005 02:47:38 GMT

lottaloot or hyper I have a pick 4 draws for Mass. going back to 1976. would either of you have an excel spreadsheet that would help selecting future pics after entering all these draws to any advantage. please help if yes. I have excel 2000.Thanks If it had filters it would be great.

Reply #27

time*treat — Sat, 17 Dec 2005 23:40:12 GMT

I'm using a 'worksheet_activate' in one of my macros, too. When I want to update that sheet, I switch to any other sheet in that workbook, then switch back.I also SET an OBJECT as the name of the workbook sheet I'm using, then I can refer to that object name for the operation.In effect, I am always using filename-sheetname-cells. That is a little more trouble than just using range or cells, but it lets you call your routine from anywhere; you may have noticed that using just cells or range tha... [ More ]

Reply #26

hypersoniq — Sat, 17 Dec 2005 21:25:05 GMT

the problem is it doesn't automatically update when you make changes, you have to re-enter the data... and copy/paste brings up a runtime error.here's a variation that highlights v-trac numbers in the A1:A10 rangePrivate Sub Worksheet_Change(ByVal Target As Range)Dim icolor As Integer If Not Intersect(Target, Range( A1:A10 )) Is Nothing Then Select Case Target Case 0 icolor = 2 Case 1 icolor = 3 Case 2 icol... [ More ]

Reply #25

hypersoniq — Sat, 17 Dec 2005 21:08:52 GMT

you are missing the bold red part, add it and it works just fine (but only when you enter data in the range that falls within the criteria)...Private Sub Worksheet_Change(ByVal Target As Range)Dim icolor As Integer If Not Intersect(Target, Range( A1:A10 )) Is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor

Reply #24

time*treat — Sat, 17 Dec 2005 18:23:07 GMT

Most sites that have code on them figure that you have outgrown the basic books and are looking to do something more esoteric. For the sake of space they leave out the 'dim' and 'option' statements. <-- in longer code, your var decs, alone, can take up pages. They figure you know to put that in. I put it in when I post here if the code is short enough, or if it is something I have written already.

Reply #23

lottaloot — Sat, 17 Dec 2005 17:14:14 GMT

Found this on www.OzGrid.com Trying to get around the conditional format limit of 3 with this VBA---Why doesn't it not work? Private Sub Worksheet_ChangeDim icolor As Integer If Not Intersect(Target, Range( A1:A10 )) Is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30... [ More ]

Reply #22

Rip Snorter — Sat, 17 Dec 2005 15:44:07 GMT

Thanks. Going there now.J

Reply #21

hypersoniq — Sat, 17 Dec 2005 15:38:23 GMT

it should be in excel '97there is some issue with seeing all of the add-in's in '97 that is addressed herehttp://support.microsoft.com/kb/157768/en-us

Reply #20

Rip Snorter — Sat, 17 Dec 2005 14:49:22 GMT

Solver's not one that shows up among my add-ins on 'Excel 97. Might be it was added sometime later.Thanks for the info hypersoniq.Jack

Reply #19

hypersoniq — Sat, 17 Dec 2005 00:28:17 GMT

speaking of add-ins... there is one called Solver just waiting to be put into the number chase...

Reply #18

Rip Snorter — Fri, 16 Dec 2005 18:20:49 GMT

Got it. Thanks powerplayer.Jack

Reply #17

powerplayer — Fri, 16 Dec 2005 18:14:49 GMT

Rip SnorterIt is a add-in item in excel.Go to tools add-ins. Then choose anylasis tools.I have excel 2002 so I don't know if this was a feature in 97 but, since it's add in I'm sure you can get it off of MS site since most new items are usually backwards compatiable. Powerplayer

Reply #16

Rip Snorter — Fri, 16 Dec 2005 18:09:04 GMT

I'm going to bite the bullet and appear exactly as stupid as I actually am.I've been trying to find Histogram.... a function.... anywhere.I looked in help and it tells all about communicating with the dialogue box.But I can't find a dialogue box, can't find anything about Histograms, how to start them without finding some dialogue box.I have Excel 97. Is it possible Histogram was some function added later than that?I've looked through tools, macros, data, views, formats, can't find any referenc... [ More ]

Reply #15

Rip Snorter — Fri, 16 Dec 2005 17:11:09 GMT

Thanks.Jack

Reply #14

time*treat — Fri, 16 Dec 2005 17:03:57 GMT

put them all in one column and histogram will do just that

Reply #13

Rip Snorter — Fri, 16 Dec 2005 16:41:16 GMT

Thanks for the explanation. I gather a macro would allow me to take a group of numbers similar to this one:3 6 7 18 19 22 23 25 27 39 40 41 47 5019 20 23 25 26 28 29 34 35 39 44 45 47 631 10 13 16 18 26 27 30 39 44 47 51 55 642 7 13 24 28 30 38 39 40 44 46 49 53 555 7 8 21 31 38 41 46 50 52 59 60 67 691 6 8 14 15 20 29 32 37 38 40 48 52 561 13 17 18 24 33 35 36 43 46 48 50 54 551 3 4 9 10 24 26 30 33 36 42 44 45 495 6 7 15 23 29 36 39 40 42 46 51 52 572 6 15 18 20 21 26 28 29 36 39 44 57 601 5

Reply #12

lottaloot — Fri, 16 Dec 2005 16:12:58 GMT

I am getting there Time*Treat -------------Sub NoLoop() Dim SearchString SearchString = 123 Cells.Find(What:=SearchString, After:=[A1], LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).ActivateEnd Sub-------------Thanks for posting--now it's time to play with yours Function lowform_3(three_digit) Dim channel() As Integer Dim slot() As Integer Dim num As Integer, pos As Integer Dim c0, c1, c2, c3

Reply #11

time*treat — Fri, 16 Dec 2005 15:25:03 GMT

I would say a macro is one way of telling the application what to do with your data. It is a programming language. It is nothing you couldn't do yourself, but it frees you to do other things, or take a nap while it runs. Of course you have to be detailed. A macro does for your data what a washing machine does for your clothes, but you have to know if you want warm wash / cold rinse and heavy blanket or gentle cycle. Some of the built-ins like histogram and descriptive stats are just macros. If... [ More ]

Reply #10

powerplayer — Fri, 16 Dec 2005 14:32:16 GMT

Thank you Time*Treat RipsnorterWhat would I do with out all the LP people in this world.You guys are great..this is excatly what I needed.Thank you againPowerplayer

Reply #9

Rip Snorter — Fri, 16 Dec 2005 14:12:26 GMT

I posted something once asking about excel and hypersoniq provided this link. Maybe it will help you.http://www.contextures.com/xladvfilter01.htmlI learned some stuff that speeded things up for me a bit, but I hit a wall fairly early. I'd like to use macros but I don't understand what they are. Not entirely sure, even where filters are concerned.Seems to me there ought to be an easier way of doing all this, so I hope you figure it out.Jack

Reply #8

time*treat — Fri, 16 Dec 2005 13:40:06 GMT

You guys are yanking my chain, right? '-----------------------------------'Sub calendar() Dim start__cell As Date, next__day As Date, col As Integer start__cell = Cells(1, 1).Value '<--- date goes here' next__day = start__cell + 1 col = 2 While Month(start__cell) = Month(next__day) Cells(1, col).Value = next__day Cells(1, col).NumberFormat = mm/dd/yy col = col + 1 next__day = next__day + 1 WendEnd sub

Reply #7

powerplayer — Fri, 16 Dec 2005 11:41:10 GMT

I want to automate my spreedsheet a little more. I usually enter all the dates with find and replace useing last months but, want to know what formula would I use to do this:02/01/0502/02/0502/03/0502/04/0502/05/0502/06/0502/07/0502/08/0502/09/0502/10/0502/11/0502/12/0502/13/0502/14/0502/15/0502/16/0502/17/0502/18/0502/19/0502/20/0502/21/0502/22/0502/23/0502/24/0502/25/0502/26/0502/27/0502/28/05 That is Febuary. I want to have a formula that will start with 03/01/05 and then increase across each... [ More ]

Reply #6

time*treat — Fri, 16 Dec 2005 10:07:37 GMT

Now... back to worktools --> macro --> visual basic editor --> insert --> module the best results come from having your data properly organized in the spreadsheet.If you can decide how you want your data handled, yes, it can be done.Too many people think the statistics tools will magically predict their states numbers for tomorrow.I posted one solution to that 'skip count' thing on the other thread https://www.lotterypost.com/threads124458p1.htm

Reply #5

time*treat — Fri, 16 Dec 2005 09:50:51 GMT

A little fun in XL97: make sure you have R1C1 box unchecked e.g. letters across, numbers down Excel - Excel 97 Flight to Credits 1. On a new Worksheet, Press F52. Type X97:L97 and hit enter3. Press the tab key4. Hold Ctrl-Shift5. Click on the Chart Wizard button6. Use mouse to fly around - Right button forward/ Left button reverse

Reply #4

lottaloot — Thu, 15 Dec 2005 17:35:15 GMT

I want it to do everything! Seriously, I just want to be able to talk to it. Right at the moment1. I would just like to search for box skips (without having the combos set up ALL OVER the sheet like I currently have now)2. I would like to have the box skips give me a return of the actual day - Today's date so that I can have a number of skips (i.e, 37 days since 123 last hit on 12/1/05) Am I expecting too much

Reply #3

winsumloosesum — Thu, 15 Dec 2005 17:03:41 GMT

Kim,Don't go pulling all your hair out.What is it your trying to do?Maybe we can all put our collective thinking caps on and help each other.Talk to us girl

Reply #2

lottaloot — Thu, 15 Dec 2005 16:43:26 GMT

Please pass the info along when if you find it.

Reply #1

powerplayer — Thu, 15 Dec 2005 16:40:28 GMT

Lottaloot there is other ways. Been doing a lot of research on this. Just not sure how yet. Powerplayer

Macros in excel

lottaloot — Thu, 15 Dec 2005 16:30:11 GMT

I am very, very frustrated about this macro business within excel. I DO NOT understand how to get into the macro section without having to run an imaginary macro-then go in edit it. And even then, I don't know if I am doing it right. I have ordered books that have not arrived yet to teach myself how to make macros. But isn't there an easier way to talk to excel???Why can't I just sort of describe what I want it to do it figure it out for me