Accessing a named range to insert a value

Question

I am trying to overwrite the value in a named range.

I get

Object doesn't support this property or method

Sub CheckStuff()

    If Evaluate("Table1RowCount") > Evaluate("Table1LastRowFixed") Then
 
        ActiveWorkbook.Range("Table1LastRowFixed").Value = Evaluate("Table1RowCount")
  
    End If

End Sub

The error occurs at ActiveWorkbook.Range.

Table1RowCount & Table1LastRowFixed are named ranges with workbook level scope hosting constant values. I am trying to update one of them based on a condition.

The Evaulate function works on both name ranges, but when I try to use the Range to update one of them I get the error.

I tried Thisworkbook.Range and Range by itself.

ActiveWorkbook.Range: A Range is a property of a Worksheet, not a Workbook. — BigBen
– BigBen, Commented Sep 24, 2024 at 18:03
"named ranges with workbook level scope hosting constant values" is a bit ambiguous. Do the names refer to worksheet cells, or to constants? If constants then: ActiveWorkbook.Names("Table1LastRowFixed").RefersTo=Evaluate("Table1RowCount") — Tim Williams
– Tim Williams, Commented Sep 24, 2024 at 18:21
Thanks Tim. Your suggestion worked and yes the named range was referring to constants and NOT TO cells on the sheet. — user27447697
– user27447697, Commented Sep 24, 2024 at 20:07

pgSystemTester · Accepted Answer · 2024-09-24 18:06:18Z

0

Ranges and names are not quite the same thing, however you can sort of make them that way using RefersToRange.

Try this:

If Evaluate("Table1RowCount") > Evaluate("Table1LastRowFixed") Then
    
    ThisWorkbook.Names("Table1LastRowFixed").RefersToRange.Value = Evaluate("Table1RowCount")

End If

I'd also recommend minimizing all the hard coding... too many quotes and possible typing errors. Consider this...

    Const nnTableRowCount = "Table1RowCount"
    Const nnLastRowFixed = "Table1LastRowFixed"
    
    If Evaluate(nnTableRowCount) > Evaluate(nnLastRowFixed) Then
        
        ThisWorkbook.Names(nnLastRowFixed).RefersToRange.Value = Evaluate(nnTableRowCount)
    
    End If

edited Sep 24, 2024 at 18:06

answered Sep 24, 2024 at 17:58

pgSystemTester

10.1k2 gold badges28 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user27447697 Over a year ago

Hello pgSystemTester, Thanks for your response, but your solution did not work, but the one provided by Tim Williams. I will certainly follow your suggestion about the hard coding part.

Collectives™ on Stack Overflow

Accessing a named range to insert a value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related