Hi

My brain will not function this morning. Here's the problem.
I am displaying a worksheet to the user with a set of transactions that
are to be selected if the goods have been received.
This is being filtered by a column not visible to the user, setting
autofilter for "Waiting".
Several non essential columns are being hidden, and I am inserting a
temporary column, with a list of checkboxes for the range visible, so
the user can tick items that have been received.
They may wish to apply a further filter to narrow the subset of
transactions to allow them to more easily see which are to be checked
off.
That filter may be released, and another applied and further checking
takes place.

When the process is complete, the user will click a button to "Update"
and I want to be able to change the status in the hidden column from
"Waiting" to "Received".
This I can do by comparing the status of the cell linked to the
checkbox, but how do I define the range to loop though, only including
those cells visible in the range the "I" have filtered by "Waiting",
having ensured that all other filters "they" might have applied have
been removed.

It's probably very simple, but I can't see it. Or, is there a better
way?


--
Regards

Roger Govier

Unless the data is huge, why not just loop through all the cells and check
this column

Dim rng as Range
set rng = Range("A1").CurrentRegion
set rng = rng.Columns(8).Cells
for each cell in rng
if cell.Value = "Waiting" then
if cell.offset(0,2).Value = True then
cell.Value = "Received"
end if
end if
next

--
Regards,
Tom Ogilvy


"Roger Govier" wrote in message
...
Hi

My brain will not function this morning. Here's the problem.
I am displaying a worksheet to the user with a set of transactions that
are to be selected if the goods have been received.
This is being filtered by a column not visible to the user, setting
autofilter for "Waiting".
Several non essential columns are being hidden, and I am inserting a
temporary column, with a list of checkboxes for the range visible, so
the user can tick items that have been received.
They may wish to apply a further filter to narrow the subset of
transactions to allow them to more easily see which are to be checked
off.
That filter may be released, and another applied and further checking
takes place.

When the process is complete, the user will click a button to "Update"
and I want to be able to change the status in the hidden column from
"Waiting" to "Received".
This I can do by comparing the status of the cell linked to the
checkbox, but how do I define the range to loop though, only including
those cells visible in the range the "I" have filtered by "Waiting",
having ensured that all other filters "they" might have applied have
been removed.

It's probably very simple, but I can't see it. Or, is there a better
way?


--
Regards

Roger Govier

Hi Tom

Thanks so much for your response., which I received on return from
lunch.
You have brought the brain back into gear. I was being rather stupid.
You are quite right, there is not so much data, that I can't do as you
suggest. There are other status values that the transactions can have,
and that was throwing me.
I have made the routine work for any of the types that it might find.
Rather then bothering about the current rgion, I have defined the range
as the used range of Status column.

I moved the test for the Offset cell value being True, up a level from
that set in your code, so of course it only flips through the rows that
have been "checked" and therefore can only be the set from the set of
cells with the staus I offer the user (in my original posting that was
"Waiting", but at other times it could be any of the other types.)
The line cell.value < "complete" is a bit superfluous, but I don't
mind a bit of "belt and braces".
Equally ,cycling through each of the possible status is a bit
inefficient, but the one macro will deal with all cases.

Sub Updatestatus()

Dim rng As Range, ws As Worksheet
Set ws = Worksheets("Awaiting Authorisation") '
With ws
lastrow = .Cells(Rows.Count, 6).End(xlUp).Row
Set rng = .Range("U12:U" & lastrow)
End With
For Each cell In rng
If cell.Offset(0, -7).Value = True Then
If cell.Value < "Complete" Then
If cell.Value = "Invoiced" Then cell.Value = "Complete"
If cell.Value = "Received" Then cell.Value = "Invoiced"
If cell.Value = "Waiting" Then cell.Value = "Received"
End If
End If
Next
End Sub

Thanks once again for gettting me kick started.
I think the brain is now functioning on 3 cylinders!!! Must have been
the wine last night<bg

--
Regards

Roger Govier


"Tom Ogilvy" wrote in message
...
Unless the data is huge, why not just loop through all the cells and
check
this column

Dim rng as Range
set rng = Range("A1").CurrentRegion
set rng = rng.Columns(8).Cells
for each cell in rng
if cell.Value = "Waiting" then
if cell.offset(0,2).Value = True then
cell.Value = "Received"
end if
end if
next

--
Regards,
Tom Ogilvy


"Roger Govier" wrote in message
...
Hi

My brain will not function this morning. Here's the problem.
I am displaying a worksheet to the user with a set of transactions
that
are to be selected if the goods have been received.
This is being filtered by a column not visible to the user, setting
autofilter for "Waiting".
Several non essential columns are being hidden, and I am inserting a
temporary column, with a list of checkboxes for the range visible, so
the user can tick items that have been received.
They may wish to apply a further filter to narrow the subset of
transactions to allow them to more easily see which are to be checked
off.
That filter may be released, and another applied and further checking
takes place.

When the process is complete, the user will click a button to
"Update"
and I want to be able to change the status in the hidden column from
"Waiting" to "Received".
This I can do by comparing the status of the cell linked to the
checkbox, but how do I define the range to loop though, only
including
those cells visible in the range the "I" have filtered by "Waiting",
having ensured that all other filters "they" might have applied have
been removed.

It's probably very simple, but I can't see it. Or, is there a better
way?


--
Regards

Roger Govier