Trouble with Declaring Worksheets

Hi again.... I seem to keep having trouble declaring controls or worksheets
with the set command!
Here is my sample code, the Set iSheet Line errors-Object required '424',
what am I doing wrong?
the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
Also how is my code... am I heading in the right direction... or can it be
tightened up a bit!

Thank... once again Craig

Private Sub Verify_Code_Click()
Dim iSheet As Worksheet
Dim iRange As Range
shVerification.Unprotect
Application.ScreenUpdating = False
If shVerification.Range("B1").Value = "Pass" Then
Audits.Range("B5").Value = "Yes"
ElseIf shVerification.Range("B1").Value = "Fail" Then
Audits.Range("B5").Value = "No"
shVerification.Activate
shVerification.Range("B3").Select
For x = 1 To 200
If ActiveCell.Value = 1 Then
Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
WHERE MY ERROR STARTS
Set iRange = ActiveCell.Offset(0, 2).Text
iSheet.Activate
iSheet.Range(iRange).Select
Set iSheet = Nothing
Set iRange = Nothing
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next x
End If
shVerification.Protect
Application.ScreenUpdating = True
End Sub

I should add that the Set iRange is picking up the value in a cell...
example: F201 I wish to goto this cell on the selected sheet.
I thought I'd mention this so I didn't have to bother you after!
Craig

"Craig" <[email protected]> wrote in message
news:UWuUe.457289$s54.161894@pd7tw2no...
> Hi again.... I seem to keep having trouble declaring controls or
> worksheets with the set command!
> Here is my sample code, the Set iSheet Line errors-Object required '424',
> what am I doing wrong?
> the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
> Also how is my code... am I heading in the right direction... or can it be
> tightened up a bit!
>
> Thank... once again Craig
>
> Private Sub Verify_Code_Click()
> Dim iSheet As Worksheet
> Dim iRange As Range
> shVerification.Unprotect
> Application.ScreenUpdating = False
> If shVerification.Range("B1").Value = "Pass" Then
> Audits.Range("B5").Value = "Yes"
> ElseIf shVerification.Range("B1").Value = "Fail" Then
> Audits.Range("B5").Value = "No"
> shVerification.Activate
> shVerification.Range("B3").Select
> For x = 1 To 200
> If ActiveCell.Value = 1 Then
> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
> WHERE MY ERROR STARTS
> Set iRange = ActiveCell.Offset(0, 2).Text
> iSheet.Activate
> iSheet.Range(iRange).Select
> Set iSheet = Nothing
> Set iRange = Nothing
> Exit For
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Next x
> End If
> shVerification.Protect
> Application.ScreenUpdating = True
> End Sub
>

Hi Craig,

Try Changing:

> Set iSheet = ActiveCell.Offset(0, 1).Text

to:

Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)

---
Regards,
Norman



"Craig" <[email protected]> wrote in message
news:UWuUe.457289$s54.161894@pd7tw2no...
> Hi again.... I seem to keep having trouble declaring controls or
> worksheets with the set command!
> Here is my sample code, the Set iSheet Line errors-Object required '424',
> what am I doing wrong?
> the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
> Also how is my code... am I heading in the right direction... or can it be
> tightened up a bit!
>
> Thank... once again Craig
>
> Private Sub Verify_Code_Click()
> Dim iSheet As Worksheet
> Dim iRange As Range
> shVerification.Unprotect
> Application.ScreenUpdating = False
> If shVerification.Range("B1").Value = "Pass" Then
> Audits.Range("B5").Value = "Yes"
> ElseIf shVerification.Range("B1").Value = "Fail" Then
> Audits.Range("B5").Value = "No"
> shVerification.Activate
> shVerification.Range("B3").Select
> For x = 1 To 200
> If ActiveCell.Value = 1 Then
> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
> WHERE MY ERROR STARTS
> Set iRange = ActiveCell.Offset(0, 2).Text
> iSheet.Activate
> iSheet.Range(iRange).Select
> Set iSheet = Nothing
> Set iRange = Nothing
> Exit For
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Next x
> End If
> shVerification.Protect
> Application.ScreenUpdating = True
> End Sub
>

Hi Craig,

>I should add that the Set iRange is picking up the value in a cell...
>example: F201 I wish to goto this cell on the selected sheet.
> I thought I'd mention this so I didn't have to bother you after!


Try Replacing:

>> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
>> WHERE MY ERROR STARTS
>> Set iRange = ActiveCell.Offset(0, 2).Text
>> iSheet.Activate
>> iSheet.Range(iRange).Select

with:

Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
Set iRange = iSheet.Range(ActiveCell.Offset(0, 2).Value)
iSheet.Activate
iRange.Select

---
Regards,
Norman



"Craig" <[email protected]> wrote in message
news:x5vUe.457375$s54.109599@pd7tw2no...
>I should add that the Set iRange is picking up the value in a cell...
>example: F201 I wish to goto this cell on the selected sheet.
> I thought I'd mention this so I didn't have to bother you after!
> Craig
>
> "Craig" <[email protected]> wrote in message
> news:UWuUe.457289$s54.161894@pd7tw2no...
>> Hi again.... I seem to keep having trouble declaring controls or
>> worksheets with the set command!
>> Here is my sample code, the Set iSheet Line errors-Object required
>> '424', what am I doing wrong?
>> the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
>> Also how is my code... am I heading in the right direction... or can it
>> be tightened up a bit!
>>
>> Thank... once again Craig
>>
>> Private Sub Verify_Code_Click()
>> Dim iSheet As Worksheet
>> Dim iRange As Range
>> shVerification.Unprotect
>> Application.ScreenUpdating = False
>> If shVerification.Range("B1").Value = "Pass" Then
>> Audits.Range("B5").Value = "Yes"
>> ElseIf shVerification.Range("B1").Value = "Fail" Then
>> Audits.Range("B5").Value = "No"
>> shVerification.Activate
>> shVerification.Range("B3").Select
>> For x = 1 To 200
>> If ActiveCell.Value = 1 Then
>> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
>> WHERE MY ERROR STARTS
>> Set iRange = ActiveCell.Offset(0, 2).Text
>> iSheet.Activate
>> iSheet.Range(iRange).Select
>> Set iSheet = Nothing
>> Set iRange = Nothing
>> Exit For
>> Else
>> ActiveCell.Offset(1, 0).Select
>> End If
>> Next x
>> End If
>> shVerification.Protect
>> Application.ScreenUpdating = True
>> End Sub
>>
>
>

I'm still getting an error.... Runtime error '9' Subscript out of range
Craig

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Craig,
>
> Try Changing:
>
>> Set iSheet = ActiveCell.Offset(0, 1).Text
>
> to:
>
> Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
>
> ---
> Regards,
> Norman
>
>
>
> "Craig" <[email protected]> wrote in message
> news:UWuUe.457289$s54.161894@pd7tw2no...
>> Hi again.... I seem to keep having trouble declaring controls or
>> worksheets with the set command!
>> Here is my sample code, the Set iSheet Line errors-Object required
>> '424', what am I doing wrong?
>> the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
>> Also how is my code... am I heading in the right direction... or can it
>> be tightened up a bit!
>>
>> Thank... once again Craig
>>
>> Private Sub Verify_Code_Click()
>> Dim iSheet As Worksheet
>> Dim iRange As Range
>> shVerification.Unprotect
>> Application.ScreenUpdating = False
>> If shVerification.Range("B1").Value = "Pass" Then
>> Audits.Range("B5").Value = "Yes"
>> ElseIf shVerification.Range("B1").Value = "Fail" Then
>> Audits.Range("B5").Value = "No"
>> shVerification.Activate
>> shVerification.Range("B3").Select
>> For x = 1 To 200
>> If ActiveCell.Value = 1 Then
>> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
>> WHERE MY ERROR STARTS
>> Set iRange = ActiveCell.Offset(0, 2).Text
>> iSheet.Activate
>> iSheet.Range(iRange).Select
>> Set iSheet = Nothing
>> Set iRange = Nothing
>> Exit For
>> Else
>> ActiveCell.Offset(1, 0).Select
>> End If
>> Next x
>> End If
>> shVerification.Protect
>> Application.ScreenUpdating = True
>> End Sub
>>
>
>

Hi Craig,

Check that the cell right-adjacent to the active cell *really* contains
Audit and that the sheet name and the cell contents are identical - check
for leading and trailing spaces, for example.

Similarly, check that the correct offset cell contains the F201 reference.

My test code ran without error, providing that the two offset cells
contained a valid sheet name and a valid range reference, respectively:

'=================>>
Public Sub Tester()

Dim iSheet As Worksheet
Dim iRange As Range

Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)

Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
Set iRange = iSheet.Range(ActiveCell.Offset(0, 2).Value)
iSheet.Activate
iRange.Select
End Sub
'<<=================

BTW, the last two lines could be replaced with the single line:

Application.Goto iRange


---
Regards,
Norman



"Craig" <[email protected]> wrote in message
news:ZDvUe.457568$s54.305647@pd7tw2no...
> I'm still getting an error.... Runtime error '9' Subscript out of range
> Craig
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Craig,
>>
>> Try Changing:
>>
>>> Set iSheet = ActiveCell.Offset(0, 1).Text
>>
>> to:
>>
>> Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Craig" <[email protected]> wrote in message
>> news:UWuUe.457289$s54.161894@pd7tw2no...
>>> Hi again.... I seem to keep having trouble declaring controls or
>>> worksheets with the set command!
>>> Here is my sample code, the Set iSheet Line errors-Object required
>>> '424', what am I doing wrong?
>>> the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
>>> Also how is my code... am I heading in the right direction... or can it
>>> be tightened up a bit!
>>>
>>> Thank... once again Craig
>>>
>>> Private Sub Verify_Code_Click()
>>> Dim iSheet As Worksheet
>>> Dim iRange As Range
>>> shVerification.Unprotect
>>> Application.ScreenUpdating = False
>>> If shVerification.Range("B1").Value = "Pass" Then
>>> Audits.Range("B5").Value = "Yes"
>>> ElseIf shVerification.Range("B1").Value = "Fail" Then
>>> Audits.Range("B5").Value = "No"
>>> shVerification.Activate
>>> shVerification.Range("B3").Select
>>> For x = 1 To 200
>>> If ActiveCell.Value = 1 Then
>>> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
>>> WHERE MY ERROR STARTS
>>> Set iRange = ActiveCell.Offset(0, 2).Text
>>> iSheet.Activate
>>> iSheet.Range(iRange).Select
>>> Set iSheet = Nothing
>>> Set iRange = Nothing
>>> Exit For
>>> Else
>>> ActiveCell.Offset(1, 0).Select
>>> End If
>>> Next x
>>> End If
>>> shVerification.Protect
>>> Application.ScreenUpdating = True
>>> End Sub
>>>
>>
>>
>
>

I was using the VBA Name of the worksheet.... not the actual Excel name of
the worksheet!
Thanks Again... working great now!
Craig
"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Craig,
>
> Check that the cell right-adjacent to the active cell *really* contains
> Audit and that the sheet name and the cell contents are identical - check
> for leading and trailing spaces, for example.
>
> Similarly, check that the correct offset cell contains the F201 reference.
>
> My test code ran without error, providing that the two offset cells
> contained a valid sheet name and a valid range reference, respectively:
>
> '=================>>
> Public Sub Tester()
>
> Dim iSheet As Worksheet
> Dim iRange As Range
>
> Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
>
> Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
> Set iRange = iSheet.Range(ActiveCell.Offset(0, 2).Value)
> iSheet.Activate
> iRange.Select
> End Sub
> '<<=================
>
> BTW, the last two lines could be replaced with the single line:
>
> Application.Goto iRange
>
>
> ---
> Regards,
> Norman
>
>
>
> "Craig" <[email protected]> wrote in message
> news:ZDvUe.457568$s54.305647@pd7tw2no...
>> I'm still getting an error.... Runtime error '9' Subscript out of range
>> Craig
>>
>> "Norman Jones" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi Craig,
>>>
>>> Try Changing:
>>>
>>>> Set iSheet = ActiveCell.Offset(0, 1).Text
>>>
>>> to:
>>>
>>> Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value)
>>>
>>> ---
>>> Regards,
>>> Norman
>>>
>>>
>>>
>>> "Craig" <[email protected]> wrote in message
>>> news:UWuUe.457289$s54.161894@pd7tw2no...
>>>> Hi again.... I seem to keep having trouble declaring controls or
>>>> worksheets with the set command!
>>>> Here is my sample code, the Set iSheet Line errors-Object required
>>>> '424', what am I doing wrong?
>>>> the Activecell.offset(0,1).text is "Audits", which is a Worksheet.
>>>> Also how is my code... am I heading in the right direction... or can it
>>>> be tightened up a bit!
>>>>
>>>> Thank... once again Craig
>>>>
>>>> Private Sub Verify_Code_Click()
>>>> Dim iSheet As Worksheet
>>>> Dim iRange As Range
>>>> shVerification.Unprotect
>>>> Application.ScreenUpdating = False
>>>> If shVerification.Range("B1").Value = "Pass" Then
>>>> Audits.Range("B5").Value = "Yes"
>>>> ElseIf shVerification.Range("B1").Value = "Fail" Then
>>>> Audits.Range("B5").Value = "No"
>>>> shVerification.Activate
>>>> shVerification.Range("B3").Select
>>>> For x = 1 To 200
>>>> If ActiveCell.Value = 1 Then
>>>> Set iSheet = ActiveCell.Offset(0, 1).Text 'THIS IS
>>>> WHERE MY ERROR STARTS
>>>> Set iRange = ActiveCell.Offset(0, 2).Text
>>>> iSheet.Activate
>>>> iSheet.Range(iRange).Select
>>>> Set iSheet = Nothing
>>>> Set iRange = Nothing
>>>> Exit For
>>>> Else
>>>> ActiveCell.Offset(1, 0).Select
>>>> End If
>>>> Next x
>>>> End If
>>>> shVerification.Protect
>>>> Application.ScreenUpdating = True
>>>> End Sub
>>>>
>>>
>>>
>>
>>
>
>