finding the Index value

[ravikr2678]

Hi All,

I have some issue while trying to get a value into a cell in sheet2.

Below is the code : Not sure how correct it is..

Sheets("Sheet2").Cells(1, 1).Value = INDEX(D2:IT1180,MATCH("northeast",D2:D1180,0),MATCH(arTemp(j),E1:IT1,0))

arTemp is the Array which holds the value in the excel sheet. column wise
and 'northeast' is the row wise value ..and i need to get the value which corresponds ( intersecting cell ) with that row and column.

Can some help me pls...
It's Very Urgent..

[figment]

Sub testing()

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

this will work, although if northeast is always going to be in the same row, then you can just hard code the row. rather then making it variable. and if you can hardcode northeast you can change exchange index for hlookup. and farther simplify the code.

[mfegyver]

not sure if i understand, would it be something to fing the index?

m = 1180
n = 200
For i = 2 To m
For j = 5 To n
If Cells(i, j) = Cells(6, 1) Then
findj = j
Cells(7, 1) = j
Else: End If
Next
Next

brgds
marcelo

[ravikr2678]

I tried runnig the code but got the below err.

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

[figment]

march proporty or match proporty?

[ravikr2678]

it's Match property,

Sorry about the typo.

[figment]

if that code didn't work then we are probibly running into a compatiblilty issue. what version of office or excel are you runing?

[ravikr2678]

I'm running on Office excel 2003,
May be we can develope an aletrnate code..in that case..
Let me explain you in detail..

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.

[figment]

sorry about the wait.
I think i see what your trying to do, but it would be easyest for me to help if you could post the workbook that this will be used in. that way i could talor the code to what you need. otherwise i would have to make a bunch of obsumtions. as for the error try this

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

there was a simly in your orignal post that i didn't notice and so i screwed up the 4th line

[ravikr2678]

Acually i had to change the logic slightly to get the desired result.
Here's the code what i got:

Sub GenerateChart()
Dim AWS As Worksheet
Dim DWS As Worksheet
Dim Company As String
Dim Value As String
Dim LevemirAr() As Variant
Dim LevemirDataAr() As Integer
Dim offset As Integer
Dim weeks As Integer
'Dim num As Integer
Dim LevemirCnt As Integer ' Counter for Array
Dim LevemirDataArCnt As Integer ' Counter for Array
Set AWS = ThisWorkbook.Sheets("DCS_IDCS")
Set DWS = ThisWorkbook.Sheets("sheet2")

Product = "LS"
LevemirCnt = 0
Row = 2
For col = 5 To 234
Value = AWS.Cells(Row, col)
If InStr(Value, Product) <> 0 Then
ReDim Preserve LevemirAr(LevemirCnt)
LevemirAr(LevemirCnt) = Val(Mid(Value, Len(Product) + 1, Len(Value) - Len(Product)))
LevemirCnt = LevemirCnt + 1
End If
Next
BubbleSort LevemirAr 'sort the array
For m = 0 To UBound(LevemirAr)
Next
weeks = 13
offset = 0
LevemirDataArCnt = 0
For i = LevemirCnt - 1 + offset To LevemirCnt - weeks Step -1
For col = 5 To 234
Value = AWS.Cells(Row, col)
If Value = Product & LevemirAr(i) <> 0 Then
ReDim Preserve LevemirDataAr(LevemirDataArCnt)
LevemirDataAr(LevemirDataArCnt) = col
LevemirDataArCnt = LevemirDataArCnt + 1
End If
Next
Next
Dim swRows As Variant
swRows = Array(1, 4, 7, 31, 32, 33, 34, 35, 36, 37, 38)

'x & y-axis data
r = 5
For m = 0 To UBound(swRows)
cc = 18
For c = UBound(LevemirDataAr) To 0 Step -1
DWS.Cells(r, 17) = AWS.Cells(swRows(m), 4)
DWS.Cells(r, cc) = AWS.Cells(swRows(m), LevemirDataAr(c))
cc = cc + 1
Next
r = r + 1
Next
End Sub
Function BubbleSort(TempArray As Variant)
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer
' Loop until no more "exchanges" are made.
Do
NoExchanges = True
' Loop through each element in the array.
For i = 1 To UBound(TempArray) - 1
' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) > TempArray(i + 1) Then
NoExchanges = False
Temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = Temp
End If
Next i
Loop While Not (NoExchanges)
End Function


However, Instead of fetching all 13 weels data,the end user needs a list box from which he can select "4 weeks' or "6 weeks"
so can we add up a list box to the above code in such a way that ..
what ever value the user selects ..using which the output is generated ?

thank you for your support. :)

[figment]

i skimmed through your code and made a few changes and coments.

Sub GenerateChart()
Dim AWS As Worksheet, DWS As Worksheet
Dim Company As String, Value As String, Product As String
Dim LevemirAr() As Variant
Dim LevemirDataAr() As Integer, offset As Integer, weeks As Integer ', num As Integer
Dim LevemirCnt As Integer, LevemirDataArCnt As Integer ' CounterS for ArrayS
Dim Row As Integer, col As Integer
Set AWS = ThisWorkbook.Sheets("DCS_IDCS")
Set DWS = ThisWorkbook.Sheets("sheet2")

Product = "LS"
LevemirCnt = 0
Row = 2

For col = 5 To 234
Value = AWS.Cells(Row, col)
If InStr(Value, Product) <> 0 Then
ReDim Preserve LevemirAr(LevemirCnt)
LevemirAr(LevemirCnt) = Val(Mid(Value, Len(Product) + 1, Len(Value) - Len(Product)))
LevemirCnt = LevemirCnt + 1
End If
Next

BubbleSort LevemirAr 'sort the array
For m = 0 To UBound(LevemirAr) '<- what is the point if you dont do anything within the loop?
Next

weeks = 13 'set equal to a range with restricted values
LevemirDataArCnt = 0

For i = LevemirCnt - 1 To LevemirCnt - weeks Step -1
For col = 5 To 234
If AWS.Cells(Row, col) = Product & LevemirAr(i) <> 0 Then
ReDim Preserve LevemirDataAr(LevemirDataArCnt)
LevemirDataAr(LevemirDataArCnt) = col
LevemirDataArCnt = LevemirDataArCnt + 1
End If
Next
Next

Dim swRows As Variant
swRows = Array(1, 4, 7, 31, 32, 33, 34, 35, 36, 37, 38)

'x & y-axis data
r = 5
For m = 0 To UBound(swRows)
cc = 18
For c = UBound(LevemirDataAr) To LBound(LevemirDataAr) Step -1
DWS.Cells(r, 17) = AWS.Cells(swRows(m), 4)
DWS.Cells(r, cc) = AWS.Cells(swRows(m), LevemirDataAr(c))
cc = cc + 1
Next
r = r + 1
Next

End Sub

Function BubbleSort(ByRef TempArray As Variant)
Dim Temp As Variant
Dim i As Integer
Dim Exchanges As Boolean
' Loop until no more "exchanges" are made.
Do
Exchanges = False
' Loop through each element in the array.
For i = LBound(TempArray) To UBound(TempArray) - 1
' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) > TempArray(i + 1) Then
Exchanges = True
Temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = Temp
End If
Next
Loop While Exchanges
End Function

as for makeing the code switch between 4 weeks and 6 weeks: could you not just set your weeks value equal to a cell value, and have the user input the desired number of week into that cell? this seems to me to be better method then adding a form with a combo box. if you wish to restrict the options that can be set in that cell you can use excel's data validation under the data menu.