CODEWITHSUNDEEP
excelvbauser-defined-functions
user816845
user816845

Reputation:

VBA UDF multi-cell reference

I am trying to create a UDF that takes the values from 4 selected cells and performs a function with it. However I don't know how to write the function to recognize the cell values. The way I have it currently written I need to input a value. Can anyone help me with this relatively simple problem? This is what I have so far.

Function solubility(anion As String, cation As String, carbon1 As String, carbon2 As String)

EDIT

So for example I am trying to select cells A, C, E, and G and they contain values of Tf, Mi, Ch, and Ch. I will then use these cells refer to corresponding values in a range to calculate and display a final value. THe whole code is provided below.

Function solubility(anion As String, cation As String, carbon1 As String, carbon2 As String)

Dim sum As Double
Dim ncavalue As Long, nanvalue As Long, ncb1value As Long, ncb2value As Long
Dim nca As Long, nan As Long, ncab As Long
Dim coeff As Range, groups As Range

'solubility groups range
groups = Worksheets("Solubility").Range("A2:A33")

'group coefficients range
coeff = Worksheets("Solubility").Range("B2:B33")

'number of groups for each group
ncavalue = Range("AE" & cation.Row)
nanvalue = Range("AC" & anion.Row)
ncb1value = Range("AG" & carbon1.Row)
ncb2value = Range("AI" & carbon2.Row)

j = 0

    For j = 0 To UBound(groups)
        If UCase(anion.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            anvalue = coeff(j).Value
        End If
        If UCase(cation.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            cavalue = coeff(j).Value
        End If
        If cation.Value = "[MIm]" Then
            cavalue = Range("B2")
            ncb1value = ncb1value.Value + 1
        End If
        If UCase(carbon1.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            cb1value = coeff(j).Value
        End If
        If UCase(carbon2.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            cb2value = coeff(j).Value
        End If
    Next j
    sum = anvalue * nanvalue + cavalue * ncavalue + cb1value * ncb1value + cb2value * ncb2value
    solubility = sum + Range("B34").Value
End Function

Upvotes: 1

Views: 1332

Answers (1)

Lance Roberts
Lance Roberts

Reputation: 22842

Assuming Row 1, call the Function like this:

=solubility(A1, C1, E1, G1)

Upvotes: 1

Related Questions