Hello,
I have been trying to modify this code in many many different ways with an attempt to make it as efficient as possible, however, I have not been very successful. If anyone can help prevent me from going crazy, I would certainly appreciate it. Here is all I am trying to do... I have a simple userform with two textboxes (RMA, CCA) and two optionbuttons in a frame. OptionButton 1 begins the process for ("Outgoing"). Search for CCA in Column B, if not found then ok to enter a new row. A1= RMA, B1= CCA, C1 = Date. OptionButton 2 selected, search CCA, if found in Column B then append to row. D1= RMA, E1= CCA, F1= Date. If Optionbutton2 ("Incoming") and NOT found, then give error and exit sub. Thanks for any help!
I have been trying to modify this code in many many different ways with an attempt to make it as efficient as possible, however, I have not been very successful. If anyone can help prevent me from going crazy, I would certainly appreciate it. Here is all I am trying to do... I have a simple userform with two textboxes (RMA, CCA) and two optionbuttons in a frame. OptionButton 1 begins the process for ("Outgoing"). Search for CCA in Column B, if not found then ok to enter a new row. A1= RMA, B1= CCA, C1 = Date. OptionButton 2 selected, search CCA, if found in Column B then append to row. D1= RMA, E1= CCA, F1= Date. If Optionbutton2 ("Incoming") and NOT found, then give error and exit sub. Thanks for any help!
Code:
Private Sub InputButton_Click()
'Force OptionButton selection
If Me.Frame1.OptionButton1.Value = False And Me.Frame1.OptionButton2.Value = False Then
MsgBox "Select INCOMING or OUTGOING!", vbExclamation, ""
Exit Sub
End If
'Find empty cell column A
Set c = Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
'Find duplicate SN Number
Dim strFind As String
Dim rLookRnge As Range
Dim rngLastCell As Range
strFind = Me.CCA.Text
Set rLookRnge = Sheets("CRR").Columns(1)
Set rngLastCell = Range("B:B").Find(what:=strFind, _
After:=Range("B2"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Not rngLastCell Is Nothing Then
MsgBox strFind & " - SN already exists!", vbCritical, "Error"
Exit Sub
End If
''''''' Option Button 1 = OUTGOING ''''''
If Frame1.OptionButton1.Value = True Then
'Submit values for Outgoing
With Me
c.Value = .RMA.Value
c.Offset(0, 1).Value = .CCA.Value
c.Offset(0, 2).Value = Date
c.Offset(0, 9).Value = strUserName
End With
End If
If Frame1.OptionButton2.Value = True Then
'Submit values for Incoming
With Me
c.Offset(-1, 4).Value = .CCA.Value
c.Offset(-1, 5).Value = Date
c.Offset(-1, 10).Value = strUserName
c.Select
End With
End If
Me.CCA = ""
End Sub