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]

The code looks consistent with your xlookup.
Give the msgbox a try and if you don't have any luck show me the message box and if possible an XL2BB of your row 6

I am login off for the night, so give me as much detail as you can so I can take a look tomorrow.