Complicated Find Question!

I have a sheet that is part of a staffing rota. The hours people work are
filled out by clicking the mouse and dragging it along the row which places
a " " (space) in each cell highlighted and changes the colour to Grey using
VBA. I currently use lookup formulas to find the first space (start time)
and the last space (end time) the problem is that to look for the last space
I have to have a mirror image of the selection area as the lookup does not
work in reverse so in theory the end space in the reversed area is actually
the first not the last space. (I used spaces to eliminate the need for
conditional formatting)

Is there some VBA code or a Formula that would find the last space within
the row bearing in mind there is blanks in the middle for lunch hour! I need
this to reduce the file size as there is 57 sheets in the workbook of which
35 are used for each day of the month although only one week shows at a
time, and quite a lot of code is in the workbook as each month has different
start days of the week so when the month changes the sheets are changed and
some hidden depending on the how many days are in the month which is why I
need help on this problem.....

EG i'm using x's here to show where spaces would be.


7.30 8.00 8.30 9.00 9.30 10.00 10.30 11.00 11.30 12.00 12.30 1.00
1.30 2.00 2.30 3.00 3.30 4.00 etc
Name1
Name2
Name3 x x x x x x x x
x x x x x x
Name4
Name5
Name6
Name7
etc

The above would give start time as 8.00 and end time as 4.00 (last x in
3.30)

A formula would probably be better at the end of each row due to the fact
that if 5 people are starting at the same time the mouse can highlight and
fill out 5 rows at once!

Hope this makes sense!!!!

Regards
Neil

VBA could be something like this:

Dim rng as Range, rng1 as Range
Dim i as Long Dim lastcell as Range
for i = 2 to 4
set rng = Cells(2,i).Resize(1,30)
On Error Resume Next
set rng1 = rng.specialcells(xlConstants,xlTextValues)
On Error goto 0
if not rng1 is nothing then
set lastcell = rng.Areas(rng.Areas.count)
set lastcell = lastcell(lastcell.count)
msgbox "Row " & i ": " & lastcell.Address
end if
next

A formula could be:

=MATCH(REPT("z",255),4:4)

for row 4.



--
Regards,
Tom Ogilvy


"Karoo News" <[email protected]> wrote in message
news:[email protected]...
> I have a sheet that is part of a staffing rota. The hours people work are
> filled out by clicking the mouse and dragging it along the row which
places
> a " " (space) in each cell highlighted and changes the colour to Grey
using
> VBA. I currently use lookup formulas to find the first space (start time)
> and the last space (end time) the problem is that to look for the last
space
> I have to have a mirror image of the selection area as the lookup does not
> work in reverse so in theory the end space in the reversed area is
actually
> the first not the last space. (I used spaces to eliminate the need for
> conditional formatting)
>
> Is there some VBA code or a Formula that would find the last space within
> the row bearing in mind there is blanks in the middle for lunch hour! I
need
> this to reduce the file size as there is 57 sheets in the workbook of
which
> 35 are used for each day of the month although only one week shows at a
> time, and quite a lot of code is in the workbook as each month has
different
> start days of the week so when the month changes the sheets are changed
and
> some hidden depending on the how many days are in the month which is why I
> need help on this problem.....
>
> EG i'm using x's here to show where spaces would be.
>
>
> 7.30 8.00 8.30 9.00 9.30 10.00 10.30 11.00 11.30 12.00 12.30
1.00
> 1.30 2.00 2.30 3.00 3.30 4.00 etc
> Name1
> Name2
> Name3 x x x x x x x
x
> x x x x x x
> Name4
> Name5
> Name6
> Name7
> etc
>
> The above would give start time as 8.00 and end time as 4.00 (last x in
> 3.30)
>
> A formula would probably be better at the end of each row due to the fact
> that if 5 people are starting at the same time the mouse can highlight and
> fill out 5 rows at once!
>
> Hope this makes sense!!!!
>
> Regards
> Neil
>
>
>

That that formula is fantastic many thanks again does the job perfect
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> VBA could be something like this:
>
> Dim rng as Range, rng1 as Range
> Dim i as Long Dim lastcell as Range
> for i = 2 to 4
> set rng = Cells(2,i).Resize(1,30)
> On Error Resume Next
> set rng1 = rng.specialcells(xlConstants,xlTextValues)
> On Error goto 0
> if not rng1 is nothing then
> set lastcell = rng.Areas(rng.Areas.count)
> set lastcell = lastcell(lastcell.count)
> msgbox "Row " & i ": " & lastcell.Address
> end if
> next
>
> A formula could be:
>
> =MATCH(REPT("z",255),4:4)
>
> for row 4.
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Karoo News" <[email protected]> wrote in message
> news:[email protected]...
>> I have a sheet that is part of a staffing rota. The hours people work are
>> filled out by clicking the mouse and dragging it along the row which
> places
>> a " " (space) in each cell highlighted and changes the colour to Grey
> using
>> VBA. I currently use lookup formulas to find the first space (start time)
>> and the last space (end time) the problem is that to look for the last
> space
>> I have to have a mirror image of the selection area as the lookup does
>> not
>> work in reverse so in theory the end space in the reversed area is
> actually
>> the first not the last space. (I used spaces to eliminate the need for
>> conditional formatting)
>>
>> Is there some VBA code or a Formula that would find the last space within
>> the row bearing in mind there is blanks in the middle for lunch hour! I
> need
>> this to reduce the file size as there is 57 sheets in the workbook of
> which
>> 35 are used for each day of the month although only one week shows at a
>> time, and quite a lot of code is in the workbook as each month has
> different
>> start days of the week so when the month changes the sheets are changed
> and
>> some hidden depending on the how many days are in the month which is why
>> I
>> need help on this problem.....
>>
>> EG i'm using x's here to show where spaces would be.
>>
>>
>> 7.30 8.00 8.30 9.00 9.30 10.00 10.30 11.00 11.30 12.00 12.30
> 1.00
>> 1.30 2.00 2.30 3.00 3.30 4.00 etc
>> Name1
>> Name2
>> Name3 x x x x x x x
> x
>> x x x x x x
>> Name4
>> Name5
>> Name6
>> Name7
>> etc
>>
>> The above would give start time as 8.00 and end time as 4.00 (last x in
>> 3.30)
>>
>> A formula would probably be better at the end of each row due to the fact
>> that if 5 people are starting at the same time the mouse can highlight
>> and
>> fill out 5 rows at once!
>>
>> Hope this makes sense!!!!
>>
>> Regards
>> Neil
>>
>>
>>
>
>
>