Code in Excel - adjust for Access

This is our rubberband macro(actually two macros here; one is called FindNext
and is called from the first macro) . We use it in Excel to against the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12187-code-in-excel-adjust-for-access.html
“tool� data. It “starts� at IAP_Benchmark of 0, and does the code, generating
the factors you see here.
I have played around trying to get this code to work against the same data
Iin an Access database. Any suggestions?

Log_Dist IAP_Benchmark Factor
-15.91
-2.62
-1.40
0.00 0 1.075
1.40
4.39
6.51 7 0.987
8.01
19.68 20 1.012
28.92
36.99
38.46 39 1




On Error GoTo err_Handler

Dim currentBenchmark As Long
Dim newBenchmark As Long
Dim currentFootage As Double
Dim newFootage As Double
Dim deltaBenchmark As Double
Dim deltaFootage As Double
Dim newFactor As Double
Dim factorCell
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12187

currentBenchmark = ActiveCell.Value
currentFootage = ActiveCell.Offset(0, -1).Value

Do
'Get new factor cell
factorCell = ActiveCell.Offset(0, 1).Address
ActiveCell.Offset(1).Activate
FindNext
newBenchmark = ActiveCell.Value
newFootage = ActiveCell.Offset(0, -1).Value

deltaBenchmark = newBenchmark - currentBenchmark
deltaFootage = newFootage - currentFootage
If deltaFootage > 0 Then
newFactor = deltaBenchmark / deltaFootage
Else
Range(factorCell).Value = 1
Exit Sub
End If

' Place factor in factor cell
Range(factorCell).Value = newFactor

currentBenchmark = newBenchmark
currentFootage = newFootage

Loop Until ActiveCell.Offset(1, -1).Value = 0

ActivateCell.Offset(0, 1).Value = 1


err_exit:
Exit Sub
err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume err_exit

End Sub


Sub FindNext()

Do While ActiveCell.Offset(0, -1).Value > 0 And ActiveCell.Value = 0
ActiveCell.Offset(1).Select
Loop

End Sub

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

While the interface may look similar, Access and Excel are very different
products. Techniques such as what you're using in Excel that rely on
"previous row" don't translate into Access.

Perhaps you can explain what it is you're trying to do in words, and someone
can help you achieve that functionality in Access.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ddunks" wrote in message
news:EEA32F6A-63D7-4ABF-9EC5-221CB6DE8110@microsoft.com...
> This is our rubberband macro(actually two macros here; one is called
FindNext
> and is called from the first macro) . We use it in Excel to against the
> "tool" data. It "starts" at IAP_Benchmark of 0, and does the code,
generating
> the factors you see here.
> I have played around trying to get this code to work against the same data
> Iin an Access database. Any suggestions?
>
> Log_Dist IAP_Benchmark Factor
> -15.91
> -2.62
> -1.40
> 0.00 0 1.075
> 1.40
> 4.39
> 6.51 7 0.987
> 8.01
> 19.68 20 1.012
> 28.92
> 36.99
> 38.46 39 1
>
>
>
>
> On Error GoTo err_Handler
>
> Dim currentBenchmark As Long
> Dim newBenchmark As Long
> Dim currentFootage As Double
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12187
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12187
> Dim newFootage As Double
> Dim deltaBenchmark As Double
> Dim deltaFootage As Double
> Dim newFactor As Double
> Dim factorCell
>
> currentBenchmark = ActiveCell.Value
> currentFootage = ActiveCell.Offset(0, -1).Value
>
> Do
> 'Get new factor cell
> factorCell = ActiveCell.Offset(0, 1).Address
> ActiveCell.Offset(1).Activate
> FindNext
> newBenchmark = ActiveCell.Value
> newFootage = ActiveCell.Offset(0, -1).Value
>
> deltaBenchmark = newBenchmark - currentBenchmark
> deltaFootage = newFootage - currentFootage
> If deltaFootage > 0 Then
> newFactor = deltaBenchmark / deltaFootage
> Else
> Range(factorCell).Value = 1
> Exit Sub
> End If
>
> ' Place factor in factor cell
> Range(factorCell).Value = newFactor
>
> currentBenchmark = newBenchmark
> currentFootage = newFootage
>
> Loop Until ActiveCell.Offset(1, -1).Value = 0
>
> ActivateCell.Offset(0, 1).Value = 1
>
>
> err_exit:
> Exit Sub
> err_Handler:
> MsgBox Err.Number & " - " & Err.Description
> Resume err_exit
>
> End Sub
>
>
> Sub FindNext()
>
> Do While ActiveCell.Offset(0, -1).Value > 0 And ActiveCell.Value = 0
> ActiveCell.Offset(1).Select
> Loop
>
> End Sub
>