Random glitch?

Hello, all.

This issue is a bit complicated, but I'll try to be as clear (yet brief) as
possible.

I have an audit workbook which contains 18 sections of audit questions on
four worksheets (called 1 thru 6, 7 thru 11, 12 thru 15 and 16 thru 18).
Each section contains a varying number of questions, and the workbook can
contain audit data for up to 20 audited facilities. Some of the facilities
are laboratories.

I have programmed a checkbox which sits next to each facility name on the
title sheet of the audit workbook. If the facility is a laboratory and the
user clicks the checkbox, the code will go through the workbook and, for that
particular facility, mark all of the line items which do NOT apply to
laboratories as "Not Applicable" (if a section header) and "N/A" (if an
individual question).

At least that's what is supposed to happen.

What I'm noticing is that sometimes it appears to work and sometimes it
doesn't. I would appreciate it if someone would review the code below and
see if you spot anything glaringly wrong.

Sub RDNAPop(FACVal)
'
'Called by one of 20 checkboxes. FACVal corresponds to a particular column
on each of the four worksheets. One column represents one facility.
'
Dim GAW As Object
Application.ScreenUpdating = False
For J = 1 To 4
If J = 1 Then Set GAW = ActiveWorkbook.Sheets("1 thru 6")
If J = 2 Then Set GAW = ActiveWorkbook.Sheets("7 thru 11")
If J = 3 Then Set GAW = ActiveWorkbook.Sheets("12 thru 15")
If J = 4 Then Set GAW = ActiveWorkbook.Sheets("16 thru 18")

For ROWID = 6 To GAW.Range("I4") + 5 'formula in cell I4 in each of the
four worksheets which tells VBA how many rows there are in each worksheet

If GAW.Cells(ROWID, 5) <> "" Then 'skip over any rows in which column E
is blank. These rows are for questions to be added later.

Set c = GAW.Cells(ROWID, 5).Find("LAB", LookIn:=xlValues) 'Look for
the three-letter combination LAB in column E. If LAB is there, the question
applies to a lab and the row should be skipped. (NOTE: The cell will
contain other text besides "LAB", which is why I used a .Find rather than If
GAW.Cells(ROWID, 5)="LAB")

If c Is Nothing And GAW.Cells(ROWID, 2) = "" Then GAW.Cells(ROWID,
FACVal) = "Not Applicable" 'If LAB is NOT found and column B is blank, this
is a header row, so put in "Not Applicable"

If c Is Nothing And GAW.Cells(ROWID, 2) <> "" Then GAW.Cells(ROWID,
FACVal) = "N/A" 'If LAB is NOT found and column B is NOT blank, this is a
question row, so put in "N/A"

End If

Next ROWID 'go to the next row in the worksheet

Next J 'Go to the next worksheet
Application.ScreenUpdating = True
Set GAW = Nothing
End Sub

There are no problems with data being put into the correct column, so that's
not an issue.

The problem is that when LAB appears in a header row or question row, it's
hit and miss as to whether "Not Applicable" or "N/A" are entered by the code.
As I said, sometimes it works and sometimes it doesn't.

All I'm asking for is for someone to review the code above and let me know
if you think it should do what I explained it should do.

Any ideas/help would be appreciated.

Thanks,
MARTY

Hi Marty
I worked thru your code and in itself I cannot see any glaring problem, one
area that I cannot check, and it is important, is the value passed from the
checkbox in FACval since this controls the column into which the message is
placed. Maybe the value is not correct or the wrong format? Try using a
debugger to check this value as the procedure is run.

--
Cheers
Nigel



"Marty" <Marty@discussions.microsoft.com> wrote in message
news:C1503AEF-5229-4887-B469-CB7BDCDFE814@microsoft.com...
> Hello, all.
>
> This issue is a bit complicated, but I'll try to be as clear (yet brief)
as
> possible.
>
> I have an audit workbook which contains 18 sections of audit questions on
> four worksheets (called 1 thru 6, 7 thru 11, 12 thru 15 and 16 thru 18).
> Each section contains a varying number of questions, and the workbook can
> contain audit data for up to 20 audited facilities. Some of the
facilities
> are laboratories.
>
> I have programmed a checkbox which sits next to each facility name on the
> title sheet of the audit workbook. If the facility is a laboratory and
the
> user clicks the checkbox, the code will go through the workbook and, for
that
> particular facility, mark all of the line items which do NOT apply to
> laboratories as "Not Applicable" (if a section header) and "N/A" (if an
> individual question).
>
> At least that's what is supposed to happen.
>
> What I'm noticing is that sometimes it appears to work and sometimes it
> doesn't. I would appreciate it if someone would review the code below and
> see if you spot anything glaringly wrong.
>
> Sub RDNAPop(FACVal)
> '
> 'Called by one of 20 checkboxes. FACVal corresponds to a particular
column
> on each of the four worksheets. One column represents one facility.
> '
> Dim GAW As Object
> Application.ScreenUpdating = False
> For J = 1 To 4
> If J = 1 Then Set GAW = ActiveWorkbook.Sheets("1 thru 6")
> If J = 2 Then Set GAW = ActiveWorkbook.Sheets("7 thru 11")
> If J = 3 Then Set GAW = ActiveWorkbook.Sheets("12 thru 15")
> If J = 4 Then Set GAW = ActiveWorkbook.Sheets("16 thru 18")
>
> For ROWID = 6 To GAW.Range("I4") + 5 'formula in cell I4 in each of
the
> four worksheets which tells VBA how many rows there are in each worksheet
>
> If GAW.Cells(ROWID, 5) <> "" Then 'skip over any rows in which column
E
> is blank. These rows are for questions to be added later.
>
> Set c = GAW.Cells(ROWID, 5).Find("LAB", LookIn:=xlValues) 'Look
for
> the three-letter combination LAB in column E. If LAB is there, the
question
> applies to a lab and the row should be skipped. (NOTE: The cell will
> contain other text besides "LAB", which is why I used a .Find rather than
If
> GAW.Cells(ROWID, 5)="LAB")
>
> If c Is Nothing And GAW.Cells(ROWID, 2) = "" Then GAW.Cells(ROWID,
> FACVal) = "Not Applicable" 'If LAB is NOT found and column B is blank,
this
> is a header row, so put in "Not Applicable"
>
> If c Is Nothing And GAW.Cells(ROWID, 2) <> "" Then
GAW.Cells(ROWID,
> FACVal) = "N/A" 'If LAB is NOT found and column B is NOT blank, this is a
> question row, so put in "N/A"
>
> End If
>
> Next ROWID 'go to the next row in the worksheet
>
> Next J 'Go to the next worksheet
> Application.ScreenUpdating = True
> Set GAW = Nothing
> End Sub
>
> There are no problems with data being put into the correct column, so
that's
> not an issue.
>
> The problem is that when LAB appears in a header row or question row, it's
> hit and miss as to whether "Not Applicable" or "N/A" are entered by the
code.
> As I said, sometimes it works and sometimes it doesn't.
>
> All I'm asking for is for someone to review the code above and let me know
> if you think it should do what I explained it should do.
>
> Any ideas/help would be appreciated.
>
> Thanks,
> MARTY

Nigel:

Sorry so long in saying "Thanks", but I really appreciate your eyes on this.
The column placement of the data is correct; that's not a problem.

I tested it some more after I posted my original message and the problem is
actually a bit worse in that it is not repeatable. Sometimes it works OK on
a facility, then when I close it and open it again and try it on the SAME
facility, it doesn't work. It appears that the title I chose for this thread
is accurate.

I'll disable this feature in my workbook and move on. I guess I'll just
have to mark it up to "one of those things".

Thanks again,
MARTY

"Nigel" wrote:

> Hi Marty
> I worked thru your code and in itself I cannot see any glaring problem, one
> area that I cannot check, and it is important, is the value passed from the
> checkbox in FACval since this controls the column into which the message is
> placed. Maybe the value is not correct or the wrong format? Try using a
> debugger to check this value as the procedure is run.
>
> --
> Cheers
> Nigel
>
>
>
> "Marty" <Marty@discussions.microsoft.com> wrote in message
> news:C1503AEF-5229-4887-B469-CB7BDCDFE814@microsoft.com...
> > Hello, all.
> >
> > This issue is a bit complicated, but I'll try to be as clear (yet brief)
> as
> > possible.
> >
> > I have an audit workbook which contains 18 sections of audit questions on
> > four worksheets (called 1 thru 6, 7 thru 11, 12 thru 15 and 16 thru 18).
> > Each section contains a varying number of questions, and the workbook can
> > contain audit data for up to 20 audited facilities. Some of the
> facilities
> > are laboratories.
> >
> > I have programmed a checkbox which sits next to each facility name on the
> > title sheet of the audit workbook. If the facility is a laboratory and
> the
> > user clicks the checkbox, the code will go through the workbook and, for
> that
> > particular facility, mark all of the line items which do NOT apply to
> > laboratories as "Not Applicable" (if a section header) and "N/A" (if an
> > individual question).
> >
> > At least that's what is supposed to happen.
> >
> > What I'm noticing is that sometimes it appears to work and sometimes it
> > doesn't. I would appreciate it if someone would review the code below and
> > see if you spot anything glaringly wrong.
> >
> > Sub RDNAPop(FACVal)
> > '
> > 'Called by one of 20 checkboxes. FACVal corresponds to a particular
> column
> > on each of the four worksheets. One column represents one facility.
> > '
> > Dim GAW As Object
> > Application.ScreenUpdating = False
> > For J = 1 To 4
> > If J = 1 Then Set GAW = ActiveWorkbook.Sheets("1 thru 6")
> > If J = 2 Then Set GAW = ActiveWorkbook.Sheets("7 thru 11")
> > If J = 3 Then Set GAW = ActiveWorkbook.Sheets("12 thru 15")
> > If J = 4 Then Set GAW = ActiveWorkbook.Sheets("16 thru 18")
> >
> > For ROWID = 6 To GAW.Range("I4") + 5 'formula in cell I4 in each of
> the
> > four worksheets which tells VBA how many rows there are in each worksheet
> >
> > If GAW.Cells(ROWID, 5) <> "" Then 'skip over any rows in which column
> E
> > is blank. These rows are for questions to be added later.
> >
> > Set c = GAW.Cells(ROWID, 5).Find("LAB", LookIn:=xlValues) 'Look
> for
> > the three-letter combination LAB in column E. If LAB is there, the
> question
> > applies to a lab and the row should be skipped. (NOTE: The cell will
> > contain other text besides "LAB", which is why I used a .Find rather than
> If
> > GAW.Cells(ROWID, 5)="LAB")
> >
> > If c Is Nothing And GAW.Cells(ROWID, 2) = "" Then GAW.Cells(ROWID,
> > FACVal) = "Not Applicable" 'If LAB is NOT found and column B is blank,
> this
> > is a header row, so put in "Not Applicable"
> >
> > If c Is Nothing And GAW.Cells(ROWID, 2) <> "" Then
> GAW.Cells(ROWID,
> > FACVal) = "N/A" 'If LAB is NOT found and column B is NOT blank, this is a
> > question row, so put in "N/A"
> >
> > End If
> >
> > Next ROWID 'go to the next row in the worksheet
> >
> > Next J 'Go to the next worksheet
> > Application.ScreenUpdating = True
> > Set GAW = Nothing
> > End Sub
> >
> > There are no problems with data being put into the correct column, so
> that's
> > not an issue.
> >
> > The problem is that when LAB appears in a header row or question row, it's
> > hit and miss as to whether "Not Applicable" or "N/A" are entered by the
> code.
> > As I said, sometimes it works and sometimes it doesn't.
> >
> > All I'm asking for is for someone to review the code above and let me know
> > if you think it should do what I explained it should do.
> >
> > Any ideas/help would be appreciated.
> >
> > Thanks,
> > MARTY
>
>
>