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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
You're missing quotes around PastTopOrder and TopOrder.
Code:
 Names("PastTopOrder").Value = Names("TopOrder").Value
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top