Home of SOLAR STARFIRE, 6th edition, rules based on the upcoming history of the Terran Solar Union.

Moderators: SDS Members, SDS Owner

Forum rules
1. Nothing obscene.
3. No personal information. Mostly aimed at the posting of OTHER people's information.
4. No flame wars. We encourage debate, but it becomes a flame when insults fly and tempers flare.

Try to stick with the forum's topic. Threads that belong to another forum will be moved to that forum.

I have been using the Solar Empire Spreadsheets for some time (thanks to all the people who put that together) and decided that the Construction tab needed a little love.

I wrote some VBA code to update the Work Done column when the "Update Data to Next Month" button was pushed. While the code works, I would like someone who has more knowledge to look it over (I'm not a programmer, just an enthusiast).

I put the code just above the update populations section of the update month subroutine.

Cheers

Here is the code (copied from notepad):

' update shipyard construction
If (debugMode = False) Then
' what sheet are we on
currentSheet = "Construction"
' where does the shipyard construction listing start
firstRow = 4
' where does the construction listing end
lastRow = Sheets(currentSheet).Range("B99999").End(xlUp).row
' where is the work done column
Dim workDoneCol As Variant: workDoneCol = "G"
Dim yardRate As Integer: yardRate = 0
' cycle through shipyards from the top row to the last row
For row = firstRow To lastRow
' check to see if row is a shipyard
If Sheets(currentSheet).Range("I" & row).Value = "(SY)" Or Sheets(currentSheet).Range("I" & row).Value = "(SYM)" Or Sheets(currentSheet).Range("I" & row).Value = "(MS)" Then
yardRate = Sheets(currentSheet).Range("I" & (row + 1)).Value
Else
If Sheets(currentSheet).Range("I" & row).Value = "None" Then
yardRate = 0
End If
End If
' check to see if there is a value in the project name column and a number in the unit cost and quantity columns
If Not IsEmpty(Sheets(currentSheet).Range("B" & row)) And _
IsNumeric(Sheets(currentSheet).Range("C" & row).Value) And Sheets(currentSheet).Range("C" & row).Value > 0 And _
IsNumeric(Sheets(currentSheet).Range("D" & row).Value) And Sheets(currentSheet).Range("D" & row).Value > 0 Then
Sheets(currentSheet).Range(workDoneCol & row).Value = Sheets(currentSheet).Range(workDoneCol & row).Value + yardRate
End If
Next row
' end of shipyard construction update
End If

' update planet construction
If (debugMode = False) Then
' what sheet are we on
currentSheet = "Construction"
' where does the shipyard construction listing start
firstRow = 4
' where does the construction listing end
lastRow = Sheets(currentSheet).Range("L99999").End(xlUp).row
' where is the work done column
workDoneCol = "R"
yardRate = 0
' cycle through shipyards from the top row to the last row
For row = firstRow To lastRow
' check to see if row is a shipyard
If Sheets(currentSheet).Range(workDoneCol & row).Value = "(SY)" Or Sheets(currentSheet).Range(workDoneCol & row).Value = "(SYM)" Or _
Sheets(currentSheet).Range(workDoneCol & row).Value = "(MS)" Then
yardRate = Sheets(currentSheet).Range("T" & (row - 1)).Value
Else
If Sheets(currentSheet).Range(workDoneCol & row).Value = "None" Or IsEmpty(Sheets(currentSheet).Range(workDoneCol & row)) Then
yardRate = 0
End If
End If
' check to see if there is a value in the project name column and a number in the unit cost and quantity columns
If Not IsEmpty(Sheets(currentSheet).Range("L" & row)) And _
IsNumeric(Sheets(currentSheet).Range("M" & row).Value) And Sheets(currentSheet).Range("M" & row).Value > 0 And _
IsNumeric(Sheets(currentSheet).Range("N" & row).Value) And Sheets(currentSheet).Range("N" & row).Value > 0 Then
Sheets(currentSheet).Range(workDoneCol & row).Value = Sheets(currentSheet).Range(workDoneCol & row).Value + yardRate
End If
Next row
' end of planet construction update
End If