I don't understand.

If you want to copy the table, then just copy the table all at once.

If you have another worksheet that has (say) a list of row values and a list of
column values and you wanted to return the intersection, then that kind of makes
sense to me.

dim M as Variant 'could be an error
dim B as Variant 'could be an error
dim myRng as range
dim myCell as range
dim LastRow as long
dim LastCol as long
Dim myTable as range
dim myRes as variant

with worksheets("othersheet")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

with worksheets("data")
'I used column A and Row 1
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
end with

for each mycell in myrng.cells
m = application.match(mycell.value,mytable.columns(1), 0)
B = application.match(mycell.offset(0,1).value,mytable .rows(1),0)

if iserror(M) _
or iserror(b) then
myres = "At least one thing didn't match"
else
myres = mytable(b,m).value
end if

mycell.offset(0,2).value = myres
next mycell

Untested, uncompiled. Watch for typos!

This looks down one column (column A in OtherSheet) and looks for a row match.
And looks at column B in Othersheet for a columns match.

Then it puts the result in column C in Othersheet.

YH wrote:

Question:

If I want to loop through all rows and columns in myTable, find the matched
intersects, and copy the matched intersect cell values to another worksheet.

What will be a good way to do it?

Thanks,

YH

"YH" wrote:

Dave,

Thanks for the help! Your code works like a charm.

YH

dim M as Variant 'could be an error
dim B as Variant 'could be an error
dim LastRow as long
dim LastCol as long

Dim myTable as range
with worksheets("data")
'I used column A and Row 1
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set mytable = .range("a1", .cells(lastrow,lastcol))

m = application.match("Q106A",mytable.columns(1),0)
B = application.match(.range("a3").value,mytable.rows( 1),0)

if iserror(M) _
or iserror(b) then
msgbox "At least one thing didn't match
else
msgbox mytable(b,m).value
end if

"Dave Peterson" wrote:

You shouldn't get 0 as the result of using =match(). It'll return a number
between 1 and the size of the range/array if there is a match--this version
(application.worksheetfunction.match()) will cause the code to blow up.

If you've surrounded your code like:

on error resume next
M = Application.WorksheetFunction.Match("Q106A", COL_Key, 0)
B = Application.WorksheetFunction.Match(A3, ROW_KEY, 0)
on error goto 0

Then M and B won't change if there is an error. If the values of M and B were 0
before (uninitialized Longs???), then they'll still be 0s if there is no match.

ps.

A3 looks kind of weird he
B = Application.WorksheetFunction.Match(A3, ROW_KEY, 0)

If A3 is a variable, ok. But if A3 is a cell address, you may want to change to
something like:

B = Application.WorksheetFunction.Match _
(worksheets("somesheet").range("A3").value, ROW_KEY, 0)

Personally, I like to use application.match() and check to see if an error was
returned--not see if the code raised an error.

dim M as Variant 'could be an error
dim B as Variant 'could be an error
dim LastRow as long
dim LastCol as long

Dim myTable as range
with worksheets("data")
'I used column A and Row 1
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set mytable = .range("a1", .cells(lastrow,lastcol))

m = application.match("Q106A",mytable.columns(1),0)
B = application.match(.range("a3").value,mytable.rows( 1),0)

if iserror(M) _
or iserror(b) then
msgbox "At least one thing didn't match
else
msgbox mytable(b,m).value
end if



I did use column A and row 1 to as headers. Not quite sure how your data is
laid out, though.




YH wrote:

I have a table like below and want to find the value of the intersect cell
for a given month and product. I want to use the Index and Match functions to
find the value of the cell, but I am not familar with the usage and am not
able to get Match function to return me the correct row or column location.

1) What did I do wrong?
2) Is there an easier way to find the value of the cell with a matched month
name in a row and product name in a column? Thanks!

Jan Feb Mar Apr...
product a
b
c
d
.
.
.

Worksheets("Data").Names.Add Name:="TABLE", RefersTo:=Range("$A$1", lastRG)
Worksheets("Data").Names.Add Name:="ROW_KEY", RefersTo:=Range("$A$1", "$A$"
& numRow)
Worksheets("Data").Names.Add Name:="COL_KEY", RefersTo:=Range("$A$2",
Cells(2, numCol))

M = Application.WorksheetFunction.Match("Q106A", COL_Key, 0)
B = Application.WorksheetFunction.Match(A3, ROW_KEY, 0)

'I got M=0 and B=0.

--

Dave Peterson


--

Dave Peterson