Passing variable to function but not seeing result in sub

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. 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.

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
There's more stuff in the sub but it's not relevant to my question.
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:

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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:

VBA Code:
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
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

VBA Code:
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

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

VBA Code:
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.

ABCDEFGHIJ
PGCTypeSales_$Sales_QtyGroupPGCTypeSales_$Sales_QtyGroup
3343Cap6,834.52512043567Cap4,567.0160254
3567Cap4,559.57602049697Shirt2,110.1215519
10101Shirt1753.545161911155Shirt4,023.4935719
11299Pants27,972.33268712

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:

PGCTypeSales_$Sales_QtyGroupPGCTypeSales_$Sales_QtyGroup
3343Cap6,834.5251204
3567Cap4,559.57602043567Cap4,567.0160254
9697Shirt2,110.1215519
10101Shirt1,753.5451619
11155Shirt4,023.4935719
11299Pants27,972.33268712
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
IF you are looking for the minimum of the enteries in that row.

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,871
Messages
5,627,386
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top