Select Case Help - Using a dynamic range

[PFDave]

Morning all,

I've attached a file which I will use to define the number of working hours affected for each row dependant on which Set of hours apply to that row.

This is worked out with a helper column (Column L in my example) using a lookup table against the location however for the purpose of this I've just included the open and close date, the set number and what I'd expect my return to display.

What I'm struggling with is how I can check the case for each row to then input the relevant formula in that cell.

Hopefully my example is clear enough as that I'm trying to achieve. I previously was going with the idea of a column for each different Set of hours and then use a lookup to only return the relevant value, but as there are 7 sets of hours to date and an ever expanding amount of data I wanted to try and solve this in a one column piece of work using VBA.

Thanks in advance

[AlanY]

extract from your file, would you clarify the criteria for Set A and B (RED below)

Open Date Close Date SET A SET B Hour SET Return
01/11/2016 15:46 02/11/2016 11:53 04:07 04:07 SET A 04:07
26/10/2016 10:45 02/11/2016 11:54 48:09 44:39 SET B 44:39
01/11/2016 16:23 02/11/2016 11:18 02:55 02:55 SET B 02:55
01/11/2016 16:26 02/11/2016 11:11 02:45 02:45 SET A 02:45

[PFDave]

Set A working hours are 9-5 Monday to Friday and 9-4 on Saturday
Set B working hours are 9-5 Monday to Friday and 9-12:30 on Saturday

[AlanY]

Set A working hours are 9-5 Monday to Friday and 9-4 on Saturday
Set B working hours are 9-5 Monday to Friday and 9-12:30 on Saturday

i got that, but how comes

01/11/2016 16:23 02/11/2016 11:18 02:55 02:55 SET B 02:55 is SET B, and
01/11/2016 16:26 02/11/2016 11:11 02:45 02:45 SET A 02:45 is SET A ?

[PFDave]

Oh, sorry how to I decide whether they are A or B?

Each of these open and close times are associated with a ticket number, this ticket number associates to a branch no., this branch no. will have a set of hours associated with it.

So I use lookups to find this out and column L is displayed in my output as people need to filter by branch hour type. So essentially, to solve this part of my issue how we establish the set's isn't what I need help with, what I need help with is inputting the relevant formula into column M based on the value displayed in column L

[AlanY]

still a bit confused!

do you need a formula to produce the results in Column M?

if so, put =IF(L2="set A",C2,D2) in M2 and copy down

or do without Cols C & D

Please Login or Register  to view this content.

[PFDave]

Sorry for the confusion Alan,

But as there are 6 sets of hours, and a growing list of rows, I'm looking for a solution which doesn't require me putting 6 columns in there and then an IF statement like you've suggested. As it's stands that's what I'm doing but it's not going to be future proof.

I want the code below to be modified so it works. So essentially I can run the macro and produce the Column M results, without the need for the 6 helper columns.

Please Login or Register  to view this content.

Maybe I need to loop this instead of using Case, but I'm not sure

[PFDave]

Ok, so this does some of what I need, but it's looking at P1 to determine the case. I need it to look at all cells in column L which are in the range set by

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[AlanY]

try this, it should loop thro' all the hour sets in column L

Please Login or Register  to view this content.

[PFDave]

Brilliant Alan,

Massively appreciated

[AlanY]

you're welcome