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)
精彩评论