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:
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:
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
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