How to create a dynamic report to adjust various paper widths on a drum (1 Viewer)

[Vector]

Situation: Paper width to adjust on to a drum that have grooves.
Goal: end goal is to place both edges onto "peaks" NOT 'Valleys or groove". Both edges must be atleast 0.1" away from "valley or call it grooves"

Example:
User Entry: 1.75"

If the width of the paper (user input) is less than 7.5" inch then add fields from table:
tblOp: id, OpPeak, OpValley~1, .547,.153

Otherwise add fields from table:
tblDr: id, DrPeak, DrValley ~1, 735,.156

Loop thru recordset and add fields until they are more than equal to 1.75"
If the last value is from field "OpValley" then check the value and move the paper onto "OpPeak" and at least 0.1" off "OpValley"
If the last value is "OpPeak" then check the value and make sure "edge of the paper" is 0.1" off "OpValley"
Do until the same thing for the other edge of the paper and make sure both edges are on "OpPeak" and 0.1" off "OpValley"

***End goal is to make sure both edges of the paper are 0.1" off "OpValley"***

Example of how drum looks like with peaks and valleys with Drive side (Dr) vs Operator side (Op)

 

[Uncle Gizmo]

I asked ChatGPT:-

Sub AdjustPaperWidth()

    Dim paperWidth As Double
    paperWidth = 1.75 ' user entry

    Dim OpPeak As Double, OpValley As Double, DrPeak As Double, DrValley As Double

    If paperWidth < 7.5 Then
        OpPeak = 0.547
        OpValley = 0.153
    Else
        DrPeak = 0.735
        DrValley = 0.156
    End If
    
    Dim totalWidth As Double
    totalWidth = 0

    Dim lastValue As String
    lastValue = ""

    Do
        If lastValue = "OpValley" Or lastValue = "" Then
            totalWidth = totalWidth + OpPeak
            lastValue = "OpPeak"
        ElseIf lastValue = "OpPeak" Then
            totalWidth = totalWidth + OpValley
            lastValue = "OpValley"
        End If
    Loop Until totalWidth >= paperWidth

    ' Make adjustments if last value is from OpValley
    If lastValue = "OpValley" And totalWidth - OpValley + OpPeak <= paperWidth Then
        totalWidth = totalWidth - OpValley + OpPeak
        lastValue = "OpPeak"
    End If

    ' Make sure edge of the paper is 0.1" off OpValley
    If lastValue = "OpPeak" And totalWidth + 0.1 <= paperWidth Then
        totalWidth = totalWidth + 0.1
    End If

    ' Do the same for the other edge
    totalWidth = totalWidth + 0.1
    
    Debug.Print totalWidth
End Sub

This script should fulfill the constraints you've mentioned. You'll have to replace the paperWidth variable with actual user input. Also, make sure to handle the cases where you have to use DrPeak and DrValley instead of OpPeak and OpValley. This script assumes that you're only working with OpPeak and OpValley.

 

[Vector]

I asked ChatGPT:-

Sub AdjustPaperWidth()

    Dim paperWidth As Double
    paperWidth = 1.75 ' user entry

    Dim OpPeak As Double, OpValley As Double, DrPeak As Double, DrValley As Double

    If paperWidth < 7.5 Then
        OpPeak = 0.547
        OpValley = 0.153
    Else
        DrPeak = 0.735
        DrValley = 0.156
    End If
   
    Dim totalWidth As Double
    totalWidth = 0

    Dim lastValue As String
    lastValue = ""

    Do
        If lastValue = "OpValley" Or lastValue = "" Then
            totalWidth = totalWidth + OpPeak
            lastValue = "OpPeak"
        ElseIf lastValue = "OpPeak" Then
            totalWidth = totalWidth + OpValley
            lastValue = "OpValley"
        End If
    Loop Until totalWidth >= paperWidth

    ' Make adjustments if last value is from OpValley
    If lastValue = "OpValley" And totalWidth - OpValley + OpPeak <= paperWidth Then
        totalWidth = totalWidth - OpValley + OpPeak
        lastValue = "OpPeak"
    End If

    ' Make sure edge of the paper is 0.1" off OpValley
    If lastValue = "OpPeak" And totalWidth + 0.1 <= paperWidth Then
        totalWidth = totalWidth + 0.1
    End If

    ' Do the same for the other edge
    totalWidth = totalWidth + 0.1
   
    Debug.Print totalWidth
End Sub

This script should fulfill the constraints you've mentioned. You'll have to replace the paperWidth variable with actual user input. Also, make sure to handle the cases where you have to use DrPeak and DrValley instead of OpPeak and OpValley. This script assumes that you're only working with OpPeak and OpValley.

Thank you for the help. I see that the vba does provide a good start for getting to end goal.