Passing variable to function but not seeing result in sub

[XCitableBill]

Hello.
I'm passing a variable from a sub to a function. The variable is passed into the function correctly and the function pulls the correct data based on the passed in variable. However, I need to use the function result in the sub that called the function and fur whatever reason, I can't get the designated variable to populate with the returned data.

Sub MultiColSort()
    Dim Sh As Worksheet
    Dim r As Long
    Dim v As Long
    Dim LR As Long
    Dim minval As Long
    Set Sh = Worksheets("Sheet1")
    r = 1
    v = 0
    With Sh
        Do
            r = r + 1
            v = v + 1
            LR = WorksheetFunction.Max(.Range("A" & .Rows.Count).End(xlUp).Row, .Range("E" & .Rows.Count).End(xlUp).Row, .Range("I" & .Rows.Count).End(xlUp).Row, .Range("M" & .Rows.Count).End(xlUp).Row, .Range("Q" & .Rows.Count).End(xlUp).Row)
            If r > LR Then Exit Sub
           
'         Pass row to FindMinValue function
          [B]minval = FindMinValue1(r, minval) [/B] - This is where I'm having the problem. the return variable is the minimum value in the row being interrogated

End Sub

There's more stuff in the sub but it's not relevant to my question.

Public Function FindMinValue1(r As Long, minval As Long) As Long

Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Dim lastcol As Long
Dim dblMin As Double
'Dim minval As Long
Dim cell As Range
Dim rng As Range

Set ws = Worksheets("Sheet1")
Set rng = Nothing
'Get row count
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Get last column - Assumes first row is header
lastcol = ws.Cells(2, Columns.Count).End(xlToLeft).Column

'For i = 2 To lastrow

i = r

If Not cell Is Nothing Then
    Debug.Print "Nothing"
Else
    Set rng = Range(Cells(i, 1), Cells(i, lastcol))
End If
'Worksheet function MIN returns the smallest value in a range

dblMin = Application.WorksheetFunction.Min(rng)

'Displays smallest value
MsgBox dblMin

minval = dblMin

End Function

Why is the result of the function not being passed back to the sub?

Thank you for your time.

 

[Fluff]

Are you values whole numbers or decimals?

 

[Norie]

Not sure what the function is meant to do, and the use of minval in the function and sub doesn't really make sense but to return a value from a function you need to assign the value to the function.

FindMinValue1 = dblMin

 

[XCitableBill]

Whole numbers

 

[XCitableBill]

The function is meant to interrogate a row of values, find hte minimum value in that row and pass that value back to MultiColSort.

 

[XCitableBill]

Thanks Norie. That's what I needed.

 

[Fluff]

In that case for the function use

Public Function FindMinValue1(Ws As Worksheet, r As Long) As Long
   Dim rng As Range

   Set rng = Nothing
   Set rng = Intersect(Ws.UsedRange, Ws.Rows(r))

   FindMinValue1 = Application.WorksheetFunction.Min(rng)
End Function

and call it like

minval = FindMinValue1(sh, r)

 

[mikerickson]

Change the last line of the function to FindMinVal = dblMin

 

[XCitableBill]

Thank you all for your responses. Fluff - I never would have thought of that. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback