Referncing a range using R1C1

[ElieAintabi]

anyone tell me how ot reference a range using r1c1 and the sheetname,

my sheet name is 0

I have
"=ABS(Worksheets(0).Cells("R2509C3:R2717C41"))"

it works fine as "=ABS("R2509C3:R2717C41")" but obviously references the current sheet, and not sheet "0"

 

[ElieAintabi]

bumpppppppp

 

[Norie]

Sheet 0?

Does such a thing exist?

Anyways, I'm not surprised the formula doesn't work, Worksheets and Cells won't be recognised on a worksheet.

Also the code probably doesn't compile in the first place.

Perhaps you should post the rest of the code and an explanation of it's purpose.

 

[ElieAintabi]

Sorry, firstly, the code does compile. It geenerates a dynamic FormulaArray based off ranges from other worksheets. It works as intended, but the problem is once FormulaArray is assigned, it is looking at the sheet where the formula resides to gather the data (simple enough because I didn not fully qualify the ranges where the initial data lies).

Im asking how ot fully qualify my formula

Public Sub BalanceDown2(nodeIndex As Integer, ByRef i() As Integer)
        Range("DENOMINATOR").Clear
    ' Get array of indexes of nodeIndex's child nodes.
        Dim childNodeIndexes As Variant
        childNodeIndexes = GetChildNodeIndexes(nodeIndex, i())
    ' Build array formula for denominator.
        Dim denominatorFormula As String
        denominatorFormula = "=ABS(" & Chr(34) & NodeRange(childNodeIndexes(1)).Address(ReferenceStyle:=xlR1C1) & Chr(34) & ")"
        Dim x As Integer
        Range("DENOMINATOR").FormulaArray = denominatorFormula
        Dim firstTerm As String
        firstTerm = denominatorFormula
        For x = 2 To UBound(childNodeIndexes)
            denominatorFormula = "+ABS(" & Chr(34) & NodeRange(childNodeIndexes(x)).Address(ReferenceStyle:=xlR1C1) & Chr(34) & ")"
        With Range("Denominator")
            .Replace firstTerm, firstTerm & denominatorFormula
        End With
        Next
    End Sub

FormulaArray is

=ABS("R2509C3:R2717C41")+ABS("R2718C3:R2926C41")+ABS("R1882C3:R2090C41")+ABS("R419C3:R627C41")+ABS("R1673C3:R1881C41")+ABS("R2091C3:R2299C41")+ABS("R1255C3:R1463C41")+ABS("R210C3:R418C41")+ABS("R2300C3:R2508C41")+ABS("R628C3:R836C41")+ABS("R1046C3:R1254C41")+ABS("R837C3:R1045C41")+ABS("R1464C3:R1672C41")

Assume that is in Sheet titled "FormulaSheet"

The data SHOULD be gathered from Sheet "0" (0 is the name of the sheet)

so while that formula is proper, it is looking up the data in "FormulaSheet" and not sheet "0", thus my problem is how do I make it look in the right place?

 

[Norie]

The original code you posted does not compile.

As for the latest code - I think we need an explanation in words.

You've got a lot going on there - named ranges, calls to other subs/functions eg GetChildNodeIndexes.

 

[Jonmo1]

Try

"=ABS(Worksheets("0").Range("R2509C3:R2717C41"))"

 

[ElieAintabi]

Try

"=ABS(Worksheets("0").Range("R2509C3:R2717C41"))"

I tried that, now I am unable ot assign it to the FormulaArray property of my range.

        denominatorFormula = "=ABS(Worksheets(" & Chr(34) & "0" & Chr(34) & ").range(" & Chr(34) & NodeRange(childNodeIndexes(1)).Address(ReferenceStyle:=xlR1C1) & Chr(34) & "))"
        Dim x As Integer
        Range("DENOMINATOR").FormulaArray = denominatorFormula

denominatorFormula = "=ABS(Worksheets("0").range("R2509C3:R2717C41"))"

crashes where
denominatorFormula "=ABS("R2509C3:R2717C41")"
works

 

[ElieAintabi]

The original code you posted does not compile.

As for the latest code - I think we need an explanation in words.

You've got a lot going on there - named ranges, calls to other subs/functions eg GetChildNodeIndexes.

Again, everything works fine when denominatorFormula is "=ABS("R2509C3:R2717C41")", it just points to the activesheet not the sheet named "0"/

I tried "=ABS(Worksheets("0").range("R2509C3:R2717C41"))" but I cannot assign it to FormulaArray property of my range as I could the previous format.

 

[Jonmo1]

OK, a formula in a cell doesn't know what Worksheets and Range is..
When you're trying to build a formula for a cell in VBA, you have to build it as a text string that represents EXACTLY how you would want it to appear in the cell.

so for this example

I tried "=ABS(Worksheets("0").range("R2509C3:R2717C41"))"

it should be

"=ABS(0!R2509C3:R2717C41)"

the 0 is the sheet's name.

 

[ElieAintabi]

thanks, perfect