function to clean up code 2

[chilly442]

Excel 2003 VBA
The code works as is.
But I want to write a function or sub routine that will clean up the code:

Private Sub cmdAdd_Click()
Dim ws As Worksheet, MyYear As Integer, MyMonth As String
Dim MyRow As Long, MyCol As Integer
Dim response As Integer
Set ws = Worksheets("Data2")

MyMonth = Me.MyMonth.Value
MyYear = Me.MyYear.Value

MyRow = ws.Range("C4:C22").Find(what:=MyYear).Row
MyCol = ws.Range("C4:O4").Find(what:=MyMonth).Column


'First IF Statement
If ws.Cells(MyRow, MyCol) <> "" Then
Select Case MsgBox("Are you sure you want to overwrite the data?", vbYesNo, "Are you sure")
Case vbYes

Case vbNo
GoTo Last
End Select
End If

'Second IF Statement
If Not Me.Number < 0 And Me.Number > 100 Then
MsgBox "Number must be between 0 and 100", vbYes, "Number "
GoTo Last
Else
ws.Cells(MyRow, MyCol) = Me.Number.Value
End If
MsgBox "Finished Inputting Data", vbYes, "Finished"
Unload Me
Last:
End Sub

If the First If statement is true then I want it to run the Second IF statement.
I want to call the First IF Statement in a function so that I don't have to copy and paste this into the 200 values that will be entered into the sheet.
In the second If statement the number value changes, and the range changes for each entry I have.

Any Ideas?
Thanks in advance for the help.
Chilly442

 

[JTBorton]

Well, I'm more or less an amature myself, but it looks to me like there are a couple ways you can do it. I'm not completely sure what you are trying to accomplish, but you could probably just do a private sub instead of a function.

[COLOR=darkblue]Private Sub[/color] cmdAdd_Click()
[COLOR=darkblue]Dim[/color] ws [COLOR=darkblue]As[/color] Worksheet, MyYear [COLOR=darkblue]As Integer[/color], MyMonth [COLOR=darkblue]As String[/color]
[COLOR=darkblue]Dim[/color] MyRow [COLOR=darkblue]As Long[/color], MyCol [COLOR=darkblue]As Integer[/color]
[COLOR=darkblue]Dim[/color] response [COLOR=darkblue]As Integer[/color]
    [COLOR=darkblue]Set[/color] ws = Worksheets("Data2")
  
    MyMonth = Me.MyMonth.Value
    MyYear = Me.MyYear.Value
     
    MyRow = ws.Range("C4:C22").Find(what:=MyYear).Row
    MyCol = ws.Range("C4:O4").Find(what:=MyMonth).Column
  
  
    [COLOR=green]'First IF Statement[/color]
    [COLOR=darkblue]If[/color] ws.Cells(MyRow, MyCol) <> "" [COLOR=darkblue]Then[/color]
        [COLOR=darkblue]Select Case[/color] MsgBox("Are you sure you want to overwrite the data?", vbYesNo, "Are you sure")
        [COLOR=darkblue]Case[/color] vbYes
            [COLOR=darkblue]Call[/color] SecondIfStatement(ws, MyRow, MyCol)
        [COLOR=darkblue]Case[/color] vbNo
            [COLOR=darkblue]Exit Sub[/color]
        [COLOR=darkblue]End Select[/color]
    [COLOR=darkblue]End If[/color]
    
[COLOR=darkblue]End Sub


Private Sub[/color] SecondIfStatement([COLOR=darkblue]ByRef[/color] ws, [COLOR=darkblue]ByVal[/color] MyRow [COLOR=darkblue]As Long[/color], [COLOR=darkblue]ByVal[/color] MyCol [COLOR=darkblue]As Integer[/color])
    [COLOR=darkblue]If Not[/color] Me.Number < 0 [COLOR=darkblue]And[/color] Me.Number > 100 [COLOR=darkblue]Then[/color]
        MsgBox "Number must be between 0 and 100", vbYes, "Number "
        [COLOR=darkblue]Exit Sub[/color]
        [COLOR=green]'Exiting the sub here will send you to the end of the Select case, which is also the end of the previous sub.  Therefor you acheive the same result[/color]
    [COLOR=darkblue]Else[/color]
        ws.Cells(MyRow, MyCol) = Me.Number.Value
    [COLOR=darkblue]End If[/color]
    
    MsgBox "Finished Inputting Data", vbYes, "Finished"
    Unload Me
[COLOR=darkblue]End Sub[/color]

 

[SkipVought]

So you want to loop until a condition is met...

    Do While ws.Cells(MyRow, MyCol) <> ""
        If Not Me.Number < 0 And Me.Number > 100 Then
          Exit Do
        Else
          ws.Cells(MyRow, MyCol) = Me.Number.Value
        End If
    Loop
    MsgBox "Finished Inputting Data", vbYes, "Finished"
    Unload Me

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

I have never seen this "Me" thingy, and I can't find it in the Microsoft help. What exactly is it?

 

[chilly442]

Me refers to the user form that the information is entered into. So Me.Number.Value tells the code to get the value "Number" from the form.