I have VBA code that I am needing to convert to script so I can put the sheet online and my macro will run. Can someone please help me with the conversion? Its a worksheet change and a couple . Any help is greatly appreciated!!
Private Sub Worksheet_Change(ByVal Target As Range) vc
Dim ws As Worksheet
Dim c As Range
Dim s() As Variant
Dim sd As Date
Dim ed As Date
Dim cost As Double
Dim crv As String
Dim ColMinDate As Date
Dim StartSpreadVar As Double
Dim StartSpreadNumber As Double
Dim i As Integer
Set ws = Sheets("Data")
Set c = Target
LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Column = 2 And ws.Cells(Target.Row, 1) <> "" Then
For i = 1 To 17
If ws.Cells(Target.Row + 1, 1) = "Architectural/Engineering" Or ws.Cells(Target.Row + 1, 1) = "Construction" Then
crv = ws.Cells(Target.Row + 1, 6).Value
If ws.Cells(c.Row + 1, 4).Value <> 0 Then
cost = ws.Cells(c.Row + 1, 4).Value
If IsDate(ws.Cells(c.Row + 1, 2)) Then
sd = ws.Cells(c.Row + 1, 2).Value
If IsDate(ws.Cells(c.Row + 1, 3)) Then
ed = ws.Cells(c.Row + 1, 3).Value / 1
col = 10
If Cells(c.Row + 1, 6) <> "Manual" Then
ws.Range(Cells(c.Row + 1, 11).Address & ":" & Cells(c.Row + 1, LastCol).Address).Select
Selection.ClearContents
s = GetSpread(cost, crv, sd, ed)
c.Select
ColMinDate = ws.Cells(2, 11)
StartSpreadVar = DateDiff("m", ColMinDate, sd)
StartSpreadNumber = 10 + StartSpreadVar
For x = 1 To UBound(s)
ws.Cells(c.Row + 1, StartSpreadNumber + x).Value = s(x, 2)
Next x
Else
End If
End If
Else
End If
Else
End If
End If
Set c = ws.Cells(c.Row + 1, 2)
Next i
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Option Base 1
Global aCurve As Variant 'Distribution Curve
Global aDays As Variant 'Curve Values with Day Count
Global aSpread As Variant 'Daily Hour Spread
Global aPeriod As Variant 'Period Hour Spread
Global bTesting As Boolean
Function GetSpread(cost As Double, c As String, s As Date, e As Date)
'sCurve = c
'dStartDate = s
'dEndDate = e
LoadCurve c
DistributeHours cost, c, s, e
PeriodSpread "Month", s, e
GetSpread = aPeriod
End Function
'**********************************************************
' Distribute daily hour values throughout date range
'**********************************************************
Sub DistributeHours(cost As Double, sCurve As String, d1 As Date, d2 As Date)
Dim x As Integer
Dim y As Integer
Dim Dur As Integer
Dim PrevDur As Integer
Dim SegLength As Double
Dim StartDate As Date
Dim EndDate As Date
Dim DHrs As Double
StartDate = d1
EndDate = d2
Dur = DateDiff("d", StartDate, EndDate, vbSunday) + 1
SegLength = Dur / 20
ReDim aDays(1 To 20, 3)
If Testing Then
ReDim aSpread(1 To Dur, 8)
Else
ReDim aSpread(1 To Dur, 2)
End If
For x = 1 To UBound(aDays)
aDays(x, 1) = SegLength * x
aDays(x, 2) = Round(SegLength * x, 0)
aDays(x, 3) = aCurve(x)
Next x
y = 1
PrevDur = 0
For x = 1 To UBound(aDays)
DHrs = ((cost * aDays(x, 3)) / (aDays(x, 2) - PrevDur))
While y <= aDays(x, 2)
aSpread(y, 1) = DateAdd("d", (y - 1), StartDate)
aSpread(y, 2) = DHrs
If Testing Then
aSpread(y, 3) = x
aSpread(y, 4) = cost
aSpread(y, 5) = (cost * aDays(x, 3))
'aSpread(y, 6) = aDays(x, 1)
aSpread(y, 6) = PrevDur
aSpread(y, 7) = aDays(x, 2)
aSpread(y, 8) = aDays(x, 3)
End If
y = y + 1
Wend
PrevDur = aDays(x, 2)
Next x
End Sub
' ******************************************************
' Select a distribution curve, place into an array
' ******************************************************
Sub LoadCurve(strCurve As String)
ReDim aCurve(20)
Select Case strCurve
Case "Linear"
aCurve = Array(0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05)
Case "Back Loaded"
aCurve = Array(0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065)
Case "Bell Shaped"
aCurve = Array(0.005, 0.005, 0.015, 0.015, 0.04, 0.04, 0.075, 0.075, 0.115, 0.115, 0.115, 0.115, 0.075, 0.075, 0.04, 0.04, 0.015, 0.015, 0.005, 0.005)
Case "Front Loaded"
aCurve = Array(0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035, 0.035)
Case "Offset Triangular"
aCurve = Array(0.01, 0.01, 0.025, 0.025, 0.035, 0.035, 0.05, 0.05, 0.065, 0.065, 0.075, 0.075, 0.085, 0.085, 0.07, 0.07, 0.05, 0.05, 0.035, 0.035)
Case "Three Step"
aCurve = Array(0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.065, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04)
Case "Trapezoidal"
aCurve = Array(0.01, 0.01, 0.035, 0.035, 0.055, 0.055, 0.075, 0.075, 0.075, 0.075, 0.075, 0.075, 0.075, 0.075, 0.055, 0.055, 0.035, 0.035, 0.01, 0.01)
Case "Triangular"
aCurve = Array(0.01, 0.01, 0.03, 0.03, 0.05, 0.05, 0.07, 0.07, 0.09, 0.09, 0.09, 0.09, 0.07, 0.07, 0.05, 0.05, 0.03, 0.03, 0.01, 0.01)
Case "Triangular Decrease"
aCurve = Array(0.09, 0.09, 0.085, 0.085, 0.07, 0.07, 0.065, 0.065, 0.055, 0.055, 0.045, 0.045, 0.035, 0.035, 0.03, 0.03, 0.015, 0.015, 0.01, 0.01)
Case "Triangular Increase"
aCurve = Array(0.01, 0.01, 0.015, 0.015, 0.03, 0.03, 0.035, 0.035, 0.045, 0.045, 0.055, 0.055, 0.065, 0.065, 0.07, 0.07, 0.085, 0.085, 0.09, 0.09)
Case "Double Peak"
aCurve = Array(0.013, 0.025, 0.038, 0.051, 0.076, 0.101, 0.076, 0.051, 0.038, 0.025, 0.025, 0.025, 0.038, 0.051, 0.076, 0.101, 0.076, 0.051, 0.038, 0.025)
Case "Early Peak"
aCurve = Array(0.012, 0.025, 0.038, 0.05, 0.075, 0.101, 0.101, 0.101, 0.088, 0.075, 0.063, 0.05, 0.05, 0.05, 0.038, 0.025, 0.02, 0.015, 0.013, 0.01)
Case "Manual"
Erase aCurve
End Select
End Sub
'**********************************************************
' Summarize total hours by period
'**********************************************************
Function PeriodSpread(Dur As String, sd As Date, ed As Date) As Variant
Dim Num As Integer
Dim PeriodTotal As Double
Dim Period As Integer
x = 1
Select Case Dur
Case "Day"
Case "Week"
Case "Month"
Num = DateDiff("m", sd, ed, vbSunday) + 1
ReDim aPeriod(Num, 2)
For y = 1 To UBound(aPeriod)
PeriodTotal = 0
Period = Month(aSpread(x, 1))
Do While x <= UBound(aSpread)
If Period = Month(aSpread(x, 1)) Then
PeriodTotal = PeriodTotal + (aSpread(x, 2))
x = x + 1
Else
Exit Do
End If
Loop
aPeriod(y, 1) = y
aPeriod(y, 2) = PeriodTotal
Next y
Case "Quarter"
Case "Year"
End Select
End Function