Search code examples
excelvbanamed-ranges

Replace R1C1 type formula to a named range formula in Excel-VBA?

I have a working VBA code (on Sheet2) that uses a Public Function to do a calculation using data on Sheet1, column B.

After I use the FillDown function to get a similar calculation for the rest of the rows.

Range("B2").FormulaR1C1 = "=PERSONAL.XLSB!Scoring(Sheet1!RC2)"
Range("B2:B2", "A" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Public Function checks the cell and assigns a score according to the value found. 

Public Function Scoring(str As String)

Dim check As Integer

check_str = 0

If str = "US" Then check_str = 1
If check_str = 1 Then Scoring = "1"
If check_str = 0 Then Scoring = "0"

End Function

My goal is to use Named Ranges instead of R1C1 formula style to avoid any complications if the Sheet1 column order is changed. I guess there are possibilities to loop through the named ranges, but are there any simpler ways?

The code that would be the simple approach to the issue (just replacing the R1C1 type with a reference to the column in a named range), does not work:

Range("B2").Formula = "=PERSONAL.XLSB!Scoring(Sheet1!Range("myRange").Columns(1))"
Range("B2:B2", "A" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Solution

  • This code works with the FillDown:

    Range("B2").Formula = "=PERSONAL.XLSB!Scoring(INDEX(Sheet!myRange,ROWS($A$1:$A1),1))"
Range("B2:B2", "A" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown