Code 1
Sub ProcessData()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Set the worksheet where your data is located
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
' Find the last row with data in column BJ (assuming BJ has continuous data)
lastRow = ws.Cells(ws.Rows.Count, "BJ").End(xlUp).Row
' Loop through each row
For i = 2 To lastRow ' Assuming your data starts from row 2 (header in row 1)
' Check if there is data in column BG (number of days since last run)
If ws.Cells(i, "BG").Value > 0 Then
' Call a function to process the data based on your conditions
ProcessRow ws, i
End If
Next i
End Sub
Sub ProcessRow(ws As Worksheet, rowNum As Long)
' Add your logic here to process each row based on the conditions you specified
' You can use ws.Cells(rowNum, "BJ") to ws.Cells(rowNum, "BO") to access the data in columns BJ to BO for the current row
' You can use ws.Cells(rowNum, "BG") to get the number of days since the last run
' Example:
Dim exactFinish As Double
Dim exactMargin As Double
Dim lowerFinish As Double
Dim lowerMargin As Double
Dim higherFinish As Double
Dim higherMargin As Double
' Add your logic to calculate the results based on your conditions
' ...
' Output the results to the desired columns (assuming columns BN and BO for this example)
ws.Cells(rowNum, "BN").Value = exactFinish
ws.Cells(rowNum, "BO").Value = exactMargin
End Sub
Code 2
Sub ProcessRow(ws As Worksheet, rowNum As Long)
Dim exactFinish As Double
Dim exactMargin As Double
Dim lowerFinish As Double
Dim lowerMargin As Double
Dim higherFinish As Double
Dim higherMargin As Double
' Get values from columns BJ to BO and BG for the current row
Dim BGValue As Double
Dim finishValue As Double
Dim marginValue As Double
BGValue = ws.Cells(rowNum, "BG").Value
finishValue = ws.Cells(rowNum, "BO").Value ' Assuming finish values are in column BO
marginValue = ws.Cells(rowNum, "BP").Value ' Assuming margin values are in column BP
' Calculate exact finish and margin based on BGValue
exactFinish = CalculateExactFinish(BGValue, finishValue)
exactMargin = CalculateExactMargin(BGValue, marginValue)
' Calculate lower finish and margin based on closest lower BGValue
Dim closestLowerBG As Double
closestLowerBG = FindClosestLowerBG(ws, rowNum, BGValue)
lowerFinish = CalculateLowerFinish(closestLowerBG, ws.Cells(rowNum, "BO").Value) ' Assuming finish values are in column BO
lowerMargin = CalculateLowerMargin(closestLowerBG, ws.Cells(rowNum, "BP").Value) ' Assuming margin values are in column BP
' Calculate higher finish and margin based on closest higher BGValue
Dim closestHigherBG As Double
closestHigherBG = FindClosestHigherBG(ws, rowNum, BGValue)
higherFinish = CalculateHigherFinish(closestHigherBG, ws.Cells(rowNum, "BO").Value) ' Assuming finish values are in column BO
higherMargin = CalculateHigherMargin(closestHigherBG, ws.Cells(rowNum, "BP").Value) ' Assuming margin values are in column BP
' Output the results to the desired columns (assuming columns BN and BO for this example)
ws.Cells(rowNum, "BN").Value = exactFinish
ws.Cells(rowNum, "BO").Value = exactMargin
' Optionally, you can output the results for lower and higher as well
' ws.Cells(rowNum, "BP").Value = lowerFinish
' ws.Cells(rowNum, "BQ").Value = lowerMargin
' ws.Cells(rowNum, "BR").Value = higherFinish
' ws.Cells(rowNum, "BS").Value = higherMargin
End Sub
Function CalculateExactFinish(BGValue As Double, finishValue As Double) As Double
' Replace this with your logic to calculate exact finish
CalculateExactFinish = finishValue ' Placeholder, replace with your actual logic
End Function
Function CalculateExactMargin(BGValue As Double, marginValue As Double) As Double
' Replace this with your logic to calculate exact margin
CalculateExactMargin = marginValue ' Placeholder, replace with your actual logic
End Function
Function FindClosestLowerBG(ws As Worksheet, rowNum As Long, BGValue As Double) As Double
' Replace this with your logic to find the closest lower value in column BG
' This is just a placeholder, replace it with your actual logic
FindClosestLowerBG = BGValue - 1
End Function
Function CalculateLowerFinish(closestLowerBG As Double, finishValue As Double) As Double
' Replace this with your logic to calculate lower finish
CalculateLowerFinish = finishValue ' Placeholder, replace with your actual logic
End Function
Function CalculateLowerMargin(closestLowerBG As Double, marginValue As Double) As Double
' Replace this with your logic to calculate lower margin
CalculateLowerMargin = marginValue ' Placeholder, replace with your actual logic
End Function
Function FindClosestHigherBG(ws As Worksheet, rowNum As Long, BGValue As Double) As Double
' Replace this with your logic to find the closest higher value in column BG
' This is just a placeholder, replace it with your actual logic
FindClosestHigherBG = BGValue + 1
End Function
Function CalculateHigherFinish(closestHigherBG As Double, finishValue As Double) As Double
' Replace this with your logic to calculate higher finish
CalculateHigherFinish = finishValue ' Placeholder, replace with your actual logic
End Function
Function CalculateHigherMargin(closestHigherBG As Double, marginValue As Double) As Double
' Replace this with your logic to calculate higher margin
CalculateHigherMargin = marginValue ' Placeholder, replace with your actual logic
End Function
