excel vba - VBA- Need to create a function, which takes the range as input -

I have a two-dimensional table in Excel such as OutputProduct Melding Combination ** 5p1: 0.6 / P3: 0.5 ** 2P1: 0.3 / P2: 0.7 4P5: 0.4 / P2: 0.7 7P11 : 0.7 / P7: 0.4

Suppose that the range of table is different from B2: C6 (this can vary). I have to create a function, whose first job is to read this category (which will be a user defined input) and then store the data in a 2 dimensional array so that I can use the data (integer) in the first column and the first column As a result, the product is index, while the second column is a combination of proportion in the ratio, which combines together to give the product in the first column.

/ P>

Then there is another table:

  Product Index Current Stock Update Share ** 1 ** ** 10 ** 2 20 ** 3 ** ** 50 ** 4 15 ** 5 ** ** 100 **. . . . .   
 After the data processing, I will update the amount of stock in this table. For example, in the ratio of 6: 5 (units) to product 1 with product 1, 1 unit is produced. Therefore, I have to update the amount of stock for each product in Table 2.  
 Any suggestions, how to convert the category into a 2 dimensional array?  
 The public function Blending_function (R1 Range, R2 as R2) as column '1 in R1 first is stored in a 2-dimensional array, such that data' R2 The table 2, where the update has the limit to be 'read, and the data in column 2 can be read (Table 1). 'The integer in Table 1 of Table 1 refers to the product index in Table 2.' P (I) stands for ith product In the first line of Table 1, P1 and P3 add PR to give a ratio of 6: 5. The current stock of each product is provided in Table 2, which is the limit of R2 (Whole Table 2). 'R1 is the range of Table 1, from which to be processed end function   
 The main obstacle for me is to convert the range (table -1) into a range 2 array. And then from that array, see the index of the output product, and find that stock in the table-2 to update the stock level.   
 
 
 Here's an example of working with 2D array. The function will break the  blending combination  and remove the values ​​you want so that you can use them.  
  (sub sample) () as the Rng1 Range Range, restart the error like Rng2 set the range next to Rng1 = Application.InputBox ("Please select Table 1 range", type: = 8) Go to error 0 if Rng1.Column.Count & lt; & Gt; Goto 0 error are at Rng2.Columns: (type = 8 "select table 2 range", type 2) Then exit the MsgBox sub end then start the error again on the next set Rng2 = Application.InputBox "Please have a limit , Select 2 columns wide. "Count < & Gt; 3 then the MsgBox exit sub end "Please select a wide range of 3 columns, which is a limit" Blending_function Rng1, Rng2 End Sub Public Function Blending_function (range as R1, range as R2) as the dim MyAr1 version , MyAr2 version dim as long as the blndCom string diminished, the OutputPrd string as Arp1 (string), Arp2 () string, tmpAr (as) as string for MyAr1 = R1 = 2 to UBound (MyAr1, 1) OutputPrd = MyAr1 (i, 1) blndCom = M YAr1 (as i, 2) tmpAr = split (blndCom, "/") ArP1 = split (tmpAr (0), ":") ArP2 = split (tmpAr (1), ":") Debug.Print OutputPrd Debug .Print trim (ArP1 (0)) Debug.Print ArP1 (1) Debug.Print ArP2 (0) Debug.Print ArP2 (1) Debug.Print "-------" Next End Function  < / Pre> 
  SNAPSHOT   
   
 Once you can use  for these values. Search  for  product index  in category  R2  and then use .    to enter your formula