CODEWITHSUNDEEP
arraysvbaexcelvariables
Cosmin
Cosmin

Reputation: 585

Grab Cell dynamically based on row and column string

I have a table with months across columns. The months are always shifting so I am trying to adapt my code to accommodate this. It works great if the column remain static,I just need to figure out how to find which column is the total column dynamically.

Here's what I have so far:

Sub CalculateTotalGP()

For RowToTest = Cells(Rows.count, 27).End(xlUp).Row To 2 Step -1
        With Cells(RowToTest, 27)
            If .Value = "GP%" Then
            Range("AL" & RowToTest & ":AL" & RowToTest).Formula = "=SUMIFS(AL:AL,AA:AA,""GrossProfit Total"")/SUMIFS(AL:AL,AA:AA,""Income Total"")"
        End If
            End If
        End With
Next RowToTest

End Sub

What I need to grab in order to replace "AL" everywhere with a variable:

InStr(1, Cells(6, i), "Total")

Basically no matter where the total columns is in the specified range, input that formula. The only thing that remains static is my column range, 16 columns from AD8 to AS9999(or N) and my criteria from column AA.

Can you help me figure this out?

Thank you

Upvotes: 0

Views: 54

Answers (1)

paul bica
paul bica

Reputation: 10715

To get the column number for header "Total" use the GetHeaderCol() function (instead of Match)

Option Explicit

Public Sub TestHeader()
    Dim tHeader As Long

    tHeader = GetHeaderCol(Sheet1, "Total", 6)  'If "Total" in row 6 & col H, result -> 8

    If tHeader > 0 Then Debug.Print "Total column: " & tHeader
End Sub

Public Function GetHeaderCol(ByRef ws As Worksheet, ByVal hdr As String, _
                             Optional ByVal hdrRow As Long = 1) As Long

    Dim hdrs As Variant, c As Variant

    If ws Is Nothing Then Set ws = ActiveSheet
    hdrRow = IIf(hdrRow = 0, 1, Abs(hdrRow))
    hdrs = ws.Range(ws.Cells(hdrRow, 1), ws.Cells(hdrRow, Columns.Count).End(xlToLeft))

    If Not IsEmpty(hdrs) Then
        For c = 1 To UBound(hdrs, 2)
            If InStr(1, hdrs(1, c), hdr, vbTextCompare) > 0 Then
                GetHeaderCol = c
                Exit Function
            End If
        Next
    End If
    GetHeaderCol = 0
End Function

Upvotes: 1

Related Questions