Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling issues
Below is some code that I have been developing that looks at a log to find
the largest sequential value (column "C") in regards to a specific Key. The problem I am having is the error handling issues. If the "Key" is not found in column "B" then I need a Msgbox to appear, let the user know that the "key" doesn't exist and give some options. I can get the "NewEON" to work great. But I get the Msgbox regardless of whether the "Key" is there or not. Where would be the best place to put the error handling, or better still, how should I code it so that it only functions if the "Key" is invalid? Sub NewAddendum() Dim Key As Integer Dim Biggest As Integer Dim RowWithBiggest As Integer Dim Row As Integer Dim LastRow As Integer Dim NewRow As Integer Dim ans As String Dim NewEON As Integer Dim NextRow As Integer Dim NewKey As Integer LastRow = Range("A10000").End(xlUp).Row Key = InputBox("EO#") Biggest = -1 For Row = 1 To LastRow If (Cells(Row, 2).Value = Key) And (Cells(Row, 3).Value Biggest) Then Biggest = Cells(Row, 3).Value RowWithBiggest = Row End If Next Row If (Cells(Row, 2).Value) < Key Then ans = MsgBox("The EO Number you typed in " & Key & " does not exist in this log. " _ & vbCr & "Would you like to re-type the number? " & " If No, then a new number" _ & " will be created for you " & vbCr & "Press cancel to Cancel the operation", vbYesNoCancel) End If Select Case ans Case vbYes NewKey = InputBox("NewNumber") For Row = 1 To LastRow If (Cells(Row, 2).Value = NewKey) And (Cells(Row, 3).Value Biggest) Then Biggest = Cells(Row, 3).Value RowWithBiggest = Row End If Next Row Case vbNo NewEON = Application.Max(Range("B4:B10000")) + 1 NextRow = Range("A10000").End(xlUp).Row + 1 Cells(NextRow, 2).Value = NewEON Cells(NextRow, 3) = "0" Exit Sub Case Else Exit Sub End Select NewRow = RowWithBiggest + 1 Rows(NewRow).Select Selection.Insert Shift:=xlDown Cells(NewRow, 1) = "E" Cells(NewRow, 2) = Key Cells(NewRow, 3) = Biggest + 1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling issues
If (Cells(Row, 2).Value) < Key Then
should be If Biggest = -1 Then I didn't look beyond that. -- Regards, Tom Ogilvy "asmenut" wrote in message ... Below is some code that I have been developing that looks at a log to find the largest sequential value (column "C") in regards to a specific Key. The problem I am having is the error handling issues. If the "Key" is not found in column "B" then I need a Msgbox to appear, let the user know that the "key" doesn't exist and give some options. I can get the "NewEON" to work great. But I get the Msgbox regardless of whether the "Key" is there or not. Where would be the best place to put the error handling, or better still, how should I code it so that it only functions if the "Key" is invalid? Sub NewAddendum() Dim Key As Integer Dim Biggest As Integer Dim RowWithBiggest As Integer Dim Row As Integer Dim LastRow As Integer Dim NewRow As Integer Dim ans As String Dim NewEON As Integer Dim NextRow As Integer Dim NewKey As Integer LastRow = Range("A10000").End(xlUp).Row Key = InputBox("EO#") Biggest = -1 For Row = 1 To LastRow If (Cells(Row, 2).Value = Key) And (Cells(Row, 3).Value Biggest) Then Biggest = Cells(Row, 3).Value RowWithBiggest = Row End If Next Row If (Cells(Row, 2).Value) < Key Then ans = MsgBox("The EO Number you typed in " & Key & " does not exist in this log. " _ & vbCr & "Would you like to re-type the number? " & " If No, then a new number" _ & " will be created for you " & vbCr & "Press cancel to Cancel the operation", vbYesNoCancel) End If Select Case ans Case vbYes NewKey = InputBox("NewNumber") For Row = 1 To LastRow If (Cells(Row, 2).Value = NewKey) And (Cells(Row, 3).Value Biggest) Then Biggest = Cells(Row, 3).Value RowWithBiggest = Row End If Next Row Case vbNo NewEON = Application.Max(Range("B4:B10000")) + 1 NextRow = Range("A10000").End(xlUp).Row + 1 Cells(NextRow, 2).Value = NewEON Cells(NextRow, 3) = "0" Exit Sub Case Else Exit Sub End Select NewRow = RowWithBiggest + 1 Rows(NewRow).Select Selection.Insert Shift:=xlDown Cells(NewRow, 1) = "E" Cells(NewRow, 2) = Key Cells(NewRow, 3) = Biggest + 1 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling issues
Tom,
Thanks. Once I re-stated the obvious and placed the final "End If" after the case statements, it worked like a charm. "Tom Ogilvy" wrote: If (Cells(Row, 2).Value) < Key Then should be If Biggest = -1 Then I didn't look beyond that. -- Regards, Tom Ogilvy "asmenut" wrote in message ... Below is some code that I have been developing that looks at a log to find the largest sequential value (column "C") in regards to a specific Key. The problem I am having is the error handling issues. If the "Key" is not found in column "B" then I need a Msgbox to appear, let the user know that the "key" doesn't exist and give some options. I can get the "NewEON" to work great. But I get the Msgbox regardless of whether the "Key" is there or not. Where would be the best place to put the error handling, or better still, how should I code it so that it only functions if the "Key" is invalid? Sub NewAddendum() Dim Key As Integer Dim Biggest As Integer Dim RowWithBiggest As Integer Dim Row As Integer Dim LastRow As Integer Dim NewRow As Integer Dim ans As String Dim NewEON As Integer Dim NextRow As Integer Dim NewKey As Integer LastRow = Range("A10000").End(xlUp).Row Key = InputBox("EO#") Biggest = -1 For Row = 1 To LastRow If (Cells(Row, 2).Value = Key) And (Cells(Row, 3).Value Biggest) Then Biggest = Cells(Row, 3).Value RowWithBiggest = Row End If Next Row If (Cells(Row, 2).Value) < Key Then ans = MsgBox("The EO Number you typed in " & Key & " does not exist in this log. " _ & vbCr & "Would you like to re-type the number? " & " If No, then a new number" _ & " will be created for you " & vbCr & "Press cancel to Cancel the operation", vbYesNoCancel) End If Select Case ans Case vbYes NewKey = InputBox("NewNumber") For Row = 1 To LastRow If (Cells(Row, 2).Value = NewKey) And (Cells(Row, 3).Value Biggest) Then Biggest = Cells(Row, 3).Value RowWithBiggest = Row End If Next Row Case vbNo NewEON = Application.Max(Range("B4:B10000")) + 1 NextRow = Range("A10000").End(xlUp).Row + 1 Cells(NextRow, 2).Value = NewEON Cells(NextRow, 3) = "0" Exit Sub Case Else Exit Sub End Select NewRow = RowWithBiggest + 1 Rows(NewRow).Select Selection.Insert Shift:=xlDown Cells(NewRow, 1) = "E" Cells(NewRow, 2) = Key Cells(NewRow, 3) = Biggest + 1 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
Error Handling | Excel Programming | |||
Error handling | Excel Programming | |||
Error Handling | Excel Programming |