Complex rig utilization calendar thing, need help with VBA

A

amkrafie

New Member
Joined
Feb 24, 2016
Messages
3
Hey,

so I'm trying to create an excel file where engineers would input information about an upcoming job, then have it auto-populate the calendars per the days of the job and of each stage in the job. I got the rolling calendar running for the quarterly and monthly and weekly view, figured out the adding of the information through the userform, and now I'm kinda stuck having the calendar populate based on the information in the job data sheet. I'm pretty sure I'm using waaay too many loops :eek: and stuff and making much harder for me to follow, and probably I'm missing something simple. I started learning VBA and excel two weeks ago, so I'm just winging it as I go. Google has been pretty helpful but I'm just stuck at this part. :oops:

eventually we want it to start to predict the next upcoming jobs based on previous job data, but right now I just want the basics of it to work.

This is my file https://www.dropbox.com/s/5gm1k3zbak3rb57/Excel Database.xlsm?dl=0

and below is the vba code

any help or suggestions would be greatly appreciated


Med






Code: 
Sub AddDataRow(tableName As String, values() As Variant)
    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer
    Dim lastRow As Range


    Set sheet = ActiveWorkbook.Worksheets("Job Data")
    Set table = sheet.ListObjects.Item(tableName)


    'First check if the last row is empty; if not, add a row
    If table.ListRows.Count > 0 Then
        Set lastRow = table.ListRows(table.ListRows.Count).Range
        For col = 1 To lastRow.Columns.Count
            If Trim(CStr(lastRow.Cells(1, col).Value)) <> "" Then
                table.ListRows.Add
                Exit For
            End If
        Next col
    Else
        table.ListRows.Add
    End If


    'Iterate through the last row and populate it with the entries from values()
    Set lastRow = table.ListRows(table.ListRows.Count).Range
    For col = 1 To lastRow.Columns.Count
        If col <= UBound(values) + 1 Then lastRow.Cells(1, col) = values(col - 1)
    Next col
End Sub




Private Sub CommandButton1_Click()


'Copy input values to sheet.
    Dim x(11)
    
        x(0) = Me.JobNum.Value
        x(1) = Me.RigName.Value
        x(2) = Me.WellName.Value
        x(3) = Me.JobType.Value
        x(4) = Me.SpudDate.Value
        x(5) = Me.InitialStage.Value
        x(6) = Me.Stage1.Value
        x(7) = Me.Stage2.Value
        x(8) = Me.Stage3.Value
        x(9) = Me.Stage4.Value
        x(10) = Me.Stage5.Value
        x(11) = Me.Stage6.Value
        AddDataRow "JobData", x
    'Clear input controls.
        Me.JobNum.Value = "KSA-16-"
        Me.RigName.Value = ""
        Me.WellName.Value = ""
        Me.JobType.Value = ""
        Me.SpudDate.Value = Date
        Me.InitialStage.Value = ""
        Me.Stage1.Value = ""
        Me.Stage2.Value = ""
        Me.Stage3.Value = ""
        Me.Stage4.Value = ""
        Me.Stage5.Value = ""
        Me.Stage6.Value = ""
        Me.ListBox1.List = ActiveWorkbook.Worksheets("Job Data").ListObjects.Item("JobData").DataBodyRange.Value
        Me.ListBox1.ColumnCount = 12
        Me.ListBox1.ColumnWidths = "60;60;60;80;60;60;40;40;40;40;40;40"


End Sub
















Private Sub CommandButton3_Click()
Me.CommandButton1.Enabled = True
Me.CommandButton6.Enabled = False
    'Clear input controls.
        Me.JobNum.Value = "KSA-16-"
        Me.RigName.Value = ""
        Me.WellName.Value = ""
        Me.JobType.Value = ""
        Me.InitialStage.Value = ""
        Me.Stage1.Value = ""
        Me.Stage2.Value = ""
        Me.Stage3.Value = ""
        Me.Stage4.Value = ""
        Me.Stage5.Value = ""
        Me.Stage6.Value = ""
End Sub


Private Sub CommandButton4_Click()
    'Close UserForm.
    Unload Me
End Sub








Private Sub CommandButton5_Click()
    Dim sheet As Worksheet
    Dim table As ListObject
    Set sheet = ActiveWorkbook.Worksheets("Job Data")
    Set table = sheet.ListObjects.Item("JobData")
    Answer = MsgBox("Are you sure you want to delete the currently selected table row? " & _
       " This cannot be undone...", vbYesNo, "Delete Row?")
    If Answer = vbYes Then table.ListRows(ListBox1.ListIndex + 1).Range.Delete xlShiftUp
     Me.ListBox1.List = ActiveWorkbook.Worksheets("Job Data").ListObjects.Item("JobData").DataBodyRange.Value
     ListBox1.ColumnCount = 12
     ListBox1.ColumnWidths = "60;60;60;80;60;60;40;40;40;40;40;40"
End Sub


Private Sub CommandButton6_Click()
    Dim sheet As Worksheet
    Dim table As ListObject
Set sheet = ActiveWorkbook.Worksheets("Job Data")
Set table = sheet.ListObjects.Item("JobData")
With ListBox1
    If .ListIndex > -1 Then
        table.DataBodyRange.Cells(.ListIndex + 1, 1) = Me.JobNum.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 2) = Me.RigName.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 3) = Me.WellName.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 4) = Me.JobType.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 5) = Me.SpudDate.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 6) = Me.InitialStage.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 7) = Me.Stage1.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 8) = Me.Stage2.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 9) = Me.Stage3.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 10) = Me.Stage4.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 11) = Me.Stage5.Value
        table.DataBodyRange.Cells(.ListIndex + 1, 12) = Me.Stage6.Value
     End If
End With
        Me.ListBox1.List = table.DataBodyRange.Value
        Me.ListBox1.ColumnCount = 12
        Me.ListBox1.ColumnWidths = "60;60;60;80;60;60;40;40;40;40;40;40"
End Sub


Private Sub CommandButton7_Click()
    Dim sheet As Worksheet
    Dim table1 As ListObject
    Dim sheet2 As Worksheet
    Dim table2 As ListObject
    Dim table3 As Range
    Dim datecountmin As Long
    Dim datecountmax As Long
    Dim SpudDate As Long
    Dim stagecounter As Long
    Dim stagetotal As Long
    Set sheet2 = ActiveWorkbook.Worksheets("Quarterly")
    Set table1 = sheet2.ListObjects.Item("QuarterView")
    Set sheet = ActiveWorkbook.Worksheets("Job Data")
    Set table2 = sheet.ListObjects.Item("JobData")
    Set table3 = Range("QuarterDates")
    
    For ii = 2 To table1.ListRows.Count
        For iii = 2 To table1.ListColumns.Count
            table1.DataBodyRange.Cells(ii, iii).Value = ""
        Next iii
    Next ii
    
    
    ' go through the job data rows
    For i = 1 To table2.ListRows.Count
    
        ' for each row in the job data go through the rig names in quarter view
        For i2 = 1 To table1.ListRows.Count
        
            ' if the rig names match in job data and quarter view
            If table1.DataBodyRange.Cells(i2, 1).Value = table2.DataBodyRange.Cells(i, 2).Value Then
            
                'spud date
                datecountmin = table2.DataBodyRange.Cells(i, 5).Value
                    
                'days total in job
                stagetotal = table2.DataBodyRange.Cells(i, 13).Value
                    
                'date of completion
                datecountmax = datecountmin + stagetotal
                
                'go through the quarter view date coloumns
                For i3 = 1 To table3.Columns.Count
                
               
                    'if date in calender is less than the date of completion and greater than or equal to spud date
                    If datecountmax >= table3.Cells(1, i3).Value And table3.Cells(1, i3).Value >= datecountmin Then
                    
                        'go through each stage
                        For i4 = 1 To 6
                        
                            'get value of days into stage counter per stage
                            stagecounter = table2.DataBodyRange.Cells(i, 6 + i4).Value
                            
                            'if the stage counter is greater than 0
                            If stagecounter > 0 Then
                            
                                    'count from 0 to total days per current stage
                                    For i5 = 0 To stagecounter
                                    
                                        'count from minimum to max date
                                        For i7 = datecountmin To datecountmax
                                        
                                        'if the date in the calender matches
                                        If i7 = table3.Cells(1, i3).Value Then
                                        
                                            'put Y into the corrospnding date and rig
                                            table1.DataBodyRange.Cells(i2, i3 + i5).Value = "Y"
                                            
                                        End If
                                        Next i7
                                    Next i5
                                
                            End If
                        Next i4
                    End If
                Next i3
            End If
        Next i2
    Next i
    
            
                                    
                          
                                
                            
            
    
    Application.Calculate
End Sub


Private Sub InitialStage_Change()
        Me.Stage1.Value = ""
        Me.Stage2.Value = ""
        Me.Stage3.Value = ""
        Me.Stage4.Value = ""
        Me.Stage5.Value = ""
        Me.Stage6.Value = ""
    Set sheet = ActiveWorkbook.Worksheets("Average Times")
    Set table = sheet.ListObjects.Item("AverageTimes")
    Set C = table.Range.Find(Me.JobType.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Me.InitialStage.Value = "24" Then
        Me.Stage1.Value = C.Offset(0, 1).Value
        Me.Stage2.Value = C.Offset(0, 2).Value
        Me.Stage3.Value = C.Offset(0, 3).Value
        Me.Stage4.Value = C.Offset(0, 4).Value
        Me.Stage5.Value = C.Offset(0, 5).Value
        Me.Stage6.Value = C.Offset(0, 6).Value
    ElseIf Me.InitialStage.Value = "18.625" Then
        Me.Stage1.Value = C.Offset(0, 2).Value
        Me.Stage2.Value = C.Offset(0, 3).Value
        Me.Stage3.Value = C.Offset(0, 4).Value
        Me.Stage4.Value = C.Offset(0, 5).Value
        Me.Stage5.Value = C.Offset(0, 6).Value
        Me.Stage6.Value = "0"
    ElseIf Me.InitialStage.Value = "13.375" Then
        Me.Stage1.Value = C.Offset(0, 3).Value
        Me.Stage2.Value = C.Offset(0, 4).Value
        Me.Stage3.Value = C.Offset(0, 5).Value
        Me.Stage4.Value = C.Offset(0, 6).Value
        Me.Stage5.Value = "0"
        Me.Stage6.Value = "0"
    ElseIf Me.InitialStage.Value = "9.625" Then
        Me.Stage1.Value = C.Offset(0, 4).Value
        Me.Stage2.Value = C.Offset(0, 5).Value
        Me.Stage3.Value = C.Offset(0, 6).Value
        Me.Stage4.Value = "0"
        Me.Stage5.Value = "0"
        Me.Stage6.Value = "0"
    ElseIf Me.InitialStage.Value = "7" Then
        Me.Stage1.Value = C.Offset(0, 5).Value
        Me.Stage2.Value = C.Offset(0, 6).Value
        Me.Stage3.Value = "0"
        Me.Stage4.Value = "0"
        Me.Stage5.Value = "0"
        Me.Stage6.Value = "0"
    ElseIf Me.InitialStage.Value = "4.5" Then
        Me.Stage1.Value = C.Offset(0, 6).Value
        Me.Stage2.Value = "0"
        Me.Stage3.Value = "0"
        Me.Stage4.Value = "0"
        Me.Stage5.Value = "0"
        Me.Stage6.Value = "0"
    End If
    
    
    
        
    End Sub
















Private Sub ToggleButton1_Click()
If ToggleButton1.Value = False Then
    Application.Visible = False
   End If
   If ToggleButton1.Value = True Then
    Application.Visible = True
    
End If
End Sub


Private Sub UserForm_Initialize()
Me.CommandButton1.Enabled = True
Me.CommandButton6.Enabled = False
Set sheet = ActiveWorkbook.Worksheets("Job Data")
Set table = sheet.ListObjects.Item("JobData")
Me.JobNum.Value = "KSA-16-"
Me.SpudDate.Value = Date
With UserForm2
' ListBox1.RowSource = "JobData"
 ListBox1.List = table.DataBodyRange.Value
 ListBox1.ColumnCount = 12
 ListBox1.ColumnWidths = "60;60;60;80;60;60;40;40;40;40;40;40"
End With
End Sub




Private Sub CommandButton2_Click()
Me.CommandButton1.Enabled = False
Me.CommandButton6.Enabled = True
With ListBox1
    If .ListIndex > -1 Then
        Me.JobNum.Value = .List(.ListIndex, 0)
        Me.RigName.Value = .List(.ListIndex, 1)
        Me.WellName.Value = .List(.ListIndex, 2)
        Me.JobType.Value = .List(.ListIndex, 3)
        Me.SpudDate.Value = .List(.ListIndex, 4)
        Me.InitialStage.Value = .List(.ListIndex, 5)
        Me.Stage1.Value = .List(.ListIndex, 6)
        Me.Stage2.Value = .List(.ListIndex, 7)
        Me.Stage3.Value = .List(.ListIndex, 8)
        Me.Stage4.Value = .List(.ListIndex, 9)
        Me.Stage5.Value = .List(.ListIndex, 10)
        Me.Stage6.Value = .List(.ListIndex, 11)
     End If
End With
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
please excuse the horrible code and the redundant sheets and information. it started out small and just kept getting bigger as the work progressed.
 
Upvote 0
Basically the sheet should do this:

1. The user inputs data from a userform including start date and duration of each stage if any
2. it goes through the data input / in the job data sheet
3. compares dates with dates in rolling calendar sheet
4. inputs the corresponding stage in the correct dates / row in the calender

This is my excel file https://www.dropbox.com/s/g39s63yyc1crga7/Excel Database EDIT.xlsm?dl=0


Med


and I think this is the code that's incorrect

Code: 
[/B]

    Private Sub CommandButton7_Click()
        Dim sheet As Worksheet
        Dim table1 As ListObject
        Dim sheet2 As Worksheet
        Dim table2 As ListObject
        Dim table3 As Range
        Dim datecountmin As Long
        Dim datecountmax As Long
        Dim SpudDate As Long
        Dim stagecounter As Long
        Dim stagetotal As Long
        Dim tempstage As Long
        Set sheet2 = ActiveWorkbook.Worksheets("Quarterly")
        Set table1 = sheet2.ListObjects.Item("QuarterView")
        Set sheet = ActiveWorkbook.Worksheets("Job Data")
        Set table2 = sheet.ListObjects.Item("JobData")
        Set table3 = Range("QuarterDates")
        Dim datelong As Long
        
        For ii = 1 To 90
            table1.ListColumns(ii + 1).DataBodyRange.ClearContents
        Next ii
        
        ' go through the job data rows
        For i = 1 To table2.ListRows.Count
        
            ' for each row in the job data go through the rig names in quarter view
            For i2 = 1 To table1.ListRows.Count
            
                ' if the rig names match in job data and quarter view
                If table1.DataBodyRange.Cells(i2, 1).Value = table2.DataBodyRange.Cells(i, 2).Value Then
                
                    'spud date
                    datecountmin = table2.DataBodyRange.Cells(i, 5).Value
                        
                    'days total in job
                    stagetotal = table2.DataBodyRange.Cells(i, 13).Value
                        
                    'date of completion
                    datecountmax = datecountmin + stagetotal
                    
                    
                    'go through the quarter view date coloumns
                    For i3 = 1 To table3.Columns.Count
                    
                        
                        'if date in calender is less than the date of completion and greater than or equal to spud date
                        If datecountmax >= CLng(table3.Cells(1, i3).Value) And CLng(table3.Cells(1, i3).Value) >= datecountmin Then
                                                
                                                                                                
                            'go through each stage
                            For i4 = 1 To 6
                                
                                'get value of days into stage counter per stage
                                stagecounter = table2.DataBodyRange.Cells(i, 6 + i4).Value
                                
                                
                                tempstage = stagecounter + datecountmin
                                
                                'if the stage counter is greater than 0
                                If stagecounter > 0 Then
                                
                                        'count from 0 to total days per current stage
                                        For i5 = 1 To stagecounter
                                        
                                            'count from minimum to max date
                                            For i6 = datecountmin To tempstage
                    
                                            'if the date in the calender matches
                                            If i6 = CLng(table3.Cells(1, i3).Value) Then
                                            
                                                'put stage into the corrospnding date and rig
                                                table1.DataBodyRange.Cells(i2, i3 + i5).Value = table2.Range.Cells(1, 6 + i4).Value
                                            
                                            End If
                                            Next i6
                                        Next i5
                                
                                datecountmin = CLng(table3.Cells(1, i3 + i5 - 1).Value)
                                
                                End If
                            Next i4
                        End If
                    Next i3
                End If
            Next i2
        Next i
    
        Application.Calculate
    End Sub


[B]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
202
Edgar_
Edgar_
P
  • Question
Putting data into ListBox1 from UserForm6 to Userform1 listbox
Replies
5
Views
298
dmt32
D
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
575
pure vito
P
SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
367
SemperFi
SemperFi
D
  • Question
Helpwith my password sreadsheet please.
Replies
5
Views
490
dmt32
D

Forum statistics

Threads
1,215,968
Messages
6,127,983
Members
449,414
Latest member
sameri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top