Solved: Consolidating Columns 2

[vzachin]

Hi,

In a previous post
http://www.vbaexpress.com/forum/showthread.php?t=8528
I received 2 solutions which works flawlessly in Excel 2002.

I am now trying to run them in Excel 97 but am running into problems.

Utilizing ALe's solution, i am getting a "Compile Error: Can't Assign to Array" .
Utilizing Matt's solution, i am getting a "Run-Time Error 13:Type Mismatch".

Any ideas as to how i can fix this to run in Excel 97?

thanks
zach

[vzachin]

had the errors reversed

Utilizing ALe's solution, i am getting a "Run-Time Error 13:Type Mismatch".
Utilizing Matt's solution, i am getting a "Compile Error: Can't Assign to Array" .

[mvidas]

Hi Zach,

I can't say for sure about mine, as I don't have xl97 to test it on, but I'm guessing it is on the line[vba]SheetData = UsedRG.Value[/vba]I don't know if array transfers work in excel 97, but one thing you could try is changing the Dim statement for SheetData from[vba]Dim SheetData()[/vba]to just [vba]Dim SheetData[/vba]
Perhaps xl97 doesn't like variant arrays. Could very well just be a limitation of xl97 though.

Matt

[vzachin]

hi Matt,

that works !

before i mark this solved, i want to see if ALe has a solution as well

thanks
zach

[mvidas]

What line are you getting the error on for his code?

Looking at his code, it looks like the variables "Expt" and "i" aren't dimensioned. I doubt that would cause the error, but it is possible. Try changing his Dim line to:[vba]Dim MyRange As Range, cella As Range, Expt As Long, i As Long[/vba]If that doesn't fix it, try removing the "As Range" after "cella", and adding .Cells after MyRange, like:[vba]Dim MyRange As Range, cella, Expt As Long, i As Long
'some code
For Each cella In MyRange.Cells[/vba]Occasionally I've received errors using a non-variant variable in a For Each statement, that could do it. If not, please explain the line the error occurs on. This always helps us debug the problem.

Matt

[vzachin]

hi Matt,
i tried your 2 suggestions but it didn't work.
this is the line that is highlighted:
[VBA]
Do While cella.Offset(0, i).Value <> 0
[/VBA]

thanks for looking into this
zach

[mvidas]

Hmmmm.....
Just a guess, but maybe try[vba]Do While Len(cella.Offset(0, i).Value) > 0
'or
Do While Len(cella.Offset(0, i).Text) > 0[/vba]

[vzachin]

Matt,

thanks so much!

this worked
[VBA]
Do While Len(cella.Offset(0, i).Value) > 0
[/VBA]

didn't bother trying the other method

thanks again
zach