XCitableBill
New Member
- Joined
- Apr 13, 2020
- Messages
- 11
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
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.
There's more stuff in the sub but it's not relevant to my question.
Why is the result of the function not being passed back to the sub?
Thank you for your time.
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.
VBA Code:
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
VBA Code:
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.
Last edited by a moderator: