I'm trying to read a set of array values into an excel worksheet. I can
transfer single values OK, but run into trouble when I try to do it in a
For..Next loop, getting a '1004' error.
eg
For x=0 to 23
ws.Cells(xlRow,xlCol) = Scores(y,x)
xlCol=xlCol+1
Next

Having tried to research the problem, I know that the problem lies with the
iterated call to Excel, but can't get the syntax correct.

Can anyone help please?

Thanks
Barry

Hi
how are the other variables defined?

--
Regards
Frank Kabel
Frankfurt, Germany


Barry Aylett-Warner wrote:
I'm trying to read a set of array values into an excel worksheet. I
can transfer single values OK, but run into trouble when I try to do
it in a For..Next loop, getting a '1004' error.
eg
For x=0 to 23
ws.Cells(xlRow,xlCol) = Scores(y,x)
xlCol=xlCol+1
Next

Having tried to research the problem, I know that the problem lies
with the iterated call to Excel, but can't get the syntax correct.

Can anyone help please?

Thanks
Barry

numrw = Ubound(Scores,1)-Lbound(Scores,1)+1
numcol = Ubound(Scores,2)-Lbound(Scores,2)+1
Range("A1").Resize(numrw,numcol).Value = Scores

change A1 to the upper left corner where you want the data to start.
--
Regards,
Tom Ogilvy


"Barry Aylett-Warner" wrote in message
...
I'm trying to read a set of array values into an excel worksheet. I can
transfer single values OK, but run into trouble when I try to do it in a
For..Next loop, getting a '1004' error.
eg
For x=0 to 23
ws.Cells(xlRow,xlCol) = Scores(y,x)
xlCol=xlCol+1
Next

Having tried to research the problem, I know that the problem lies with
the
iterated call to Excel, but can't get the syntax correct.

Can anyone help please?

Thanks
Barry

Tom,
thanks for the suggestion - am now getting '1004 - Method 'Range' of
Object'_Global' failed.
Any thoughts?

Regards
Barry


"Tom Ogilvy" wrote in message
...
numrw = Ubound(Scores,1)-Lbound(Scores,1)+1
numcol = Ubound(Scores,2)-Lbound(Scores,2)+1
Range("A1").Resize(numrw,numcol).Value = Scores

change A1 to the upper left corner where you want the data to start.
--
Regards,
Tom Ogilvy

Sure. In the code you posted, you are working with another excel
application instance - it is unclear why or what application is running your
code, but you would have to incorporate my suggestion in your code so it is
properly qualified. Otherwise you might get '1004 - Method 'Range' of
Object'_Global' failed because your unqualified references are inconsistent
with what you are trying to do.

You didn't post your revision, so no specific suggestion can be offered.


--
Regards,
Tom Ogilvy




"Barry Aylett-Warner" wrote in message
...
Tom,
thanks for the suggestion - am now getting '1004 - Method 'Range' of
Object'_Global' failed.
Any thoughts?

Regards
Barry


"Tom Ogilvy" wrote in message
...
numrw = Ubound(Scores,1)-Lbound(Scores,1)+1
numcol = Ubound(Scores,2)-Lbound(Scores,2)+1
Range("A1").Resize(numrw,numcol).Value = Scores

change A1 to the upper left corner where you want the data to start.
--
Regards,
Tom Ogilvy

Tom,

I created a test procedure as below and it returned the error mentioned.

Private Sub Command2_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\My
Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
xlRow = 6
numrw = UBound(Scores, 1) - LBound(Scores, 1) + 1
numcol = UBound(Scores, 2) - LBound(Scores, 2) + 1
Range(xlRow, xlCol ).Resize(numrw, numcol).Value = Scores
wb.SaveAs "C:\Documents and Settings\My Documents\Finals.xls"
wb.Close
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing
End Sub

Am trying to save a numeric array, Scores(9,23) to a block of cells
starting at X6.

Regards
Barry

"Tom Ogilvy" wrote in message
...
Sure. In the code you posted, you are working with another excel
application instance - it is unclear why or what application is running
your
code, but you would have to incorporate my suggestion in your code so it
is
properly qualified. Otherwise you might get '1004 - Method 'Range' of
Object'_Global' failed because your unqualified references are
inconsistent
with what you are trying to do.

You didn't post your revision, so no specific suggestion can be offered.


--
Regards,
Tom Ogilvy

Unless I missed something, this should fix it:
Qualified the Range and changed range to Cells
Shifted some stuff at the bottom
Avoided prompt to overwrite Finals.xls


Private Sub Command2_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open( _
"C:\Documents and Settings\My Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
xlRow = 6
numrw = UBound(Scores, 1) - LBound(Scores, 1) + 1
numcol = UBound(Scores, 2) - LBound(Scores, 2) + 1
ws.Cells(xlRow, xlCol ).Resize(numrw, numcol).Value = Scores
wb.close SaveChanges:=True
Set ws = Nothing
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

--
Regards,
Tom Ogilvy

"Barry Aylett-Warner" wrote in message
...
Tom,

I created a test procedure as below and it returned the error mentioned.

Private Sub Command2_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\My
Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
xlRow = 6
numrw = UBound(Scores, 1) - LBound(Scores, 1) + 1
numcol = UBound(Scores, 2) - LBound(Scores, 2) + 1
Range(xlRow, xlCol ).Resize(numrw, numcol).Value = Scores
wb.SaveAs "C:\Documents and Settings\My Documents\Finals.xls"
wb.Close
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing
End Sub

Am trying to save a numeric array, Scores(9,23) to a block of cells
starting at X6.

Regards
Barry

"Tom Ogilvy" wrote in message
...
Sure. In the code you posted, you are working with another excel
application instance - it is unclear why or what application is running
your
code, but you would have to incorporate my suggestion in your code so it
is
properly qualified. Otherwise you might get '1004 - Method 'Range' of
Object'_Global' failed because your unqualified references are
inconsistent
with what you are trying to do.

You didn't post your revision, so no specific suggestion can be offered.


--
Regards,
Tom Ogilvy