RuntimeError 9, Subscript out of Range using Names

I_Batman

Board Regular
Joined
Oct 14, 2011
Messages
62
Hi, another problem I am having with the Names concept.
This is a very basic concept I believe that I am having big issues with.

What I am trying to do is very simple.
I am creating 2 Names, PastTopOrder and TopOrder.
Both will store a number, which is defined as currency on 2 worksheets in the same workbook. It does not matter that I am overwriting the values when I run the subroutine a second time. I am zeroing out the values for the two names every time I run the subroutines.

The subroutine then goes through every row in the worksheet, and looks at the value in column D (which is formatted as currency).

It stores the highest quoted price in Name TopOrder. If a higher quoted price is found, the value in TopOrder is stored in Name PastTopOrder and the new higher value stored in Name TopOrder.

I am getting a runtime error 9, subscript out range on the line:
Code:
Names(PastTopOrder).Value = Names(TopOrder).Value
Can I not simply assign a new value to a Name, or do I have to do some thing with the RefersTo method?

The entire subroutine is below:

Code:
Sub FindTopOrder()
'
' FindTopOrder Macro
'
' Keyboard Shortcut: Ctrl+r
'

Names.Add Name:="PastTopOrder", RefersTo:=0
Names.Add Name:="TopOrder", RefersTo:=0

Dim PastTopOrderVar As Single
Dim TopOrderVar As Single

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
TopOrderVar = 0

'Start Looping at Row 2, since Row 1 is headers'
For RowCounter = 2 To FinalRow
    If Cells(RowCounter, 4).Value > TopOrderVar Then
        TopOrderVar = Cells(RowCounter, 4).Value
        Names(PastTopOrder).Value = Names(TopOrder).Value
        Names("TopOrder").Value = TopOrderVar
    End If
Next RowCounter

End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
You're missing quotes around PastTopOrder and TopOrder.
Code:
 Names("PastTopOrder").Value = Names("TopOrder").Value
 

Watch MrExcel Video

Forum statistics

Threads
1,129,801
Messages
5,638,437
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top