need to find the cell which intersets the fixed row and a variable column

[ravikr2687]

Hullo all..

Below is the code which i tried running but i got an error. as shown below..


With Excel.WorksheetFunction
Sheets("Sheet2").Cells(1, 1).Value = .Index("D2:IT1180", _
.Match("northeast", D21180, 0), .Match(arTemp(j), "E1:IT1", 0))
End With


I tried runnig the code but got the below err.

Run-time Error '1004'
Unable to get the march property of the WorksheetFunctionclass

Here arTemp(j) is an array refering some value..
I need to find the cell which intersets the above row and column.
Pls. Help...

[Bob Phillips]

Not tested, but try

[vba]
With Excel.WorksheetFunction
Sheets("Sheet2").Cells(1, 1).Value = _
.Index(Range("D2:IT1180"), _
.Match("northeast", Range("D21180"), 0), _
.Match(arTemp(j), Range("E1:IT1"), 0))
End With
[/vba]

[ravikr2678]

Hi I tried the code..
but got the below erorr msg.

Run-time Error '1004'
Unable to get the match property of the WorksheetFunctionclass

was unable to get the result.

[Bob Phillips]

Well, I have just tested it and it works fine for me.

A tjought that has occurred to me, what sheet is the data that is being matched on?

[mikerickson]

Try

With Application

[ravikr2678]

Hi All,
Need some help.
I have an excel sheet which is updated every week with new column values.
The sheet contains 3 names which keep repeating for every week's data.
some like this.
..............abc..........abc1..........abc2..........xyz..........xyz1... .......xyz2
............6/20/07......6/27/07......7/4/07.......6/20/07.....6/27/07......7/4/07
east..........0.2............0.4...........0.2............0.3............0. 5...........0.3
west..........0.1............0.3...........0.4............0.2............0. 5...........0.9

north..........0.2............0.4...........0.2............0.3............0 .5...........0.3

south..........0.2............0.4...........0.2............0.3............0 .5...........0.3

Here abc and xyz are two products and the data is added every week.
You can see the dates are repeated for products.
that means for product 'abc' and 'xyz' on date 7/4/07 two column are added at respective places, however those
columns are not side by side.
So now I need the data with respect to product and zone, for a the last two weeks. 'i.e' for 'east' zone, for
'abc' product on week 7/4/07, the data is 0.2
and for 'xyz' on east zone it's 0.3,
I need this data 0.2 and 0.3 in sheet2. so that i can make a chart using it.
It's a Urgent requirment. and i have no much idea on VBA coding.
Any Help is greatly appreciated.
Thanks in advance.

[mikerickson]

For a quick fix (meeting in a half hour) situation, you might copy/Paste Special Transpose the data to another sheet and use Autofiler to get the records you want.

[Bob Phillips]

Create a workbook with the data and post that. Add the code that I gave and let's see.

[ravikr2678]

well, no luck yet..
OK, I'm uploading the sheet as attachment..
pls. find it.

[ravikr2678]

appraciate your help

[Bob Phillips]

I don't see how you can decide that abc on 7/4/07 is 02, there are 4 values below, and must it be VBA?