more simplified codes...

[tmeister]

Hellow, I think I wrote a sort of complicated code which could be more simplified.
It is excell file (macro included) about scheduling for variable number of event.
It can be excuted macro.
Can I get your help?
Thank you.

 

[tmeister]

I surely attached excell file but I can't find it.
I paste my code..

Private Sub CommandButton1_Click()

Dim i As Integer
Dim j As Integer

Dim cntTxtbox As Integer
Dim Evtnum As Integer

Dim DDiff1 As Integer
Dim DDiff2 As Integer
Dim DDiff3 As Integer
Dim DDiff4 As Integer
Dim DDiff5 As Integer

Dim locator1 As Integer
Dim locator2 As Integer
Dim locator3 As Integer
Dim locator4 As Integer
Dim locator5 As Integer

cntTxtbox = 0

With UserForm1

'--------------------------------------------------
For i = 1 To 15

If Me.Controls("TextBox" & i).Value <> "" Then

cntTxtbox = cntTxtbox + 1

Else

cntTxtbox = cntTxtbox + 0

End If

Next i
'------------------------------------------------------------
If cntTxtbox Mod 3 = 0 Then

Evtnum = cntTxtbox / 3

Else

MsgBox "Please Enter full event information"

End If
'-----------------------------------------------------------------------------
If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" Then

Range("W1").Value = TextBox1.Value
Range("W2").Value = DateSerial(TextBox2.Value, TextBox3.Value, 1)

End If

If TextBox4.Value <> "" And TextBox5.Value <> "" And TextBox6.Value <> "" Then

Range("X1").Value = .TextBox4.Value
Range("X2").Value = DateSerial(.TextBox5.Value, .TextBox6.Value, 1)

End If

If TextBox7.Value <> "" And TextBox8.Value <> "" And TextBox9.Value <> "" Then

Range("Y1").Value = .TextBox7.Value
Range("Y2").Value = DateSerial(.TextBox8.Value, .TextBox9.Value, 1)

End If

If TextBox10.Value <> "" And TextBox11.Value <> "" And TextBox12.Value <> "" Then

Range("Z1").Value = .TextBox10.Value
Range("Z2").Value = DateSerial(.TextBox11.Value, .TextBox12.Value, 1)

End If

If TextBox13.Value <> "" And TextBox14.Value <> "" And TextBox15.Value <> "" Then

Range("AA1").Value = .TextBox13.Value
Range("AA2").Value = DateSerial(.TextBox14.Value, .TextBox15.Value, 1)

End If

'-------------------------------------------------------------------------------

If Evtnum = 1 Then

locator1 = 18

Cells(3, locator1 + 3).Value = Range("W2").Value

End If

If Evtnum = 2 Then

DDiff1 = DateDiff("d", Range("C3"), Range("W2"))
DDiff2 = DateDiff("d", Range("C3"), Range("X2"))

Range("W7").Value = DDiff1
Range("X7").Value = DDiff2

locator1 = Round(Range("W7").Value / Range("X7").Value * 19)
locator2 = Round(Range("X7").Value / Range("X7").Value * 19) - 1

Cells(3, locator1 + 3).Value = Range("W2").Value
Cells(3, locator2 + 3).Value = Range("X2").Value

End If

If Evtnum = 3 Then

DDiff1 = DateDiff("d", Range("C3"), Range("W2"))
DDiff2 = DateDiff("d", Range("C3"), Range("X2"))
DDiff3 = DateDiff("d", Range("C3"), Range("Y2"))

Range("W7").Value = DDiff1
Range("X7").Value = DDiff2
Range("Y7").Value = DDiff3

locator1 = Round(Range("W7").Value / Range("Y7").Value * 19)
locator2 = Round(Range("X7").Value / Range("Y7").Value * 19)
locator3 = Round(Range("Y7").Value / Range("Y7").Value * 19) - 1

Cells(3, locator1 + 3).Value = Range("W2").Value
Cells(3, locator2 + 3).Value = Range("X2").Value
Cells(3, locator3 + 3).Value = Range("Y2").Value

End If

If Evtnum = 4 Then

DDiff1 = DateDiff("d", Range("C3"), Range("W2"))
DDiff2 = DateDiff("d", Range("C3"), Range("X2"))
DDiff3 = DateDiff("d", Range("C3"), Range("Y2"))
DDiff4 = DateDiff("d", Range("C3"), Range("Z2"))

Range("W7").Value = DDiff1
Range("X7").Value = DDiff2
Range("Y7").Value = DDiff3
Range("Z7").Value = DDiff4

locator1 = Round(Range("W7").Value / Range("Z7").Value * 19)
locator2 = Round(Range("X7").Value / Range("Z7").Value * 19)
locator3 = Round(Range("Y7").Value / Range("Z7").Value * 19)
locator4 = Round(Range("Z7").Value / Range("Z7").Value * 19) - 1

Cells(3, locator1 + 3).Value = Range("W2").Value
Cells(3, locator2 + 3).Value = Range("X2").Value
Cells(3, locator3 + 3).Value = Range("Y2").Value
Cells(3, locator4 + 3).Value = Range("Z2").Value


End If

If Evtnum = 5 Then

DDiff1 = DateDiff("d", Range("C3"), Range("W2"))
DDiff2 = DateDiff("d", Range("C3"), Range("X2"))
DDiff3 = DateDiff("d", Range("C3"), Range("Y2"))
DDiff4 = DateDiff("d", Range("C3"), Range("Z2"))
DDiff5 = DateDiff("d", Range("C3"), Range("AA2"))

Range("W7").Value = DDiff1
Range("X7").Value = DDiff2
Range("Y7").Value = DDiff3
Range("Z7").Value = DDiff4
Range("AA7").Value = DDiff5

locator1 = Round(Range("W7").Value / Range("AA7").Value * 19)
locator2 = Round(Range("X7").Value / Range("AA7").Value * 19)
locator3 = Round(Range("Y7").Value / Range("AA7").Value * 19)
locator4 = Round(Range("Z7").Value / Range("AA7").Value * 19)
locator5 = Round(Range("AA7").Value / Range("AA7").Value * 19) - 1

Cells(3, locator1 + 3).Value = Range("W2").Value
Cells(3, locator2 + 3).Value = Range("X2").Value
Cells(3, locator3 + 3).Value = Range("Y2").Value
Cells(3, locator4 + 3).Value = Range("Z2").Value
Cells(3, locator5 + 3).Value = Range("AA2").Value

End If

End With

End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub

Private Sub CommandButton3_Click()

Range("d3:u3").ClearContents

End Sub

 

[yakpol]

tmeister,
The third block of your code can be shortened as presented below. Follow example to modify the rest

Dim tbx1 As Control, tbx2 As Control, tbx3 As Control
Dim i, rng As Range

For i = 1 To 5
    Set tbx1 = Controls("TextBox" & i * 3 - 2)
    Set tbx2 = Controls("TextBox" & i * 3 - 2)
    Set tbx3 = Controls("TextBox" & i * 3 - 2)
    Set rng = Range("W1").Cells(1, i)

    If tbx1.Value <> "" And tbx2.Value <> "" And tbx3.Value <> "" Then
        rng(1, 1).Value = tbx1.Value
        rng(2, 1).Value = DateSerial(tbx2.Value, tbx3.Value, 1)
    End If
Next i

End Sub

hope it helps!

 

[yakpol]

Or a little bit more expanded..

Dim tbx1 As Control, tbx2 As Control, tbx3 As Control
Dim i%, rng As Range, DDiff(1 to 5), locator(1 to 5)

For i = 1 To 5
    Set tbx1 = Controls("TextBox" & i * 3 - 2)
    Set tbx2 = Controls("TextBox" & i * 3 - 1)
    Set tbx3 = Controls("TextBox" & i * 3 )
    Set rng = Range("W1").Cells(1, i)

    If tbx1.Value <> "" And tbx2.Value <> "" And tbx3.Value <> "" Then
        rng(1, 1).Value = tbx1.Value
        rng(2, 1).Value = DateSerial(tbx2.Value, tbx3.Value, 1)
    End If
Next i

If evtnum = 1 Then
    locator1 = 18
    Cells(3, locator1 + 3).Value = Range("W2").Value
Else
    For i = 2 To evtnum
        DDiff(i) = DateDiff("d", Range("C3"), Range("W2")(1, i))
        Range("W7")(1, i).Value = DDiff(i)
        locator(i) = Round(Range("W7")(1, i).Value / Range("X7")(1, i).Value * 19) - IIf(i = evtnum, 1, 0)
        Cells(3, locator(i) + 3).Value = Range("W2")(1, i).Value
    Next i
    
End If