Hello friendly community,
this is my first time trying VBA code, nothing is obvious to me, and all help is appreciated!

I’ve been trying to figure it out with ChatGPT, but I’ve run into issues, especially when dealing with cell reference transitions between different numbers of characters (like AA to Z or AA90 to Y90 or AB99 to AB107).

In short, I have a workbook consisting of 7 sheets listing financial information for 7 different companies, and 1 sheet (CCA!) that compares these companies along a range of financial metrics. For example, on my company comparison sheet ASML’s current ratio for 2023 is located in cell I3. The formula in that cell refers to ASML’s input sheet (ASML!), as follows:

=ASML!AB90/ASML!AB98

ASML!AB90 is 2023’s current assets.
ASML!AB98 is 2023’s current liabilities.

The cell for ASML’s current ratio for 2022 is located 9 rows below (I12) and continues in the same manner down to 2011. The other companies are all offset 1 cell below, but follow the same 9 cell pattern. However, on the input sheet, the earlier year’s data (e.g., for 2022) is found in the cells to the immediate left of the 2023 data. For example:

=ASML!AA90/ASML!AA98 for 2022.

This is complicated further, because the inputs for the different companies aren’t necessarily in the same cells, so AB90 and AB98 might be AB99 and AB107 instead.

What I’m trying to achieve is a script that can:

1. Dynamically retrieve the needed variables and calculate metrics.
2. Loop that process x amount of times for x years.

Below is my most “successful” attempt, which managed to calculate 1/27 ratios for 2/7 companies.

Sub FillDynamicCurrentRatio()

    Dim inputSheet As String
    Dim assetCell As String
    Dim liabilityCell As String
    Dim startRow As Integer
    Dim startCol As Integer

    ' Prompt the user for inputs
    inputSheet = InputBox("Enter the input sheet name:", "Input Sheet", "ASML")
    assetCell = InputBox("Enter the starting current asset cell reference (e.g., AB90):", "Asset Cell", "AB90")
    liabilityCell = InputBox("Enter the starting current liability cell reference (e.g., AB98):", "Liability Cell", "AB98")
    startRow = InputBox("Enter the starting row on the target sheet:", "Starting Row", 3)
    startCol = InputBox("Enter the starting column number on the target sheet (e.g., 9 for column I):", "Starting Column", 9)

    ' Call the sub with the input parameters
    Call DynamicCurrentRatio(inputSheet, assetCell, liabilityCell, startRow, startCol)

End Sub

Sub DynamicCurrentRatio(inputSheet As String, assetCell As String, liabilityCell As String, startRow As Integer, startCol As Integer)

    Dim rowTarget As Integer
    Dim assetRow As Long
    Dim liabilityRow As Long
    Dim assetCol As Long
    Dim liabilityCol As Long
    Dim year As Integer
    Dim formulaBase As String
    Dim wsInput As Worksheet
    Dim wsTarget As Worksheet

    ' Ensure the input sheet exists
    On Error Resume Next
    Set wsInput = Sheets(inputSheet)
    If wsInput Is Nothing Then
        MsgBox "The input sheet " & inputSheet & " does not exist.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    ' Ensure the target sheet exists
    Set wsTarget = Sheets("CCA") ' Replace "TargetSheet" with your actual target sheet name
    If wsTarget Is Nothing Then
        MsgBox "The target sheet does not exist.", vbExclamation
        Exit Sub
    End If

    ' Initialize variables
    rowTarget = startRow
    assetRow = Val(Mid(assetCell, InStr(1, assetCell, Left(assetCell, 1)) + 1))
    liabilityRow = Val(Mid(liabilityCell, InStr(1, liabilityCell, Left(liabilityCell, 1)) + 1))
    assetCol = Range(assetCell).column
    liabilityCol = Range(liabilityCell).column
    year = 2023

    ' Loop through the years
    Do While year >= 2011
        ' Create dynamic formula using column numbers instead of letters
        formulaBase = "=" & inputSheet & "!" & Cells(assetRow, assetCol).Address(False, False) & " / " & inputSheet & "!" & Cells(liabilityRow, liabilityCol).Address(False, False)
        wsTarget.Cells(rowTarget, startCol).Formula = formulaBase

        ' Update references
        rowTarget = rowTarget + 9
        assetCol = assetCol - 1 ' Move to the next column to the left
        liabilityCol = liabilityCol - 1
        year = year - 1
    Loop

End Sub

Most of my problems so far seem to stem from managing dynamic cell references in. Specifically, I’m having trouble handling the transition between column references of different lengths, like moving from “AB” to “AA” and then to “Z”, and extracting the correct row and column values from cell references (e.g., “AB90” or “Y90”).