Convert only a fraction of formula to absolute ref

Question

First off, I do apologize if it is a silly question, but I just couldn't find an answer anywhere online. I have a question to ask anyone who is familiar in changing relative formula to absolute formula using vba.

My problem right now is that, my absolute formula is determined by calculating row difference of different cell conditions of other columns (Column C) as I have input multiple formulas across a column (Column B) using vba. Hence the absolute formula that I want to achieve is unpredictable and it is constantly changing.

   Set Rng = Range(Cells(2, 3), Cells(lastrow, 3))
        Range("B2").Select
        startb = lastrow
        For b = lastrow To 2 Step -1
        If Cells(b, 3) <> Cells(b - 1, 3) Then
        endb = b
        rowdiff = startb - endb
        Cells(end, 2).Select
        ActiveCell.FormulaR1C1 = "=Countif(RC[-1]:R[" & rowdiff & "]C[-1],RC[-1])"
        ActiveCell.Formula = Application.ConvertFormula(Formula:=ActiveCell.Formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)
        Selection.Copy
        Range(Cells(startb, 2), Cells(endb, 2)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False

        End If
        Next b

As you guys can see that I have a code line that can convert relative formula to absolute formula straight away. Unfortunately in my COUNTIF formula, I want an absolute range (the section before the comma) but a relative criteria (in this case the RC[-1] after the comma).

Is there anything I could do to just convert a partial formula to become absolute? Or there isn't such thing? Any help and advice is greatly appreciated. Please let me know if I am doing it in a completely wrong way.

Thank you for reading and your time.

Answer 1

as to your very point of partially converting a formula from relative to absolute except for the last cell reference, you could use this function

Function LeaveLastRelative(strng As String) As String
    LeaveLastRelative = Left(strng, InStrRev(strng, ",")) & Replace(Right(strng, Len(strng) - InStrRev(strng, ",")), "$", "")
End Function

whose more verbose and (possibly) clear version is:

Function LeaveLastRelative(strng As String) As String
    Dim strng1 As String, strng2 As String

    strng2 = Right(strng, Len(strng) - InStrRev(strng, ","))
    strng1 = Left(strng, InStrRev(strng, ","))
    LeaveLastRelative = strng1 & Replace(strng2, "$", "")
End Function

to be used as:

            ActiveCell.FormulaR1C1 = "=Countif(RC[-1]:R[" & rowdiff & "]C[-1],RC[-1])"
            ActiveCell.formula = Application.ConvertFormula(formula:=ActiveCell.formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)
            ActiveCell.formula = LeaveLastRelative(ActiveCell.formula) '<--| here you use the function