PDA

View Full Version : need to find the cell which intersets the fixed row and a variable column



ravikr2687
11-01-2007, 01:08 PM
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
11-01-2007, 02:02 PM
Not tested, but try


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

ravikr2678
11-01-2007, 02:47 PM
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
11-01-2007, 03:12 PM
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
11-01-2007, 03:28 PM
Try

With Application

ravikr2678
11-02-2007, 12:19 PM
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
11-02-2007, 03:07 PM
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
11-02-2007, 03:38 PM
Create a workbook with the data and post that. Add the code that I gave and let's see.

ravikr2678
11-02-2007, 04:34 PM
well, no luck yet..
OK, I'm uploading the sheet as attachment..
pls. find it.

ravikr2678
11-02-2007, 04:35 PM
appraciate your help :)

Bob Phillips
11-03-2007, 12:42 AM
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?