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.

 

[XCitableBill]

Hello once again!! I have run into another issue. The worksheet consists of 5 years of data, each year represented by 5 columns. In each of the 5 columns are the same data points so that the lowest value I want to identify, ignoring zeros, are in non-adjacent cells in the worksheet. The values I need interrogated are in columns A, F, K, P, U. In each set of yearly data, there are other data points that also happen to be numbers so I can't just interrogate the entire row. It has to be those specific columns. The problem arises when a blank cell is in column A. I can't figure out how to determine the lowest non-zero number in the array, and pass it back to the sub. I'm sure there is a much cleaner way to code what I'm trying to code, so any and all comments are welcome.

My current function looks like this:

Public Function FindMinValue1(Ws As Worksheet, r As Long) As Long

Dim i As Long
Dim lastrow As Long
Dim lastcol As Long
Dim dblMin As Double
Dim minval As Integer
Dim cell As Range
Dim rng As Range
Dim aryData(5) As Long

i = r

If Not cell Is Nothing Then
    Debug.Print "Nothing"
Else
aryData(0) = Cells(i, 1)
aryData(1) = Cells(i, 6)
aryData(2) = Cells(i, 11)
aryData(3) = Cells(i, 16)
aryData(4) = Cells(i, 21)
End If

'I need to find the minimum non-zero number here!

'Worksheet function MIN returns the smallest value in a range
dblMin = Application.WorksheetFunction.Min(aryData(0), aryData(1), aryData(2), aryData(3), aryData(4))

FindMinValue1 = dblMin

End Function

Hard-coding the column number in Cells is very poor coding, I just cant figure out a way around it.

Thank you.

 

[mikerickson]

I'm not sure what roll the argument r plays in this,
But, if given a worksheet, you want to find the minimum value in columns A, F, K, P, U you could use code like

Function MinimumValue(Ws as Worksheet, r as Variant) As Double
    MinimumValue = Application.WorksheetFunction.Min(ws.Range("A:A, F:F, K:K, P:P, U:U")) 
End Function

 

[XCitableBill]

I'm not sure what roll the argument r plays in this,
But, if given a worksheet, you want to find the minimum value in columns A, F, K, P, U you could use code like

Function MinimumValue(Ws as Worksheet, r as Variant) As Double
    MinimumValue = Application.WorksheetFunction.Min(ws.Range("A:A, F:F, K:K, P:P, U:U"))
End Function

Mike thanks for replying. The 'r' argument is the row number. The WorksheetFunction.Min returns a 0 if cell (1,A) is blank. I want to calculate yearly changes in sales and quantities of the PGCs. Below is a representative sample of the data I have in the worksheet.

A	B	C	D	E	F	G	H	I	J
PGC	Type	Sales_$	Sales_Qty	Group	PGC	Type	Sales_$	Sales_Qty	Group
3343	Cap	6,834.52	5120	4	3567	Cap	4,567.01	6025	4
3567	Cap	4,559.57	6020	4	9697	Shirt	2,110.12	155	19
10101	Shirt	1753.54	516	19	11155	Shirt	4,023.49	357	19
					11299	Pants	27,972.33	2687	12

Any data set (A:E, F:J) whose PGC is greater than the minimum PGC value of that row is moved to the next row. Like the following:

PGC	Type	Sales_$	Sales_Qty	Group	PGC	Type	Sales_$	Sales_Qty	Group
3343	Cap	6,834.52	5120	4
3567	Cap	4,559.57	6020	4	3567	Cap	4,567.01	6025	4
					9697	Shirt	2,110.12	155	19
10101	Shirt	1,753.54	516	19
					11155	Shirt	4,023.49	357	19
					11299	Pants	27,972.33	2687	12

The problem comes in when there is a blank in the PGC of row A. Above, since there are no more rows in column A past the 10101 PGC, the next row value in column A is blank and is returned as a 0 to the calling subroutine. Therefore subroutine will keep moving the rows in data set F:J (PGCs 11155 and 11299) to the following row ad infinitum since the PGC in row A will never be anything more than 0. Because I'm passing an array back to the calling sub, the blank value will always be 0. I can ignore a 0 value in the calling function, so this function has to return the lowest non-zero number in each row as the minval, which would be 11155 and 11299 in the last two rows.

 

[mikerickson]

IF you are looking for the minimum of the enteries in that row.

Function MinimumValue(Ws as Worksheet, r as Variant) As Double
    MinimumValue = Application.WorksheetFunction.Min(ws.Rows(r).Range("A1, F1, K1, P1, U1"))
End Function