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 lineSheetData = UsedRG.ValueI don't know if array transfers work in excel 97, but one thing you could try is changing the Dim statement for SheetData fromDim SheetData()to just Dim SheetData
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:Dim MyRange As Range, cella As Range, Expt As Long, i As LongIf that doesn't fix it, try removing the "As Range" after "cella", and adding .Cells after MyRange, like:Dim MyRange As Range, cella, Expt As Long, i As Long
'some code
For Each cella In MyRange.CellsOccasionally 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:

Do While cella.Offset(0, i).Value <> 0


thanks for looking into this
zach

[mvidas]

Hmmmm.....
Just a guess, but maybe tryDo While Len(cella.Offset(0, i).Value) > 0
'or
Do While Len(cella.Offset(0, i).Text) > 0

[vzachin]

Matt,

thanks so much!

this worked

Do While Len(cella.Offset(0, i).Value) > 0


didn't bother trying the other method

thanks again
zach