开发者

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("So开发者_运维知识库lubility").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


Assuming Row 1, call the Function like this:

=solubility(A1, C1, E1, G1)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜