Advanced Min-Max in Access

[theracer]

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.

what the function does is to iteratively find the max and min in an interval
and smooth them out so as to achieve a linear approximation of the numberset
in the interval. It keeps on adding to the valleys in the numberset and
subtract from the peaks until the sum of those changes equals a predefined
parameter which is the "target".

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

Thanks in advance

 

[revans]

While I generally understand what you're doing I'm not quite sure why you're doing it so I won't comment on the code directly. I can say this type of thing looks like you need to get your data, put it in an array or perhaps a dynamic recordset, then loop through it changing based on your min/max criteria.

The hard part (for me) is then putting the data back in the table. Access data is not like Excel data. Access doesn't care about position within a table, just fields and unique id. When you get your data you'll want to also make sure you mark your data with a unique id so when you put it back you know where to put it.

Now that I think about it a dynamic recordset will probably be most like excel and will address my issue with unique ids. If you create a recordset including your data and the id you should be able to loop, change, and update just fine.

hth

Rich

 

[ajetrumpet]

haven't you posted this question before? the first paragraph is exactly the same as something i read before.

also, if you post your code in a readable manner, you might even get some help with the actual conversion...