URGENT: Alan Beban - SubArray not working - any thoughts?

M

Marston

Alan -

For some reason, my project is going backwards,
or at least it feels that way.

I have the following:

Sub .... ()

Dim rng As Range
Dim aTemp
Range("A1").Select
ActiveCell.CurrentRegion.Select
r = Selection.Rows.Count
c = Selection.Columns.Count
Set rng = Range("A1").CurrentRegion
aTemp = rng.Value
Sheets.Add
Set rng = Range("A1").Range(Cells(1,1),Cells(r,c)).Select
rng.Resize(r,c).Values = SubArray(aTemp,1,c,1,r)

End Sub

I try it now on a single file - hoping that in effect all it would do
is read the range into an array and then write it back out to a
different worksheet.

When I watch the subroutine, it appears to do everything correct but
the line with SubArray.

Any thoughts?
 
A

Alan Beban

Marston said:
Alan -

For some reason, my project is going backwards,
or at least it feels that way.

I have the following:

Sub .... ()

Dim rng As Range
Dim aTemp
Range("A1").Select
ActiveCell.CurrentRegion.Select
r = Selection.Rows.Count
c = Selection.Columns.Count
Set rng = Range("A1").CurrentRegion
aTemp = rng.Value
Sheets.Add
Set rng = Range("A1").Range(Cells(1,1),Cells(r,c)).Select
rng.Resize(r,c).Values = SubArray(aTemp,1,c,1,r)

End Sub

I try it now on a single file - hoping that in effect all it would do
is read the range into an array and then write it back out to a
different worksheet.

When I watch the subroutine, it appears to do everything correct but
the line with SubArray.

Any thoughts?
Click to expand...
Bite your tongue! Your problems don't have anything to do with the
SubArray function. Drop the .Select in the next to last line (I can't
believe that that line, as included above, does not throw an Object
required error, rather than doing everything correct), and in the last
line change "Values" to "Value"; you misinferred that because there was
a problem on this last line it resided in the SubArray function.

Alan Beban
 
M

Marston

Actually the odd thing was - even though I didn't have an On Error
Resume Next in my subroutine,
it was running all the way through and sat there and watched just
about every darn variable.
(the Values was a typo not in the code by the way).
For some reason, it really looked like everything was loading into the
array aTemp
but when I tried to put in through SubArray, it came back with
nothing.

At the end of the day, even after I corrected the .Select it still
didn't work.

The solution was to unload another program I had in Excel that is the
D/B query tool from our ERP tool to Excel. For whatever reason, it was
keeping all sorts of things from executing.

Thanks for your response.
 
Top