UserForm

[sdoppke]

Hi everyone I have a sub that works in a module but not a userform, would anyone help me to modify this to work in my userform. I need to learn from any changes you make so that I can do it to a couple others. I never new you could write scripts in the same way.

Right now im getting hung up on the part of "Set Found" in red...

Private Sub CommandButton1_Click() 'input
Dim ws As Worksheet
Dim wb As Workbook
Dim FindValue As String, Box As String, putName As String, FindName As String, FindNumber As String, Found As String
Dim RowNum As Integer, ColNum As Integer
Dim r As Integer
Application.ScreenUpdating = False
FindValue = Me.Calendar1.Value
If Me.OptionButton1 = True Then
Box = "O"
End If
If Me.OptionButton2 = True Then
Box = "P"
End If
    On Error GoTo finish
    Range("P11:AD75").Select
   Selection.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
If Me.CheckBox1 = True Then
    putName = Me.TextBox1.Value & " " & Me.TextBox2.Value
    For r = 1 To 10
        If Cells(RowNum + r, ColNum) = "" Or Cells(RowNum + r, ColNum) = putName Then
            Cells(RowNum + r, ColNum) = putName
            Cells(RowNum + r, ColNum + 1) = Box
            
    'begin part that inputs the letter to Schedule Template
FindName = Me.TextBox1.Value & " " & Me.TextBox2.Value
FindNumber = Me.Calendar1.Value
For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
ws.Visible = True
ws.Rows.Hidden = False
ws.Select
ActiveSheet.Range("A:A").Select
     On Error GoTo finish
        [COLOR=red]Set Found[/COLOR] = ws.Columns("A:A").Find(What:=FindNumber, After:=Range("A3"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not[COLOR=red] Found[/COLOR] Is Nothing Then
   
    Selection.FindNext(After:=ActiveCell).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Find(What:=FindName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
        
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
    Cells(RowNum, ColNum + 88) = Box
    End If
 
Next
'end part that inputs the letter to Schedule Template
            For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
            ws.Visible = False
            Next
            Sheets("MyStoreInfo").Select
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
            
            ElseIf Cells(RowNum + r, ColNum + 1) = putName Then
            
            MsgBox ("You have already input that name here.")
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
    
            Else
            If r = 10 Then
                MsgBox ("No more room")
                Range("Q9").Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
            
        End If
    Next
            
finish:
    Err.Clear
    For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
    ws.Visible = False
    Next
    Range("Q9").Select
    Application.ScreenUpdating = True
    MsgBox ("Date not found")
End If
End Sub

thanks for any help or direction.

sd

 

[Norie]

sd

Removing the On Error statements wouldn't solve the problem, but it might have helped find the root of the problem.

Anyway, looks like rorya spotted the problem.