Option Explicit
Public Function udf_IdentifyLowestCost(getSupplier_1 As Variant, _
getSupplier_2 As Variant, _
Optional getSupplier_3 As Variant, _
Optional getSupplier_4 As Variant, _
Optional getSupplier_5 As Variant) As String
Dim rg As Range
Dim sht As Worksheet
Dim arrColNam() As String
Dim i As Long, iUBound As Long
Dim arrHolding() As Variant
Dim arrSorted() As Variant, x As Variant
Dim iColumnNo As Long, iRowNo As Long, iRowCount As Long
Dim j As Long, iCellValue As Double, iCellValue2 As Double
Dim iLowestCellValue() As Variant
Dim cellAddress As String
Dim c As Double
Dim iarrColumName As Integer
Dim outputMsg As String
Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range
Dim strColumnList As String, colListValue As String
Dim Supplier1ColumnNo As Long, Supplier2ColumnNo As Long, Supplier3ColumnNo As Long, Supplier4ColumnNo As Long, Supplier5ColumnNo As Long
Dim Supplier1ColumnName As String, Supplier2ColumnName As String, Supplier3ColumnName As String, Supplier4ColumnName As String, Supplier5ColumnName As String
Dim SupLowestTotal_1 As Double, SupLowestTotal_2 As Double, SupLowestTotal_3 As Double, SupLowestTotal_4 As Double, SupLowestTotal_5 As Double
Set r1 = Range(getSupplier_1)
Set r2 = Range(getSupplier_2)
With r1
iColumnNo = .Column
iRowNo = .Row
iRowCount = .Rows.Count
End With
Supplier1ColumnNo = r1.Column
Supplier2ColumnNo = r2.Column
Supplier1ColumnName = GetCol(Supplier1ColumnNo)
Supplier2ColumnName = GetCol(Supplier2ColumnNo)
strColumnList = r1.Column
strColumnList = strColumnList & "," & r2.Column
If IsMissing(getSupplier_3) = False Then
Set r3 = Range(getSupplier_3)
strColumnList = strColumnList & "," & r3.Column
Supplier3ColumnNo = r3.Column
Supplier3ColumnName = GetCol(Supplier3ColumnNo)
End If
If IsMissing(getSupplier_4) = False Then
Set r4 = Range(getSupplier_4)
strColumnList = strColumnList & "," & r4.Column
Supplier4ColumnNo = r4.Column
Supplier4ColumnName = GetCol(Supplier4ColumnNo)
End If
If IsMissing(getSupplier_5) = False Then
Set r5 = Range(getSupplier_5)
strColumnList = strColumnList & "," & r5.Column
Supplier5ColumnNo = r5.Column
Supplier5ColumnName = GetCol(Supplier5ColumnNo)
End If
arrColNam() = Split(strColumnList, ",")
ReDim arrHolding(0 To UBound(arrColNam()))
For j = iRowNo To (iRowCount + iRowNo) - 1
For i = LBound(arrColNam) To UBound(arrColNam)
iCellValue = Cells(j, CDbl(arrColNam(i))).Value
arrHolding(i) = iCellValue
colListValue = colListValue & "," & Str(iCellValue)
Next i
iLowestCellValue = BubbleSrt(arrHolding, True)
c = 0
Do Until iLowestCellValue(c) > 0
c = c + 1
iLowestCellValue(0) = iLowestCellValue(c)
Loop
For i = LBound(arrColNam) To UBound(arrColNam)
iCellValue2 = Cells(j, CDbl(arrColNam(i))).Value
If (iLowestCellValue(0) = iCellValue2) Then
cellAddress = Cells(j, CDbl(arrColNam(i))).Address
alterCellColor (cellAddress)
iarrColumName = Cells(j, CDbl(arrColNam(i))).Column
If Supplier1ColumnNo = iarrColumName Then
SupLowestTotal_1 = SupLowestTotal_1 + iCellValue2
ElseIf Supplier2ColumnNo = iarrColumName Then
SupLowestTotal_2 = SupLowestTotal_2 + iCellValue2
ElseIf Supplier3ColumnNo = iarrColumName Then
SupLowestTotal_3 = SupLowestTotal_3 + iCellValue2
ElseIf Supplier4ColumnNo = iarrColumName Then
SupLowestTotal_4 = SupLowestTotal_4 + iCellValue2
ElseIf Supplier5ColumnNo = iarrColumName Then
SupLowestTotal_5 = SupLowestTotal_5 + iCellValue2
End If
End If
Next i
Debug.Print j & ":- "; Mid(colListValue, 2) & ": in this row the lowest value is " & iLowestCellValue(0) & " in cell - " & cellAddress
colListValue = ""
Next j
outputMsg = "Supplier 1 in column '" & Supplier1ColumnName & "' total in yellow is = " & SupLowestTotal_1
outputMsg = outputMsg & vbNewLine & "Supplier 2 in column '" & Supplier2ColumnName & "' total in yellow is = " & SupLowestTotal_2
If Supplier3ColumnNo <> 0 Then
outputMsg = outputMsg & vbNewLine & "Supplier 3 in column '" & Supplier3ColumnName & "' total in yellow is = " & SupLowestTotal_3
End If
If Supplier4ColumnNo <> 0 Then
outputMsg = outputMsg & vbNewLine & "Supplier 4 in column '" & Supplier4ColumnName & "' total in yellow is = " & SupLowestTotal_4
End If
If Supplier5ColumnNo <> 0 Then
outputMsg = outputMsg & vbNewLine & "Supplier 5 in column '" & Supplier5ColumnName & "' total in yellow is = " & SupLowestTotal_5
End If
udf_IdentifyLowestCost = outputMsg
End Function
Private Function alterCellColor(cellName As String)
Range(cellName).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Function
Private Function BubbleSrt(ArrayIn, Ascending As Boolean) As Variant
Dim SrtTemp As Variant
Dim i As Long
Dim j As Long
If Ascending = True Then
For i = LBound(ArrayIn) To UBound(ArrayIn)
For j = i + 1 To UBound(ArrayIn)
If ArrayIn(i) > ArrayIn(j) Then
SrtTemp = ArrayIn(j)
ArrayIn(j) = ArrayIn(i)
ArrayIn(i) = SrtTemp
End If
Next j
Next i
Else
For i = LBound(ArrayIn) To UBound(ArrayIn)
For j = i + 1 To UBound(ArrayIn)
If ArrayIn(i) < ArrayIn(j) Then
SrtTemp = ArrayIn(j)
ArrayIn(j) = ArrayIn(i)
ArrayIn(i) = SrtTemp
End If
Next j
Next i
End If
BubbleSrt = ArrayIn
End Function
Private Function GetCol(ColumnNumber) As String
Dim FuncRange As String
Dim FuncColLength As Integer
FuncRange = Cells(1, ColumnNumber).AddressLocal(False, False)
FuncColLength = Len(FuncRange)
GetCol = Left(FuncRange, FuncColLength - 1)
End Function