Error setting dynamic range name using row.count/column.count

Question

This is a problem linked to the following original question: Cannot vlookup range after name change in VBA

My code is now successfully naming my ranges except in one case:

'Set range for Variance calculation
        StartCell = "$B$5"
        FinalColumn = wb.Sheets("Pivot Data 3 - To Use").Cells(5, Columns.Count).End(xlToLeft).Column - 1
        FinalRow = wb.Sheets("Pivot Data 3 - To Use").Cells(Rows.Count, "B").End(xlDown).Column

        With wb.Names("Variance")
                .RefersTo = "='Pivot Data 3 - To Use'!" & StartCell & ":" & FinalRow & FinalColumn
                .Visible = True
        End With

All VBA is telling me is that there is a run time error. When I step through, the code resolves itself to an answer of:

.RefersTo="='Pivot Data 3 - To Use'!$B$5:$Z$47"

but won't apply it to the named range. The code is identical (I copied and pasted) in form to all of the completed assignations so I'm stumped as to why the code falls over here e.g.

    'Set range name for Key analysis
        With wb.Names("KeyData")
            .RefersTo = "='Pivot Data 3 - To Use'!$A$5" & ":$CM$" & LearnerNumbers + 5
            .Visible = True
        End With

This code works fine.

Any help in wrangling this is greatly appreciated!

Thanks, Steph

Run-time error 1004 - the formula you typed contains an error. — UncouthScientist, Dec 07 '17 at 11:47

score 0 · Answer 1 · answered Dec 07 '17 at 15:57

When trying to set RefersTo, it looks like the ending range is not actually a range but an integer value. Also it seems like FinalRow always refers to the bottom cell of column B. Was that the intention?

Not knowing how your sheet is setup I can't say for certain how you should approach changing this, but based on your code spinet I would modify by doing something like:

Dim sht As Worksheet
Dim rngEnd As Range

StartCell = "$B$5"
Set sht = wb.Sheets("Pivot Data 3 - To Use")

Set rngEnd = sht.Range(sht.Cells(5, Columns.Count).End(xlToLeft).Offset(0, -1), sht.Cells(Rows.Count, "B").End(xlDown))
With wb.Names("Variance")
    .RefersTo = "='" & sht.Name & "'!" & StartCell & ":" & rngEnd.Address
    .Visible = True
End With

Apologies for taking so long to reply. I got pulled onto a different project and haven't had time to play with this model in a while! The code runs :) But, unfortunately, it selects all of the rows in the sheet. — UncouthScientist, Jan 11 '18 at 14:35

Error setting dynamic range name using row.count/column.count

1 Answers1