Forum Home
Press F1
Thread ID: 91458	2008-07-08 01:12:00	Help with some Excel VBA please!	nofam (9009)	Press F1

Post ID	Timestamp	Content	User
686349	2008-07-08 01:12:00	Hi All, I need some help with the below code. What this does is take a date range, and count the number of dates, but groups consecutive dates into 'spells', for example: 01/05/2008 1 02/05/2008 1 06/05/2008 2 09/05/2008 3 11/05/2008 4 12/05/2008 4 This would be counted as 4 'spells', which is what I want. But what I need the macro to do is to run through a list of people's names, get to the last name, offset 2 columns, and place the number of spells there: Name: Date: Spells: Charlie 01/04/2008 Charlie 02/04/2008 Charlie 03/04/2008 Charlie 06/04/2008 2 Mike 03/04/2008 Mike 04/04/2008 Mike 18/04/2008 Mike 20/04/2008 Mike 21/04/2008 Mike 29/04/2008 4 Hope this makes sense!! Sub spells() Dim i As Integer Range("E1").Select i = 1 Do Until ActiveCell = "" If ActiveCell = "" Then GoTo 10 Else ActiveCell.Offset(1, 0).Select If ActiveCell = ActiveCell.Offset(-1, 0) + 1 Then i = i Else i = i + 1 End If End If Loop 10 Range("H2").Select ActiveCell = i - 1 End Sub	nofam (9009)
686350	2008-07-08 08:22:00	Hi Nofam, I'm assuming there is more to the spreadsheet and code than that? If not, I'm doing a fair bit wrong. (Not unusual) :yuck: Have you tried www.mrexcel.com and the forum board there?	the_bogan (9949)
686351	2008-07-08 20:59:00	No that really is pretty much it - I have a list of names & dates (from a database) that is a few hundred rows long, and I need the code to programatically set a date range based on the first and last instance of a name, and then offset a few columns over from the last instance, and drop the 'spells' calculation in that cell. I've since turned my code into a UDF, which may help, but the ultimate would be if someone could come up with a non-VBA of doing this??	nofam (9009)
686352	2008-07-09 02:28:00	Not sure you could do this without VBA. But anyway, assuming that your list is continguous (i.e no break between your charlie and mike rows in your example), you could try something like this: Public Sub InsertSpells() Dim rwIndex As Integer, colIndex As Integer, intCount As Integer 'starting point in sheet (example below corresponds to B3 on sheet1) rwIndex = 3 colIndex = 2 'initialise spell count to 1 intCount = 1 'keep looping while there are no empty cells Do While IsEmpty(Worksheets("Sheet1").Cells(rwIndex, colIndex)) = False If Worksheets("Sheet1").Cells(rwIndex, colIndex) = Worksheets("Sheet1").Cells(rwIndex + 1, colIndex) Then 'Check to see if dates are consecutive If Worksheets("Sheet1").Cells(rwIndex, colIndex + 1) + 1 <> Worksheets("Sheet1").Cells(rwIndex + 1, colIndex + 1) Then 'Not consecutive so add count intCount = intCount + 1 End If Else 'Output spell count Worksheets("Sheet1").Cells(rwIndex, colIndex + 2) = intCount 'Reinitialise spell count to 1 intCount = 1 End If 'increment row index counter rwIndex = rwIndex + 1 Loop End Sub HTH Dave	odyssey (4613)
686353	2008-07-09 03:47:00	Dave - you sir, are a legend! Works like a charm! :banana Thanks all for your suggestions! :thumbs:	nofam (9009)
686354	2008-07-09 05:31:00	Further to this Dave, can the same code be tweaked to simply count the number of dates, and place the result one column over from the spells?	nofam (9009)
686355	2008-07-09 08:07:00	Hi, I prefer VBA in these types of operations but just as an alternative you can use formulas to achieve this if the data was in name then date order. Assuming your data is in cols A:B then in D2 you could enter the following formula and drag down. =IF(A2<>"",IF(A2<>A1,1,IF(B2>B1+1,D1+1,D1)),"") cheers, Graham	Parry (5696)
686356	2008-07-10 03:17:00	Sure, no problem to tweak. Try this: Public Sub InsertSpells() Dim rwIndex As Integer, colIndex As Integer, intCount As Integer, intNoDates As Integer 'starting point in sheet (example below corresponds to B3 on sheet1) rwIndex = 3 colIndex = 2 'initialise counters intCount = 1 intNoDates = 1 'keep looping while there are no empty cells Do While IsEmpty(Worksheets("Sheet1").Cells(rwIndex, colIndex)) = False If Worksheets("Sheet1").Cells(rwIndex, colIndex) = Worksheets("Sheet1").Cells(rwIndex + 1, colIndex) Then 'Check to see if dates are consecutive If Worksheets("Sheet1").Cells(rwIndex, colIndex + 1) + 1 <> Worksheets("Sheet1").Cells(rwIndex + 1, colIndex + 1) Then 'Not consecutive so add count intCount = intCount + 1 End If Else 'Output spell count and total number of days Worksheets("Sheet1").Cells(rwIndex, colIndex + 2) = intCount Worksheets("Sheet1").Cells(rwIndex, colIndex + 3) = intNoDates 'Reinitialise counters intCount = 1 intNoDates = 0 End If 'increment row index counter and number of days rwIndex = rwIndex + 1 intNoDates = intNoDates + 1 Loop End Sub Cheers Dave	odyssey (4613)
686357	2008-07-10 04:08:00	Superb Dave - thanks heaps! ;) So how did you learn this stuff? And can you suggest a good place for me to start learning it too?? I really get the feeling it would add a whole new dimension to what I can do with Access/Excel!! :banana	nofam (9009)
1