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

M

mmm55

New Member
Joined
Jun 9, 2015
Messages
5
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)
 
Last edited:
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
[TABLE="width: 909"]
<tbody>[TR]
[TD]transform input table into output table... hope that helps..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]output table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]input table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row[/TD]
[TD]col[/TD]
[TD]value[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]e[/TD]
[TD][/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]f[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]g[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]h[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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...

mmm55 said:
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)
Click to expand...

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:

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Evaluate cell or have cell refer to itself after being transposed via. lookup functions
Replies
2
Views
122
Sam_D_Ben
Sam_D_Ben
B
  • Question Question
Excel VBA When new rows are added to table 1 copy the information to table 2
Replies
0
Views
870
BlueHawker42
B
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
700
Welshy1491
W
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
339
krunal123
K
M
  • Solved
VBA: Checking if a row exists with exact data in multiple columns
Replies
6
Views
1K
mwl_y
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top