|
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 |
Trouble with Declaring Worksheets
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" 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 |
Trouble with Declaring Worksheets
Hi Craig,
Try Changing: Set iSheet = ActiveCell.Offset(0, 1).Text to: Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value) --- Regards, Norman "Craig" 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 |
Trouble with Declaring Worksheets
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" 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" 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 |
Trouble with Declaring Worksheets
I'm still getting an error.... Runtime error '9' Subscript out of range
Craig "Norman Jones" wrote in message ... Hi Craig, Try Changing: Set iSheet = ActiveCell.Offset(0, 1).Text to: Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value) --- Regards, Norman "Craig" 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 |
Trouble with Declaring Worksheets
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" 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" wrote in message ... Hi Craig, Try Changing: Set iSheet = ActiveCell.Offset(0, 1).Text to: Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value) --- Regards, Norman "Craig" 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 |
Trouble with Declaring Worksheets
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" wrote in message ... 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" 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" wrote in message ... Hi Craig, Try Changing: Set iSheet = ActiveCell.Offset(0, 1).Text to: Set iSheet = Sheets(ActiveCell.Offset(0, 1).Value) --- Regards, Norman "Craig" 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 |
| All times are GMT +1. The time now is 06:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com