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

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

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