×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

more simplified codes...

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.

RE: more simplified codes...

(OP)
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

RE: more simplified codes...

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

CODE

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!

RE: more simplified codes...

Or a little bit more expanded..

CODE

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

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources