create a vba function with two tables where table1(three columns), table2 has (1 row and 1 column)

[mmm55]

create a vba function with two tables where table1(three columns(with 20 rows)), table2 has (1 row and 1 column), match entries in table1 column1(with 20 rows) with table2 row1(20columns) and match table1 column 2 with table 2 column 1 and enter value in corresponding row and column found from table1 in table 2
function1 find matching column (match table1 column1 entries with table2 row1 entries)
function 2 find matching row (match table 1 column2 entries with table2 column1 entries)
function 3 enter value in matching cell from column3 of table 1 .cells(row, column).value=price.cells(i+1, j+2).value
complete function is consisting of all three functions into one vba function.
function should enter all matching entries from table1 column3 into table2 range(B2:H16)

 

[Aaron Blood]

To be clear...
1) You want the function to analyze 2 tables and return an array result?
2) You are OK with this VBA function being an array formula?

...if not an array formula, the function would require an incrementing value parameter so it would know which element of the array to return.

 

[mmm55]

Thanks for replying aaron

I could create a sub, but i am trying to create a function which return multiple values and is independent of worksheets and row and column number.
code for sub is given below...

Sub xx()

(enter values from row 4 to 20, column 8 to 20, then run the macro)



Dim i As Integer, j As Integer, a As Integer, b As Integer, m As Integer, n As Integer
m = 3'row(user defined)
n = 7'column(user defined)
For a = 1 To 18 Step 1

For i = n To 50 Step 1

If (ThisWorkbook.Worksheets("Sheet6").Cells(m, i + 1).Value = ThisWorkbook.Worksheets("Sheet4").Cells(a + 1, 1).Value) Then

For j = m To 50 Step 1
If (ThisWorkbook.Worksheets("Sheet6").Cells(j + 1, n).Value = ThisWorkbook.Worksheets("Sheet4").Cells(a + 1, 2).Value) Then

ThisWorkbook.Worksheets("sheet6").Cells(j + 1, i + 1).Value = ThisWorkbook.Worksheets("sheet4").Cells(a + 1, 3).Value

Else

End If
Next j

End If
Next i
Next a

End Sub

 

[Aaron Blood]

Understood that you are trying to create a function and not a sub.

What I'm asking is, if we create a function to return an array, it would have to be entered on the worksheet as an array-style formula across a range using Ctrl-Shift-Enter.

If you want to create a function that can be entered in cells without it being an array-style formula, then we would need to have the formula include a parameter that tells it which element of the array to return.

Trying to figure out what style of function you want?

 

[mmm55]

I want to try both the ways if you don't mind. Thanks

 

[Aaron Blood]

I'll try to put something together by this afternoon with an optional input for array position... I'm thinking it could be used either way then.

 

[Aaron Blood]

Actually having a very hard time trying to piece together your explanations above...

Could you re-explain this assuming Table 1 is a 3x5 (3 row, 5 column) range on Sheet1 and Table 2 is a... well, I'm not really sure what shape Table 2 is exactly? ...is it a 5x3 range on a different sheet?

 

[mmm55]

transform input table into output table... hope that helps..
		output table						input table
		1	2	3	4				row	col	value
	a	33							a	1	33
	b	44							b	1	44
	c		12						c	2	12
	d		45						d	2	45
	e		56						e	2	56
	f			25					f	3	25
	g				11				g	4	11
	h				15				h	4	15

<tbody>
</tbody>

 

[mmm55]

Also add new column (in output table) if col value(in input table) is not found

then add the value in cell(row,col) in the output table.

 

[Aaron Blood]

OK, so you followed up with a PM which provided an example of what you are doing with a SUMPRODUCT.
Normally, I would not post a PM without permission, but I see no harm in reposting it here just for clarity...

i have this (SumProduct) formula which is working perfectly well in the excel but unable to implement it in VBA.
Could you help:
=SUMPRODUCT(($M$31:$M$38=E31)*($N$31:$N$38=$F$30),$O$31:$O$38)

So you are trying to recreate the SUMPRODUCT formula that you have in VBA. I will refrain from criticism and suggesting alternatives and just assume there is a good reason for why you would want to do this. So, with that... the approach I would take in your VBA formula is to just declare your function along with its required input ranges:

Function Rec2Table(ByRef Row_Rng As Range, ByRef Row_Hdr As Range, ByRef Col_Rng As Range, ByRef Col_Hdr As Range, ByRef Val_Rng As Range) As Variant

Then based on what you've specified as your range references above, state the format for the actual cell formula:
Dim Fn As String
Fn = "SUMPRODUCT((Row_Rng=Row_Hdr)*(Col_Rng=Col_Hdr),Val_Rng)"

Once you have these in place, all you need to do is use the "REPLACE" command in VBA to step through your Fn string and swap out the variable names with the appropriate range references and then use EVALUATE on the resulting Fn string to set the result of the formula. Your final custom VBA function might look like this:

Function Rec2Table(ByRef Row_Rng As Range, ByRef Row_Hdr As Range, ByRef Col_Rng As Range, ByRef Col_Hdr As Range, ByRef Val_Rng As Range) As Variant
       
    Dim Fn As String
    Fn = "SUMPRODUCT((Row_Rng=Row_Hdr)*(Col_Rng=Col_Hdr),Val_Rng)"

    Fn = Replace(Fn, "Row_Rng", Row_Rng.Address)
    Fn = Replace(Fn, "Row_Hdr", Row_Hdr.Address)
    Fn = Replace(Fn, "Col_Rng", Col_Rng.Address)
    Fn = Replace(Fn, "Col_Hdr", Col_Hdr.Address)
    Fn = Replace(Fn, "Val_Rng", Val_Rng.Address)
    
    Rec2Table = Evaluate(Fn)

End Function

where "Row_Rng" is your formula ref: $M$31:$M$38
and "Row_Hdr" is your formula ref: E31