Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Code in Excel - adjust for Access

Discussion in 'Information Technology' started by ddunks, Jul 28, 2006.

  1. ddunks

    ddunks
    Expand Collapse
    Guest

    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
    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
     
  2. Loading...

    Similar Threads Forum Date
    Introducing The Spn Code Of Forum Etiquette Announcements Feb 20, 2016
    UK Sikh 'code of silence' on sexual grooming Breaking News Sep 2, 2013
    Can a Dress Code Bar Ethnic and Religious Attire Based on Customer Preferences in the US? Questions and Answers May 3, 2011
    Pacific Code Exempts Religious Drivers (Fiji) Breaking News Feb 16, 2011
    Controversial Gurdwaras 'Breaking the Sikh Code' on Meat and Alcohol Hard Talk Dec 2, 2010

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <ddunks@discussions.microsoft.com> 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
    > 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
    >
     

Share This Page