CODEWITHSUNDEEP
vbaexcel
user2933813
user2933813

Reputation: 3

Loop Through a range of cells in Excel

I have an excel sheet that contains phone calls with Start day, Start Time and End Time. I would like to find a way to loop through the calls to see how many of our phone trunks are in use during each call. There may be a better way to check this but here is the logic I was thinking of using.

In Cell C1 I have the Start time of the first call of the month. In D1 I have the end time of that call. I know at the beginning of that first call only one trunk is in use. I need to loop through each call to see if the End time of any calls prior to the current checked call is greater than the start time of the current call being checked.

So the work really begins with the second row and a check to see if C2 (call Start time of call 2) is < D1 ( end time of call 1).

The next loop through would check if C3 < D1 and then check if C3 < D2. If any of the checks are true then a value called Trunks (With a starting value of 1) would increment by 1. Once the row check was finished Trunks would be written to column E corresponding with the row being checked.

The next loop would check if C4 < D1 then C4 < D2 then C4 < D3 and write the value of trunks to E4

Then on to C5 and so on until the end of the sheet. The sheet will be from 12,000 to 15,000 rows.

Can this be done through a sub in VBA? Can anyone help write this sub for me? Here are the values of C1 - C11 and D1 - D11. In the example below the value of trunk in every row would be 1 until you arrived at row 7. since the start time of the call in row 7 (6:10:05) is less than end time of the call from row 6 (6:10:59), that means two calls were happening at the same time so trunks would equal 2.

C1 2:44:00 AM   D1 2:45:08 AM
C2 3:30:15 AM   D2 3:33:10 AM
C3 4:02:50 AM   D3 4:04:12 AM
C4 4:48:15 AM   D4 4:48:32 AM
C5 6:02:25 AM   D5 6:03:57 AM
C6 6:08:54 AM   D6 6:10:59 AM
C7 6:10:05 AM   D7 6:10:34 AM
C8 6:10:05 AM   D8 6:10:41 AM
C9 6:23:30 AM   D9 6:29:07 AM
C10 6:36:28 AM  D10 6:39:54 AM
C11 7:16:56 AM  D11 7:24:56 AM

Thanks in Advance!

Upvotes: 0

Views: 1122

Answers (2)

Sohel
Sohel

Reputation: 676

The basic idea of looping through a bunch of cells, comparing it with values in another column is shown in the following. I have used a similar one in one of the programs. Now you can use if/else logic matching your requirement. 

For rowIndex = 2 To lr2

calcFormula1 = (Application.SumIf(Range("C2:C100"), Range(rowIndex, "D2:D99").Text, Range("E:E")))

Cells(rowIndex, "O").Value = calcFormula1

Next rowIndex

Upvotes: 0

digitlworld
digitlworld

Reputation: 1046

You do not necessarily need VBA. Try the following, adjusting for your data ranges. You cannot use full column references in this case, or else the formula will take a long time to run.

=SUM(IF(C$1:C$11<=C1,IF(D$1:D$11>=C1,1,0),0))

When you enter this, you'll have to press CTRL+SHIFT+ENTER instead of just ENTER. You'll know it worked because Excel will surround the formula with {}.

{=SUM(IF(C$1:C$11<=C1,IF(D$1:D$11>=C1,1,0),0))}

This is an Array formula, or CSE Formula. It treats each cell in the ranges you specify as a single instance. The IFs are emulating an AND operation. This is essentially summing the number of rows that have a start time <= current row's start time, and a end time >= the start time.

This formula needs to appear on every row.

This works for your example above, but is NOT a complete solution. There are missed cases.

EDIT: The following may cover the missed cases. Same rules as above apply. This is still something for you to learn about on your own, however, so the explanation is very light.

=SUM(IF(C$1:C$11<=C1,IF(D$1:D$11>=C1,1,0),IF(C1:C11>=C1,IF(C1:C11<=D1,1,0),0)))

Upvotes: 2

Related Questions