Tags: capturing, code, direct, error, excel, execute, microsoft, msdn, public, sheetcheck, software, sub

On Error Issues

On Microsoft » Microsoft Excel

6,665 words with 3 Comments; publish: Fri, 23 May 2008 04:58:00 GMT; (306171.88, « »)

I'm having issues capturing an error and using it to direct the code.

Here is the code I'm trying to execute:

Public Sub sheetCheck()

cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count

For i = 1 To cNodes

On Error GoTo NewSheet

isThere = True

sheetName = sheetBase & i

Sheets(sheetName).Visible = False

check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name = sheetName

Next i

Exit Sub

NewSheet: Scoring_0.Copy after:=Scoring_0

isThere = False

GoTo check

End Sub

Nodes refers to a list of nodes for this system. The sheets are named

Scoring x, where x would be replaced by i in the loop. The code will

execute perfectly through one error, but if I have more than one

non-existent sheet, it fails.

Cheers-

Chris

--

cmk18

---

cmk18's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6047

View this thread: http://www.excelforum.com/showthread.php?threadid=386540

All Comments

Leave a comment...

  • 3 Comments
    • I think you need to reset the error check

      On Error goto 0

      place this after

      NewSheet:

      --

      steveB

      Remove "AYN" from email to respond

      "cmk18" <cmk18.1s2m2l_1121195188.9637.excel.questionfor.info.excelforum-nospam.com> wrote in

      message news:cmk18.1s2m2l_1121195188.9637.excel.questionfor.info.excelforum-nospam.com...

      > I'm having issues capturing an error and using it to direct the code.

      > Here is the code I'm trying to execute:

      > Public Sub sheetCheck()

      > cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count

      > For i = 1 To cNodes

      > On Error GoTo NewSheet

      > isThere = True

      > sheetName = sheetBase & i

      > Sheets(sheetName).Visible = False

      > check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name => sheetName

      > Next i

      > Exit Sub

      > NewSheet: Scoring_0.Copy after:=Scoring_0

      > isThere = False

      > GoTo check

      > End Sub

      > Nodes refers to a list of nodes for this system. The sheets are named

      > Scoring x, where x would be replaced by i in the loop. The code will

      > execute perfectly through one error, but if I have more than one

      > non-existent sheet, it fails.

      > Cheers-

      > Chris

      >

      > --

      > cmk18

      > ---

      > cmk18's Profile:

      > http://www.excelforum.com/member.php?action=getinfo&userid=6047

      > View this thread: http://www.excelforum.com/showthread.php?threadid=386540

      >

      #1; Fri, 23 May 2008 04:59:00 GMT
    • Once an error is raised, VBA is operating in "error mode". You

      need to cause it to resume in "normal mode" by using a Resume

      statement. Change your

      GoTo check

      ' to

      Resume check

      Cordially,

      Chip Pearson

      Microsoft MVP - Excel

      Pearson Software Consulting, LLC

      www.cpearson.com

      "cmk18" <cmk18.1s2m2l_1121195188.9637.excel.questionfor.info.excelforum-nospam.com>

      wrote in message

      news:cmk18.1s2m2l_1121195188.9637.excel.questionfor.info.excelforum-nospam.com...

      > I'm having issues capturing an error and using it to direct the

      > code.

      > Here is the code I'm trying to execute:

      > Public Sub sheetCheck()

      > cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count

      > For i = 1 To cNodes

      > On Error GoTo NewSheet

      > isThere = True

      > sheetName = sheetBase & i

      > Sheets(sheetName).Visible = False

      > check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name

      > => sheetName

      > Next i

      > Exit Sub

      > NewSheet: Scoring_0.Copy after:=Scoring_0

      > isThere = False

      > GoTo check

      > End Sub

      > Nodes refers to a list of nodes for this system. The sheets

      > are named

      > Scoring x, where x would be replaced by i in the loop. The

      > code will

      > execute perfectly through one error, but if I have more than

      > one

      > non-existent sheet, it fails.

      > Cheers-

      > Chris

      >

      > --

      > cmk18

      > ---

      > cmk18's Profile:

      > http://www.excelforum.com/member.php?action=getinfo&userid=6047

      > View this thread:

      > http://www.excelforum.com/showthread.php?threadid=386540

      >

      #2; Fri, 23 May 2008 05:00:00 GMT
    • You could use a separate function that checks for existence of the worksheet.

      It might make it easier to read your code when you come back to it, too.

      Function WorksheetExists(SheetName As Variant, _

      Optional WhichBook As Workbook) As Boolean

      'from Chip Pearson

      Dim WB As Workbook

      Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)

      On Error Resume Next

      WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)

      End Function

      Public Sub sheetCheck()

      cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count

      For i = 1 To cNodes

      sheetName = sheetBase & i

      if worksheetsexists(sheetname, thisworkbook) then

      'it exists

      else

      'it doesn't exist

      end if

      next i

      End Sub

      cmk18 wrote:

      > I'm having issues capturing an error and using it to direct the code.

      > Here is the code I'm trying to execute:

      > Public Sub sheetCheck()

      > cNodes = ThisWorkbook.Names("Nodes").RefersToRange.Rows.Count

      > For i = 1 To cNodes

      > On Error GoTo NewSheet

      > isThere = True

      > sheetName = sheetBase & i

      > Sheets(sheetName).Visible = False

      > check: If Not isThere Then Sheets("Scoring Sheet 0 (2)").Name => sheetName

      > Next i

      > Exit Sub

      > NewSheet: Scoring_0.Copy after:=Scoring_0

      > isThere = False

      > GoTo check

      > End Sub

      > Nodes refers to a list of nodes for this system. The sheets are named

      > Scoring x, where x would be replaced by i in the loop. The code will

      > execute perfectly through one error, but if I have more than one

      > non-existent sheet, it fails.

      > Cheers-

      > Chris

      > --

      > cmk18

      > ---

      > cmk18's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6047

      > View this thread: http://www.excelforum.com/showthread.php?threadid=386540

      --

      Dave Peterson

      #3; Fri, 23 May 2008 05:01:00 GMT