more simplified codes...
more simplified codes...
(OP)
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.
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.





RE: more simplified codes...
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
RE: more simplified codes...
The third block of your code can be shortened as presented below. Follow example to modify the rest
CODE
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!
RE: more simplified codes...
CODE
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