Trouble with Declaring Worksheets

[Craig]

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

 

[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!
Craig

 

[Norman Jones]

Hi Craig,

Try Changing:

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

to:

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

 

[Norman Jones]

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:

with:

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

 

[Craig]

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

 

[Norman Jones]

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

 

[Craig]

I was using the VBA Name of the worksheet.... not the actual Excel name of
the worksheet!
Thanks Again... working great now!
Craig