Posts by Simon Lloyd

Re: Save As Method

I tried your code using Excel 2003 and it worked fine!, if you are using Excel 2003 i suggest you re-install (well at least the library and help files).

Regards,
Simon

Re: Insert date in range based on a primary column

Hi you do not say where you would like the date, you do not say what the basis is for the new date....do you want the date in A1 if colum A has data or in B1 if colum B has data.....etc?

Adding todays date is quite simple, you can add this to your code so when you run it the current date will be put in cell A1

With ActiveSheet
 Range("A1").value = "=Today()" 
End With

of course you can change the range to suit!

regards,
Simon

Re: Address and GoTo and Timing

Hi, For your timing answer try this link http://www.ozgrid.com/forum/showthread.php?t=57009 , and as for your which sheet/cell try

Set ActSht = ActiveSheet
Set ActCell = ActiveCell

I have also used this in my coding so i can get back to the sheet i left

Dim OriginalSheet
OriginalSheet = ActiveSheet.Name

Im no expert or even that experienced but i think if you set a variable to them you can call on that variable.....wow i even sounded like i know what im talking about! maybe it will help maybe not!

Regards,
Simon

Re: Grouping Contiguous List into Non-Contiguous Groups

Dave, again thanks for the reply!, the method you supplied will of course automate the population of the framework from the list that appears in column H however, it does not group the players in groups of 3 or 4, the list could be typically 52 players long or less, if its less than 52 or more than 4 then grouping must take place. Lets take the scenario of 14 players, these would have to be grouped in to 2 groups of 3 and 2 groups of 4, the groups of 3 would need pasting in to the frame work first so the first group would occupy positions 1,2,3 as indicated on the framework the next group would occupy 5,6,7 and so on for all groups of 3 and then repeat for groups of 4, if there were 21 players the grouping would be 3 groups of 3 and 3 groups of 4 and so on for all permutations for up to 52 players.

I may be asking the impossible!!!

Regards,
Simon

Re: Scheduling Macro for Every 5 Minutes

You just have to type a square bracket then the word code then close the square bracket before your code starts and then type a square bracket then / and the word code and then close the square bracket after end sub of your code.....simple as that!

regards,
Simon

Re: Grouping Data Into Lists

Sorry Dave attached as requested!

Simon

Re: Grouping Data Into Lists

Sorry i forgot to attatch the picture, in the picture you will see the frame work and the list of people that has been randomly generated, there will never be groups of 5 or more and never less than 3, as you can see 12 players fits nicely in to 3 groups of 4 but 13 players would be 1 group of 4 and 3 groups of 3 and so on...etc the framework in its entirity will accommodate 52 players and at the moment i cut/copy/paste manually from the list to the right of the framework i would like this all to be handled by VBA either through a command button or when the generated list appears column H starting at H4 to H56.

regards,
Simon

Re: Scheduling Macro for Every 5 Minutes

Dave of course is dead right!, but the error handling i gave you should stop excel giving you the error message.....sloppy i know Dave but i'm still qyuite new to programming and have a head full of sawdust!

Regards,
Simon

Re: Grouping Data Into Lists

Dave, thanks for the reply, i'm sorry i dont quite understand!, i create a randomised list of players from a list in VBA and the results pasted to a worksheet, on another worksheet i have a frame work which is 4 colums wide by 5 rows deep then skip a row then repeat 13 times. What i would like to happen (via VBA) is for this list to be grouped in to 3's or 4's depending on its size when gouped start with the groups of 3 and paste them to the frame work i.e first paste would miss the first row, one column in paste then skip 3 rows and paste the next group of 3 (if there is one) then paste the groups of 4 to the framework but after pasting skip 2 rows then paste next group. I have attached a picture of the framework in a word document.

Regards,
Simon

Re: Scheduling Macro for every 5 minutes

The code will initiate when opening the workbook started with this line:

Private Sub Workbook_Open() 
    Application.OnTime Now + TimeValue("00:01:00"), "Macroseconds" 
End Sub

you should not have to start it manually maybe if you add this:

Sheets("Sheet1").Select 'or you can substitute select for activate

then it should help, you can change the 1 minute in the code to be the 5 minutes you wanted and as for interrupting you im not sure try it and see.....by the way you can manually put that formula in the cell and when ever you make a change to the sheet or move cells by pressing return or enter the =Now() will update (xl2003)
Regards,
Simon

Re: Scheduling Macro for every 5 minutes

Yes of course, you will find that once you have pasted the code where i indicated and you try to close the workbook it will give you that error because it is trying to stop a timer that has not started. Just click end when you get the problem....re-open the workbook allow the timer to put =Now() in the required cell then close the workbook...........all should be ok! If it is a problem that you will close the workbook before the first 5 minutes has elapsed then paste this code over the sub shown below!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo Xit
    Application.OnTime dTime, "Macroseconds", , False
Xit:      Exit Sub
End Sub

Regards,
Simon

Re: Scheduling Macro for every 5 minutes

The code below will run as soon as the workbook is opened and continue to run every minute until the workbook is closed! Just change the times to suit.

Regards,
Simon

Right click on the Excel icon next to "File" and select "View Code". In here put;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Macroseconds", , False
End Sub


Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "Macroseconds"
End Sub

Now in any Standard Module (Insert>Module) place this;

Public dTime As Date
Sub Macroseconds()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "Macroseconds"
With ActiveSheet
Range("B6").Select
ActiveCell.FormulaR1C1 = "NOW()"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A4").Select
End With
End Sub

Hi all,

I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3

Hope someone can help....it seems very complex to group all the permutations!

Simon.