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

Question

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

I'd be surprised if Range("B2:B2", "A" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown works. Can you post your function code? — SJR
What is the purpose of Sheet1!Range("myRange").Columns(1))? Are you trying to only refer to the first column in the named range? If so, what does the function do, and can it handle this input? — Plutian
@SJR , the Public Function checks the cell and assigns a score according to the value found. (see updated - above) — Arturo
(1) Your function declares check but then uses check_str and check_ip (but doesn't seem to do anything meaningful) (2) Please don't call a function "Function" (3) It returns a string and uses a string as a parameter so you cannot pass a range of cells. — SJR
@Plutian Yes, I am trying to refer to the first column in the named range, hoping that the FillDown will pick the appropriate row (and thus the exact cell) similar to how it works with R1C1 formula style. But is it possible at all?... — Arturo

Rory Rory · Accepted Answer · 2019-10-24T08:51:13

If the named range has workbook scope, your formula would be:

"=PERSONAL.XLSB!Function(INDEX(myRange,0,1))"

to refer to the first column of that range. If the name has worksheet scope, then use Sheet1!myRange in the formula.

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

2 Answers