Assign selected range to two-dimentional array and compare 2 arrays VBA-CodePudding

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