CODEWITHSUNDEEP
vbaexcel
DGMS89
DGMS89

Reputation: 1677

Finding the row position of a value and using it for calculation in VBA

I have a code that defines a range and finds the highest and lowest value of that range.

What I want to do is get those values, find the rows for each of them, see if the row of the high value is smaller then the row of the low value, then do a calculation.

Problem 1 For some reason I keep getting an invalid qualifier error in the line the uses the row for the comparison.

Problem 2 If the low value comes before the high, how can I find the highest value in the range that comes before the low?

The relevant part of the code is:

 Set MDDRange = Worksheets("TIME").Range(Col_Letter(lColumn) & 2, Col_Letter(lColumn) & LastRow) '.Value

        MDDHigh = Application.WorksheetFunction.Min(MDDRange)
        MDDHighPos = Worksheets("TIME SERIES").Range(MDDRange).Find(MDDHigh, LookIn:=xlWhole)

        MDDLow = Application.WorksheetFunction.Max(MDDRange)
        MDDLowPos = Worksheets("TIME").Range(MDDRange).Find(MDDLow, LookIn:=xlWhole)


'if to see if mddhigh comes before mddlow

        If MDDHighPos.Row + 1 < MDDLowPos.Row + 1 Then ' error is here

            MDD = (MDDLow - MDDHigh) / MDDHigh

            Worksheets("STATS").Cells(6, lColumn).Value = MDD

        Else
            'if my high value is not before my low, how to find the highest value before the low?
        End If

Any help will be deeply appreciated.

Modified Part correcting both problems (this is from the accepted answer from SJR and modifications from R3uK):

Set MDDRange = Worksheets("TIME").Range(Col_Letter(lColumn) & 2, Col_Letter(lColumn) & LastRow) '.Value

        MDDHigh = Application.WorksheetFunction.Max(MDDRange)
        Set MDDHighPos = MDDRange.Find(MDDHigh)

        MDDLow = Application.WorksheetFunction.Min(MDDRange)
        Set MDDLowPos = MDDRange.Find(MDDLow)

        a = MDDHighPos.Row
        b = MDDLowPos.Row

'if to see if mddhigh comes before mddlow

        If a < b Then

            MDD = ((MDDLow - MDDHigh) / MDDHigh)

            Worksheets("STATS").Cells(6, lColumn).Value = MDD

        Else
'this creates a new range from begin of old range to the lowest value, and finds the highest value in this range, then calculates the drawdown

            Set MDDRangeNew = Worksheets("TIME").Range(Col_Letter(lColumn) & 2, Col_Letter(lColumn) & b)

            MDDHighNew = Application.WorksheetFunction.Max(MDDRangeNew)

            MDD = ((MDDLow - MDDHighNew) / MDDHighNew)

            Worksheets("STATS").Cells(6, lColumn).Value = MDD

        End If

Upvotes: 2

Views: 100

Answers (1)

SJR
SJR

Reputation: 23081

Here you go. Am assuming your variable declarations are at the beginning...

Set MDDRange = Worksheets("TIME").Range(Col_Letter(lColumn) & 2, Col_Letter(lColumn) & LastRow) '.Value

        MDDHigh = Application.WorksheetFunction.Min(MDDRange)
        Set MDDHighPos = Worksheets("TIME SERIES").Range(MDDRange).Find(MDDHigh, LookIn:=xlWhole)

        MDDLow = Application.WorksheetFunction.Max(MDDRange)
        Set MDDLowPos = Worksheets("TIME").Range(MDDRange).Find(MDDLow, LookIn:=xlWhole)


'if to see if mddhigh comes before mddlow

        If MDDHighPos.Row + 1 < MDDLowPos.Row + 1 Then ' error is here

            MDD = (MDDLow - MDDHigh) / MDDHigh

            Worksheets("STATS").Cells(6, lColumn).Value = MDD

        Else
            'if my high value is not before my low, how to find the highest value before the low?

             Set MDDRange = Worksheets("TIME").Range(Col_Letter(lColumn) & "2", Col_Letter(lColumn) & MDDLowPos.Row - 1)
             'And here you go again! ;)

        End If

Upvotes: 2

Related Questions