I was unable to insert the entire code, but i cut out the section that fails. What's interesting is that the other section is a mirror of this code and works perfectly. It exits the do loop properly. Thanks all. P.S. I tried Leith's suggestion to no avail.
Private Sub OK_Click()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim LastRow As Long
Dim rng As Range, rang As Range
Dim addr As String
Set ws = ThisWorkbook.ActiveSheet
Set ws1 = ThisWorkbook.Sheets("Initial Setup")
ScreenUpdating = False
ws1.Activate
1
If IND.Value = True And IASUB = True Then
If Trim(NAM) <> "" Then
With Sheets("Initial Setup").Range("p6002:r13999")
Set rang = .Find(What:=NAM, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rang Is Nothing Then
Do
addr = rang.Address
addr1 = rang.Offset(0, -4)
addr2 = rang.Offset(0, -2)
addr3 = rang.Offset(0, -6)
a = MsgBox("There is a " & rang & ", " & Format(addr1, "(###) ###-####") & " in " & addr3 & ", " & ". Would You like to use this one?", vbYesNoCancel)
If a = vbCancel Then
GoTo 1000
Else
If a = vbYes Then
ws.Activate
If Range("d57").Value <> "" Then
If Range("d61").Value <> "" Then
b = MsgBox("There are no Open IA slots on this claim. Would you like to replace one?", vbYesNoCancel)
If b = vbCancel Then
GoTo 1000
Else
If b = vbNo Then
MsgBox ("Unable to add IA. Both slots are taken. Consider removing one that you are not using anymore.")
GoTo 1000
Else
If b = vbYes Then
c = MsgBox("Would you like to replace IA #1?", vbYesNoCancel)
If c = vbCancel Then
GoTo 1000
Else
If c = vbNo Then
Range("d61").Value = rang
Range("d62").Value = addr1
Range("d63").Value = addr2
Range("d60").EntireRow.Hidden = False
Range("d61").EntireRow.Hidden = False
Range("d62").EntireRow.Hidden = False
Range("d63").EntireRow.Hidden = False
GoTo 1000
Else
If c = vbYes Then
Range("d57").Value = rang
Range("d58").Value = addr1
Range("d59").Value = addr2
Range("d56").EntireRow.Hidden = False
Range("d57").EntireRow.Hidden = False
Range("d58").EntireRow.Hidden = False
Range("d59").EntireRow.Hidden = False
GoTo 1000
End If
End If
End If
End If
End If
End If
Else
If Range("d61") = "" Then
Range("d61").Value = rang
Range("d62").Value = addr1
Range("d63").Value = addr2
Range("d60").EntireRow.Hidden = False
Range("d61").EntireRow.Hidden = False
Range("d62").EntireRow.Hidden = False
Range("d63").EntireRow.Hidden = False
GoTo 1000
End If
End If
Else
If Range("d57") = "" Then
Range("d57").Value = rang
Range("d58").Value = addr1
Range("d59").Value = addr2
Range("d56").EntireRow.Hidden = False
Range("d57").EntireRow.Hidden = False
Range("d58").EntireRow.Hidden = False
Range("d59").EntireRow.Hidden = False
GoTo 1000
End If
End If
GoTo 1000
Else
If a = vbNo Then
End If
End If
End If
Set rang = .FindNext(rang)
Loop Until rang.Address = addr
GoTo 200
Else
If rng Is Nothing Then
200 bb = MsgBox("This Individual will be added to the database for future use. Is that okay?", vbYesNoCancel)
If bb = vbNo Or Cancel Then
GoTo 1000
End If
LastRow = Range("p6002:r13999").End(xlDown).Row + 1
Cells(LastRow, 1).Value = COM.Value
Cells(LastRow, 4).Value = TAX.Value
Cells(LastRow, 6).Value = ADD.Value
Cells(LastRow, 9).Value = CIT.Value
Cells(LastRow, 10).Value = STA.Value
Cells(LastRow, 11).Value = ZIP.Value
Cells(LastRow, 12).Value = PHO.Value
Cells(LastRow, 14).Value = EMA.Value
Cells(LastRow, 16).Value = NAM.Value
Cells(LastRow, 19).Value = "IA"
ws.Activate
If Range("d57").Value <> "" Then
If Range("d61").Value <> "" Then
b = MsgBox("There are no Open IA slots on this claim. Would you like to replace one?", vbYesNoCancel)
If b = vbCancel Then
GoTo 1000
Else
If b = vbNo Then
MsgBox ("Unable to add IA. Both slots are taken. Consider removing one that you are not using anymore.")
GoTo 1000
Else
If b = vbYes Then
c = MsgBox("Would you like to replace IA #1?", vbYesNoCancel)
If c = vbCancel Then
GoTo 1000
Else
If c = vbNo Then
Range("d61").Value = NAM
Range("D62").Value = PHO
Range("D63").Value = EMA
Else
If c = vbYes Then
Range("d57").Value = NAM
Range("d58").Value = PHO
Range("d59").Value = EMA
Range("d56").EntireRow.Hidden = False
Range("d57").EntireRow.Hidden = False
Range("d58").EntireRow.Hidden = False
Range("d59").EntireRow.Hidden = False
End If
End If
End If
End If
End If
End If
Else
If Range("d61") = "" Then
Range("d61").Value = NAM
Range("d62").Value = PHO
Range("d63").Value = EMA
Range("d60").EntireRow.Hidden = False
Range("d61").EntireRow.Hidden = False
Range("d62").EntireRow.Hidden = False
Range("d63").EntireRow.Hidden = False
End If
End If
Else
If Range("d57") = "" Then
Range("d57").Value = NAM
Range("d58").Value = PHO
Range("d59").Value = EMA
Range("d56").EntireRow.Hidden = False
Range("d57").EntireRow.Hidden = False
Range("d58").EntireRow.Hidden = False
Range("d59").EntireRow.Hidden = False
End If
End If
End If
End If
End With
End If
End If
500
1000
ScreenUpdating = True
Unload Me
1001
End Sub
LinkBack URL
About LinkBacks
Register To Reply
My goto's are working fine and my code works fine with the exception of the aforementioned issue. That being said, are you suggesting a better way of peeling the apple with the goto's? As I am a newb, I am always open to suggestions!!! If we're not learning, we're dead. I will upload my file when I get home tonight. Thank you for your help.
Bookmarks