CODEWITHSUNDEEP
arraysvbaexcelmin
A.Doe
A.Doe

Reputation: 25

Calculating cells and storing in array to find the smallest value; "min"-function gives problems

I'm trying to calculate the cheapest alternative for a product out from a table, based on a user input. The code is supposed to a)take an input number, the product size, (from K19 in User Interface sheet) and match it to the correct row in the table in order to find the different alternatives for this size. b) multiply the specific cost with the size so as to get the total cost for each alternative (hence the variants Mx, Nx, etc which are the specific costs for each alternative). c) store the total costs in an array, and find the smallest of these numbers which is given back to the user in C45 in the user interface.

The described code sample:

Sub Calculate()

Dim var As Variant
Dim rownumber As Integer
Dim Mx As Variant
Dim Nx As Variant
Dim Ox As Variant
Dim Px As Variant
Dim Qx As Variant
Dim Rx As Variant
Dim low As Variant
Dim cat As Variant

var = Range(WorksheetFunction.Match(Sheets("User Interface").Range("K19"), Sheets("C-type").Range("L1:L10000"), 0)).Value

rownumber = var.Row

Mx = var * Range("M" & rownumber).Value
Nx = var * Range("N" & rownumber).Value
Ox = var * Range("O" & rownumber).Value
Px = var * Range("P" & rownumber).Value
Qx = var * Range("Q" & rownumber).Value
Rx = var * Range("R" & rownumber).Value

cat = Array("Mx", "Nx", "Ox", "Px", "Qx", "Rx")

low = Min(cat).Value

Sheets("User Interface").Range("C45").Value = low

Sheets("User Interface").Range("c45").Activate
Application.Goto ActiveCell.EntireRow, True

End Sub

The problem is that VBA gives an error ("sub or function not defined") at low = Min(cat).Value even if from my findings I think I'm using the correct syntax. I couldn't find any viable solutions up until now. Could anyone help me troubleshoot?

Many thanks in advance!

Upvotes: 0

Views: 299

Answers (4)

Dy.Lee
Dy.Lee

Reputation: 7567

the code has some mistakes.

Sub Calculate()

Dim var As Variant
Dim rownumber As Integer
Dim Mx As Variant
Dim Nx As Variant
Dim Ox As Variant
Dim Px As Variant
Dim Qx As Variant
Dim Rx As Variant
Dim low As Variant
Dim cat As Variant

var = Range(WorksheetFunction.Match(Sheets("User Interface").Range("K19"), Sheets("C-type").Range("L1:L10000"), 0)).Value
'<~~ range error
rownumber = var.Row

Mx = var * Range("M" & rownumber).Value
Nx = var * Range("N" & rownumber).Value
Ox = var * Range("O" & rownumber).Value
Px = var * Range("P" & rownumber).Value
Qx = var * Range("Q" & rownumber).Value
Rx = var * Range("R" & rownumber).Value

'cat = Array("Mx", "Nx", "Ox", "Px", "Qx", "Rx")
cat = Array(Mx, Nx, Ox, Px, Qx, Rx) '<~~ quote is not need
low = WorksheetFunction.Min(cat) '<~~ use WorksheetFunction

Sheets("User Interface").Range("C45").Value = low

Sheets("User Interface").Range("c45").Activate
Application.Goto ActiveCell.EntireRow, True

End Sub

Upvotes: 1

maaajo
maaajo

Reputation: 1025

low = Application.WorksheetFunction.Min(cat)

This piece of code should help.

Upvotes: 1

Pspl
Pspl

Reputation: 1474

  1. When using Excel function Min you have to write WorksheetFunction.Min or Application.Min;
  2. Don't use the .value on the function Min;
  3. Your low value will always be 0 'cause your array doesn't contain any values... Write instead cat = Array(Mx, Nx, Ox, Px, Qx, Rx)

Upvotes: 2

braX
braX

Reputation: 11755

Are you trying to use WorksheetFunction.Min ?

MSDN page for Excel built-in MIN function

Upvotes: 2

Related Questions