I'm stuck for hours solving my case. Code included below, I'll explain my case first for better understanding and to be easier to follow.
I have created a two dimensional array that has multiple compounds and corresponding heating values for them at two temperatures- it is contained in code and the user does not have a view of it.
The user types in the compounds and percentages of the mixture into the cells, and I want the selected cells that make up the array of multiple rows and two columns to be added to the two-dimensional array and then used in the function created to calculate a certain value (which is shown in the attached screenshot).
Ultimately, I want the program to search the user's entered and selected table to match the union name with the array, which is "hidden "in the code to properly perform the algebraic operation.
Code:
Function LoopThroughArray(T, x)
Dim arr() As Variant
ReDim arr(2, 4)
arr(0, 0) = "CH4"
arr(0, 1) = 35.818
arr(0, 2) = 35.808
arr(1, 0) = "C2H6"
arr(1, 1) = 63.76
arr(1, 2) = 63.74
arr(2, 0) = "C3H8"
arr(2, 1) = 91.18
arr(2, 2) = 91.15
Dim arrUser() As Variant
ReDim arrUser(2, 4)
arrUser(0, 0) = "CH4"
arrUser(0, 1) = 0.7
arrUser(1, 0) = "C2H6"
arrUser(1, 1) = 0.3
'declare variables for the loop
Dim i As Long, j As Long
'loop for the first dimension
For i = LBound(arr, 1) To UBound(arr, 1)
'loop for the second dimension
For j = LBound(arr, 2) To UBound(arr, 2)
If T = 0 And arr(i, j) = "CH4" And arrUser(i, j) = "CH4" Then
LoopThroughArray = arr(i, j 1) * x 'the X is concentration of CH4 selected by user
Else
If T = 25 And arr(i, j) = "CH4" And arrUser(i, j) = "CH4" Then
LoopThroughArray = arr(i, j 2) * x 'the X is concentration of CH4 selected by user
End If
End If
Next j
Next i
End Function
Screenshot from Excel: I am also attaching a screenshot showing the values of the table that is embedded in the code, and how the function would ultimately work.
Problem:
Currently, my code that I have written only works when the function is for a CH4 compound and the user manually select cell containing value of concentration (x in my code).
How should I modify the code so that the function/loop will search the table entered by the user, match the compound names from it with those in the built-in table in the code and calculate a value in the form: concentration (user defined, currently the x value in my code) * LHV for specific compound in desired temperatures (0 or 25 deg).
CodePudding user response:
I would loop through user input rows (rows in A9:A10) and look for matches at the array to obtain the concentrations and do the calculus:
' call the function like in =loopthrougharray(25;"A9:A10")
Function LoopThroughArray(T, userRange) As Double
Dim arr() As Variant
Dim strFind As String
Dim i As Long, j As Long, row As Long
Dim curRow As Range
Dim ret As Double, x As Double
ReDim arr(2, 4)
arr(0, 0) = "CH4"
arr(0, 1) = 35.818
arr(0, 2) = 35.808
arr(1, 0) = "C2H6"
arr(1, 1) = 63.76
arr(1, 2) = 63.74
arr(2, 0) = "C3H8"
arr(2, 1) = 91.18
arr(2, 2) = 91.15
' Loop through user input rows:
For Each curRow In userRange
arraycompound = Trim(UCase(curRow.Value2))
For i = 0 To UBound(arr, 1)
If arr(i, 0) = arraycompound Then
' x retrieves user's input of concentration:
x = curRow.Offset(0, 1)
If T = 0 Then
ret = ret arr(i, j 1) * x
Else
ret = ret arr(i, j 2) * x
End If
End If
Next
Next
LoopThroughArray = ret
End Function