View Full Version : Solved: Consolidating Columns 2
vzachin
08-29-2006, 10:51 AM
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
08-29-2006, 10:55 AM
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
08-29-2006, 11:00 AM
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
08-30-2006, 05:56 AM
hi Matt,
that works !
before i mark this solved, i want to see if ALe has a solution as well
thanks
zach
mvidas
08-30-2006, 06:30 AM
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
08-30-2006, 07:38 AM
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
08-30-2006, 07:47 AM
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
08-30-2006, 11:08 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.