Create cleaner code than this 1

[tamnet]

Guys,
I have been asked to automate some data in a spreadsheet concerning dates. Basically what they want is to be able to select a date from a calendar which populates a cell and the other cells are populated with other dates based on varying spans i.e

stage 1 = 3 days after selected date
stage 2 = 5 days after stage 1 date
stage 3 = 4 days after stage 2 date

etc. however these stages will be working days (Mon-Fri) so i have created the code below to do this. However I am sure that there is a cleaner way to code this and i was wondering if someone could put me on the right track.

Private Sub Calendar1_Click()
Range("D5").Value = Calendar1.Value
Range("B6").Value = cmbType.Value

    If Range("B6").Value = "Design" Then
    Range("C6").Value = "3 Days"
    Else
    Range("C6").Value = "2 Days"
    End If
    
    If Range("C5").Value = 1 And Range("B6").Value = "Design" Then
    Range("D6").Value = Range("D5").Value + 3       'Design 3 Days (TH)
    Range("D7").Value = Range("D6").Value + 5       'Approve Design/Sample 3 Days (TU)
    Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (TU)
    Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (M)
    Range("D10").Value = Range("D9").Value + 2      'Artwork 2 Days (W)
    Range("D11").Value = Range("D10").Value + 5     'Artwork App 3 Days (M)
    
    Else
    If Range("C5").Value = 1 And Range("B6").Value = "Sample" Then
    Range("D6").Value = Range("D5").Value + 2       'Sample 2 Days (W)
    Range("D7").Value = Range("D6").Value + 5       'Approve Design/Sample 3 Days (M)
    Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (M)
    Range("D9").Value = Range("D8").Value + 4       'Distribution 4 Days (F)
    Range("D10").Value = Range("D9").Value + 4      'Artwork 2 Days (TU)
    Range("D11").Value = Range("D10").Value + 3     'Artwork App 3 Days (F)
        
        Else
        If Range("C5").Value = 2 And Range("B6").Value = "Design" Then
        Range("D6").Value = Range("D5").Value + 3       'Design 3 Days (F)
        Range("D7").Value = Range("D6").Value + 5       'Approve Design/Sample 3 Days (W)
        Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (W)
        Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (TU)
        Range("D10").Value = Range("D9").Value + 2      'Artwork 2 Days (TH)
        Range("D11").Value = Range("D10").Value + 5     'Artwork App 3 Days (TU)
    
        Else
        If Range("C5").Value = 2 And Range("B6").Value = "Sample" Then
        Range("D6").Value = Range("D5").Value + 2       'Sample 2 Days (TH)
        Range("D7").Value = Range("D6").Value + 5       'Approve Design/Sample 3 Days (TU)
        Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (TU)
        Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (M)
        Range("D10").Value = Range("D9").Value + 2      'Artwork 2 Days (W)
        Range("D11").Value = Range("D10").Value + 5     'Artwork App 3 Days (M)
                
            Else
            If Range("C5").Value = 3 And Range("B6").Value = "Design" Then
            Range("D6").Value = Range("D5").Value + 5       'Design 3 Days (M)
            Range("D7").Value = Range("D6").Value + 3       'Approve Design/Sample 3 Days (TH)
            Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (TH)
            Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (W)
            Range("D10").Value = Range("D9").Value + 2      'Artwork 2 Days (F)
            Range("D11").Value = Range("D10").Value + 5     'Artwork App 3 Days (W)
    
            Else
            If Range("C5").Value = 3 And Range("B6").Value = "Sample" Then
            Range("D6").Value = Range("D5").Value + 2       'Sample 2 Days (F)
            Range("D7").Value = Range("D6").Value + 5       'Approve Design/Sample 3 Days (W)
            Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (W)
            Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (TU)
            Range("D10").Value = Range("D9").Value + 2      'Artwork 2 Days (TH)
            Range("D11").Value = Range("D10").Value + 5     'Artwork App 3 Days (TU)
                
                Else
                If Range("C5").Value = 4 And Range("B6").Value = "Design" Then
                Range("D6").Value = Range("D5").Value + 5       'Design 3 Days (TU)
                Range("D7").Value = Range("D6").Value + 3       'Approve Design/Sample 3 Days (F)
                Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (F)
                Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (TH)
                Range("D10").Value = Range("D9").Value + 4      'Artwork 2 Days (M)
                Range("D11").Value = Range("D10").Value + 3     'Artwork App 3 Days (TH)
        
                Else
                If Range("C5").Value = 4 And Range("B6").Value = "Sample" Then
                Range("D6").Value = Range("D5").Value + 4       'Sample 2 Days (M)
                Range("D7").Value = Range("D6").Value + 3       'Approve Design/Sample 3 Days (TH)
                Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (TH)
                Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (W)
                Range("D10").Value = Range("D9").Value + 2      'Artwork 2 Days (F)
                Range("D11").Value = Range("D10").Value + 5     'Artwork App 3 Days (W)
                
                    Else
                    If Range("C5").Value = 5 And Range("B6").Value = "Design" Then
                    Range("D6").Value = Range("D5").Value + 5       'Design 3 Days (W)
                    Range("D7").Value = Range("D6").Value + 5       'Approve Design/Sample 3 Days (M)
                    Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (M)
                    Range("D9").Value = Range("D8").Value + 4       'Distribution 4 Days (F)
                    Range("D10").Value = Range("D9").Value + 4      'Artwork 2 Days (TU)
                    Range("D11").Value = Range("D10").Value + 3     'Artwork App 3 Days (F)
            
                    Else
                    If Range("C5").Value = 5 And Range("B6").Value = "Sample" Then
                    Range("D6").Value = Range("D5").Value + 4       'Sample 2 Days (TU)
                    Range("D7").Value = Range("D6").Value + 3       'Approve Design/Sample 3 Days (F)
                    Range("D8").Value = Range("D7").Value + 7       'Trial 5 Days (F)
                    Range("D9").Value = Range("D8").Value + 6       'Distribution 4 Days (TH)
                    Range("D10").Value = Range("D9").Value + 4      'Artwork 2 Days (M)
                    Range("D11").Value = Range("D10").Value + 3     'Artwork App 3 Days (TH)
                    
                        Else
                        If Range("C5").Value = 6 Or 7 Then
                        Call MsgBox("You Have Entered An Invalid Date, Please Select A Weekday", vbExclamation Or vbDefaultButton1, "Warning")
                        End If
                    End If
                    End If
                End If
                End If
            End If
            End If
        End If
        End If
    End If
    End If
Unload Me
End Sub

Regards

Paul

 

[SkipVought]

Hi,

It would be alot better if you stated your logic requirements in detail, rather then just posting code.

Chances are, this could be coded directly on your worksheet using formulas without the need for VBA.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[tamnet]

Skip,
here are the requirements
to track new requests through the system in an effort to make sure every stage is completed on time. to this effect we have 7 stages to complete

stage 0 = Initial Start Date
stage 1 = 2 Working daysif it is a sample OR 3 working days if it is a design
stage 2 = 3 working days after stage 1 date
stage 3 = 5 working days after stage 2 date
stage 4 = 4 working days after stage 3 date
stage 5 = 2 workingdays after stage 4 date
stage 6 = 3 working days after stage 5 date

The columns for these stages are D511

However these days are working days i.e. Mon-Fri therfore i am trying to automate adding extra days if the date should fall on a week-end.

For example if stage 2 ends on a wednesday then stage 3 should be completed 5 working days after, this would mean that in actual fact I would need to make the date 7 days to allow for the week-end

Hope this makes sense and thanks for the response



Regards

Paul

 

[SkipVought]

Here's a simple shreadsheet approch...

column E is days to add to previous date
E6: =IF(MOD(D5+F6,7)=0,2,IF(MOD(D5+F6,7)=1,1,0))+F6
and copy down
column F is days for stage
F6: =IF(B6="Sample",2,3)
F7:F11 are values for each stage

the 2 cells that get variable data are B6 & D5

B6 can be a Data/Validation - List (in cell drop down)

D5 can be the Calendar object that you can place on the sheet. Make the Visible property TRUE when D5 is SELECTED via the Worksheet_SelectionChange event and assign the Calendar's valus on the Calendar1_Click event, also setting the visible property FALSE.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[tamnet]

Skip,
Thanks for the help


Regards

Paul