Hi,
I am looking at the transfering the following code from Excel VBA to Access VBA and was wondering if anybody could help me by telling me which factors I should look out for/ how I should change the code so as to make it functional.
What the code does is to take an interval in a set of numbers and smooth out the peaks and valleys by linearising according to a "Target" smoothing factor, i.e. adding or subtracting to the numbers until the addition and subtractions reach the Target.
Code:
Sub test()
Total = 0
num_days = Worksheets("run_tool").Cells(12, 5)
' interval to run the function on. 1 day means 24 numbers to be smoothed
For hour_num = 0 To num_days * 24 - 1 Step 24
tot = 0
'total changes made
Target = Worksheets("run_tool").Cells(8, 5)
'smoothing factor, total subtractions/additions equal this number
inc = Worksheets("run_tool").Cells(9, 5) / 24
'step value for change
Do
' loop to add/subtract till target met
Max = Worksheets("run_tool").Cells(2 + hour_num, 2)
'take first cell as start for max
Min = Worksheets("run_tool").Cells(2 + hour_num, 2)
'first cell as start for min
maxindex = 2 + hour_num
'points at cell 1 for max
minindex = 2 + hour_num
'points at cell 1 for min
Change = 0
'flag for changes not used at present
For i = 2 + hour_num To 24 + hour_num
' loop through remainder of list
x = Worksheets("run_tool").Cells(i + 1, 2)
' pull out value from list - call it x
If x > Max Then
'check if this is a max
Max = x
maxindex = i + 1
End If
If x < Min Then
'check if this is a min
Min = x
minindex = i + 1
End If
Next
Worksheets("run_tool").Cells(maxindex, 2) = Worksheets("run_tool").Cells(maxindex, 2) - inc 'lower max
Worksheets("run_tool").Cells(minindex, 2) = Worksheets("run_tool").Cells(minindex, 2) + inc 'increae min
tot = tot + inc 'add to total changes made
Loop Until (tot >= Target)
'keep going till change is > target change
Total = Total + tot
tot = 0
Worksheets("run_tool").Cells(6, 5) = Total
Next hour_num
End Sub