CODEWITHSUNDEEP
excelvbanamed-ranges
Arturo
Arturo

Reputation: 43

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

Upvotes: 0

Views: 704

Answers (2)

Arturo
Arturo

Reputation: 43

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

Upvotes: 0

Rory
Rory

Reputation: 34055

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.

Upvotes: 3

Related Questions