Arrays and offsets 1

[caerdydd]

Hello all,
My procedure has an array in 'list' and takes each 'ID' from this 'list' and then searches through the results (histarray) until correct data is returned and drops into my spreadsheet cell.
Problem is that when the procedure is run it brings my results back in rows that i do not expect i.e they end up in the correct column but in row 4 and then in row 10 and then row 13 etc. Obviously the problem is with the offset which dumps my results and the variable 'r'. Becuase 'r' retrieves around 30 different pieces of data, when the correct value is found the value of 'r' is maybe 5 or 6 and i think this is where the result is then sent to in the spreadsheet. What i am trying to do is get the first result in row 1 and then second result in row 2 etc etc. How do i do this?
Thanks in advance for thoughts and suggestions on this. If I am unclear (which i probably am!!) please let me know.

---------------------------------------------
Dim i As Integer, r As Integer, c As Integer, x As Integer, counter As Integer, n As Integer
Dim NumberOfRows As Integer
Dim ID As Variant
Dim list As Variant
Dim sql As String
Dim histarray()

Spreadsheet2.ActiveSheet.Range("BI1:BJ300").ClearContents

With Spreadsheet2
n = Spreadsheet2.ActiveSheet.Range("W1").Value
list = Spreadsheet2.ActiveSheet.Range("V2:V" & n).Value
ID = Spreadsheet2.ActiveSheet.Range("V2").Value

For Each ID In list
i = i + 1

sql = "SELECT NAME, INDEX FROM INDEX WHERE ID=" & ID & ""

histarray = Empty
histarray = xms(sql)

For r = LBound(histarray, 1) To UBound(histarray, 1)
For c = LBound(histarray, 2) To UBound(histarray, 2)
If UCase(histarray(r, 1)) Like "DOW JONES*" Then
unwantedIndex = False
If InStr(1, histarray(r, 1), "50") > 0 Then unwantedIndex = True
If InStr(1, histarray(r, 1), "Components") > 0 Then unwantedIndex = True

If unwantedIndex = False Then
Spreadsheet2.ActiveSheet.Cells(2, 60).Offset(i, c).Value = histarray(r, c)
End If
End If
Next c
Next r
Next ID

 

[kphu]

From the way it looks this line:

If unwantedIndex = False Then
Spreadsheet2.ActiveSheet.Cells(2, 60).Offset(i, c).Value = histarray(r, c)

dictates the data that is placed in your spreadsheet.

since you mentioned that the data is placed in the correct column then logically all you would need to do is move the cell up.

Based on this logic you may wanna try this.


Replace the line above with this code.

If unwantedIndex = False Then
 Spreadsheet2.ActiveSheet.Cells(2, 60).Offset(i, c).select

selection.end(xlup).offset(1,0).select
activecell.formula = histarray(r, c)

[\code]

 

[caerdydd]

kphu,
Thanks very much for the neat code, i could not see the wood for the trees.
Thanks again