How to find the MAX Value in a range defined with R1C1?

[Melanie_]

Hi,

I'm new to VBA and this forum, so sorry, if this is a stupid question. I' trying to write a function for a university project. In this function there's a situation where I want to define a range with using R1C1 syntax. I don't want to use smth like "A1:B1" since the range is dependent on the cell in which the function is used (dynamic range?).
After defining the range I want to get the maxium value of it.

See the code below. I don't know what's wrong with it, but it doesn't work. I tried defining the range with A1:B1 style as a test and this worked perfectly fine. I've been starring at this for a while now an can't figure out the problem. I appreciate any suggestions, since I couldn't find a solution using Google. Thanks!

Note: I want to use the function in Sheet x, but the range is defined in sheet y ("Windspeed_all").
Code:

Function JustATest()

'defining variables
Dim h_address As String
Dim h_row As Integer
Dim v_row As Integer
Dim v_range As Range
Dim v_max As Double

h_address = Application.Caller.Address
h_row = Range(h_address).Row
v_row = h_row + 1

'everything is fine to this point!
'now I want to define my range, i tried it like this:

Set v_range = Application.ThisWorkbook.Worksheets("Windspeed_all").Range(Cells(v_row, 3), Cells(v_row, 37))
v_max = Application.WorksheetFunction.Max(v_range)
JustATest = v_max

End Function

 

[Alex Blakenburg]

Try this:
- you need to tell VBA that the 2 references to Cells belong to Worksheets("Windspeed_all")

With ThisWorkbook.Worksheets("Windspeed_all")
    Set v_range = .Range(.Cells(v_row, 3), .Cells(v_row, 37))
    v_max = Application.WorksheetFunction.Max(v_range)
End With

 

[Melanie_]

Try this:
- you need to tell VBA that the 2 references to Cells belong to Worksheets("Windspeed_all")

With ThisWorkbook.Worksheets("Windspeed_all")
    Set v_range = .Range(.Cells(v_row, 3), .Cells(v_row, 37))
    v_max = Application.WorksheetFunction.Max(v_range)
End With

Oh, thanks alot for the quick help! This worked, yay

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[Melanie_]

I got stuck again...maybe you can help. In the next step I want to find the address of v_max, take the column number, and pick a value in this very column but another row...
I tried this:

With ThisWorkbook.Worksheets("Windspeed_all")
Set v_range = .Range(.Cells(v_row, 3), .Cells(v_row, 37))
v_max = Application.WorksheetFunction.Max(v_range)
v_max_address = v_range.Find(What:=v_max).Address
End With

 

[Melanie_]

I got stuck again...maybe you can help. In the next step I want to find the address of v_max, take the column number, and pick a value in this very column but another row...
I tried this:

With ThisWorkbook.Worksheets("Windspeed_all")
Set v_range = .Range(.Cells(v_row, 3), .Cells(v_row, 37))
v_max = Application.WorksheetFunction.Max(v_range)
v_max_address = v_range.Find(What:=v_max).Address
End With

Argh, posted befor finishing code. Again. I tried this:

With ThisWorkbook.Worksheets("Windspeed_all")
    Set v_range = .Range(.Cells(v_row, 3), .Cells(v_row, 37))
    v_max = Application.WorksheetFunction.Max(v_range)
    v_max_address = v_range.Find(What:=v_max).Address
    h_column = Range(v_max_address).Column
    height = .Range(4, h_column).Value
End With

JustATest = height

 

[Alex Blakenburg]

Assuming you really do want the value in Row 4. Change the below.

' Replace this
h_column = Range(v_max_address).Column
height = .Range(4, h_column).Value
' To this
h_column = .Range(v_max_address).Column    ' Added the "." before Range
height = .Cells(4, h_column).Value                    ' Changed Range to Cells

 

[Melanie_]

Assuming you really do want the value in Row 4. Change the below.

' Replace this
h_column = Range(v_max_address).Column
height = .Range(4, h_column).Value
' To this
h_column = .Range(v_max_address).Column    ' Added the "." before Range
height = .Cells(4, h_column).Value                    ' Changed Range to Cells

Yes, I want values in row 4. I tried your code, but unfortunately it doesn't work. Do you have another idea? But thanks anyway!
It looks like this now:

With ThisWorkbook.Worksheets("Windspeed_all")
    Set v_range = .Range(.Cells(v_row, 3), .Cells(v_row, 37))
    v_max = Application.WorksheetFunction.Max(v_range)
    v_max_address = v_range.Find(What:=v_max).Address
    h_column = .Range(v_max_address).Column
    height = .Cells(4, h_column).Value
End With

JustATest = height

In the mean time I found another solution by just using xlookup. But I would still be interested in where my mistake is.

 

[Alex Blakenburg]

What does, "it doesn't work" mean ?
Did it error out ? Did it give a different result ?
Show me your Xlookup formula that works.

Another thing you can do is add the code below just before your End With and see if what appears in the message box makes sense.

      MsgBox "v_max: " & v_max & Chr(10) & _
             "v_max_address: " & v_max_address & Chr(10) & _
             "h_column: " & h_column & Chr(10) & _
             "Height address: " & .Cells(4, h_column).Address & Chr(10) & _
             "Height: " & Height

 

[Melanie_]

What does, "it doesn't work" mean ?
Did it error out ? Did it give a different result ?
Show me your Xlookup formula that works.

Another thing you can do is add the code below just before your End With and see if what appears in the message box makes sense.

      MsgBox "v_max: " & v_max & Chr(10) & _
             "v_max_address: " & v_max_address & Chr(10) & _
             "h_column: " & h_column & Chr(10) & _
             "Height address: " & .Cells(4, h_column).Address & Chr(10) & _
             "Height: " & Height

I'm using excel in german and the result after using the function is "#WERT!", I think in english it would be "#VALUE!"

My xlookup formula, that works (german): =XVERWEIS(I5;Windspeed_all!C6:AK6;Windspeed_all!$C$4:$AK$4)
where I5 is the max. windspeed (v_max) which I determined in another column by =MAX(Windspeed_all!C6:AK6)


I will try your suggestions later today, thanks!! This will for sure be helpfull while debugging