I have a piece of code that is trying to overwrite the value in a named range, but I am getting <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 the “ActiveWorkbook.Range” line. Table1RowCount & Table1LastRowFixed are both named ranges with workbook level scope hosting constant values. I am trying to update one of them based on a condition. As you can see the Evaulate function works on both name ranges, but when I try to use the Range to update on of them I am getting the error.
Please note that I have tried “Thisworkbook.Range” and “Range” by itself without much success.
Any help is appreciated.
user27447697 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
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
1