Macro working on my pc but no one else's

[goose]

Ok, so I write myself a macro at home in excel xp, which works beautifully. I send it to myself at work, where I have excel 97. Still works beautifully. I send it to the person sitting next to me, whose computer was built on the same image as mine and also has excel 97.

After a few lines of code excel gives an illegal operation message and shuts down. Same result on every other pc I've tried except my home and work pcs.

Even more odd is that there doesn't seem to be a particular line of code causing the problem. I ran the macro line by line on another pc, and it seemed to work fine as long as I put a breakpoint every 5 lines or so. Once I try to continue without a breakpoint, it shuts down again.

My coding isn't that great, I realize, but it does what I need it to on my computer. Anyone care to guess why it won't work on anyone else's. I've never had this problem before, but then again normally I only make macros for my own personal use so I don't have to make them user-friendly.

Columns("B:B").Select
Selection.Delete shift:=xlToLeft
Range("c1").Select
If ActiveCell = "" Then
Columns("c:c").Select
Selection.Delete shift:=xlToLeft
Columns("e:e").Select
Selection.Insert shift:=xlToRight
Range("h13").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-12]C[-5]:R[19987]C[-5])"
End If
Range("a1").Select
Do
If ActiveCell.Offset(0, 2) = "" Then
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Columns("A:C").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("H14").Select
qout = ActiveCell.Value
Range("h11").Select
Do
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell.Offset(-1, 0) <> ""
ActiveCell = qout
outren = ActiveCell.Offset(0, -1).Value
Range("h11").Select
Do
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell <> ""
dropcount = ActiveCell.Offset(0, -1)
Range("d1").Select
Do
countup = 1
Do
ActiveCell = countup
ActiveCell.Offset(1, 0).Select
countup = countup + 1
Loop Until countup > dropcount Or ActiveCell.Offset(0, -1) = ""
Loop Until ActiveCell.Offset(0, -1) = ""
Columns("A:D").Select
Range("A49").Activate
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("C1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

Do
Range("h1").Select
If ActiveCell = "" Then
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell <> "" Or ActiveCell.Offset(0, -1) = ""
End If
If ActiveCell <> "" Then
target = ActiveCell.Value
dropno = ActiveCell.Offset(0, -1).Value
ActiveCell.ClearContents
Range("c1").Select
current = 0
Do
If ActiveCell.Offset(0, 1) = dropno Then
current = current + ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, 1) <> dropno
Do
If current <> target Then
If current > target Then
xout = current - target
Range("c1").Select
Do
If ActiveCell > xout Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell <= xout Or ActiveCell = ""
If ActiveCell = "" Or ActiveCell.Offset(0, 1) <> dropno Then
Range("C1").Select
current = current - ActiveCell.Value
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + 10
ElseIf ActiveCell <> "" Then
ActiveCell.Offset(0, -2).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
ActiveCell.Offset(1, 0).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell = ActiveCell + 10
xout = xout - ActiveCell.Offset(0, -1)
current = target + xout
End If
ElseIf current < target Then
xout = target - current
Range("c1").Select
Do
If ActiveCell.Offset(0, 1) = dropno Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset(0, 1) <> dropno
Do
If ActiveCell > xout Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell <= xout Or ActiveCell = ""
If ActiveCell = "" Then
Range("c1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, 1) <> dropno
current = current + ActiveCell.Value
ActiveCell.Offset(0, 1) = dropno
ElseIf ActiveCell <> "" Then
ActiveCell.Offset(0, 1) = dropno
xout = xout - ActiveCell
current = target - xout
End If
End If
Range("d1").Select
Columns("A:D").Select
Range("A49").Activate
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("C1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End If
Loop Until xout = 0
Range("a1").Select
Do
If ActiveCell.Offset(0, 3) = dropno Then
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
ActiveSheet.Previous.Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, 3) <> dropno
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
End If
Range("d1").Select
Do
If ActiveCell > 10 Then
ActiveCell = dropno + 1
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Columns("A:D").Select
Range("A49").Activate
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("C1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("a1").Select
Loop Until ActiveCell = ""
ActiveSheet.Next.Select
Range("d1").Select
Do
If ActiveCell = outren Then
ActiveCell = 99
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Columns("C:G").Select
Selection.Insert shift:=xlToRight
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 9), Array(6, 9), Array(14, 9), Array(16, 9), _
Array(20, 9)), TrailingMinusNumbers:=True
Range("C1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Columns("D:G").Select
Selection.Delete shift:=xlToLeft
Columns("A:B").Select
Range("B1").Activate
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-75
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:C").Select
Range("C1").Activate
Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft
Range("A1").Select
Do
If ActiveCell.Offset(0, 1) = 99 Then
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
Range("a1").Select
Do
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert shift:=xlDown
ActiveCell.Offset(0, 2).FormulaR1C1 = "[zipcrrt]"
ActiveCell.Offset(1, 0).Select
curdrop = ActiveCell.Offset(0, 1).Value
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, 1) <> curdrop
Loop Until ActiveCell = ""
Columns("A:B").Select
Selection.Delete shift:=xlToLeft

Thanks!!

[Dave Peterson]

I don't see anything that would cause excel to crash--I do see things that would
cause the macro to fail, though.

TrailingMinusNumbers:=True was added in xl2002. But that should not cause excel
to crash and burn. You should be getting a compile error.

Does the code always fail in a particular workbook? Does it work ok in a test
workbook--just enough info in that test workbook to be able to test ok?

You may want to compile the code first (via debug|compile project in the VBE).

Maybe running Rob Bovey's code cleaner would help:
You can find it here:
http://www.appspro.com/

Or maybe rebuildint the workbooks that crash excel would work.



goose wrote:
>
> Ok, so I write myself a macro at home in excel xp, which works
> beautifully. I send it to myself at work, where I have excel 97.
> Still works beautifully. I send it to the person sitting next to me,
> whose computer was built on the same image as mine and also has excel
> 97.
>
> After a few lines of code excel gives an illegal operation message and
> shuts down. Same result on every other pc I've tried except my home
> and work pcs.
>
> Even more odd is that there doesn't seem to be a particular line of
> code causing the problem. I ran the macro line by line on another pc,
> and it seemed to work fine as long as I put a breakpoint every 5 lines
> or so. Once I try to continue without a breakpoint, it shuts down
> again.
>
> My coding isn't that great, I realize, but it does what I need it to on
> my computer. Anyone care to guess why it won't work on anyone else's.
> I've never had this problem before, but then again normally I only make
> macros for my own personal use so I don't have to make them
> user-friendly.
>
> Columns("B:B").Select
> Selection.Delete shift:=xlToLeft
> Range("c1").Select
> If ActiveCell = "" Then
> Columns("c:c").Select
> Selection.Delete shift:=xlToLeft
> Columns("e:e").Select
> Selection.Insert shift:=xlToRight
> Range("h13").Select
> ActiveCell.FormulaR1C1 = "=SUM(R[-12]C[-5]:R[19987]C[-5])"
> End If
> Range("a1").Select
> Do
> If ActiveCell.Offset(0, 2) = "" Then
> Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
> Selection.ClearContents
> End If
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell = ""
> Columns("A:C").Select
> Selection.Sort Key1:=Range("C1"), Order1:=xlDescending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
> _
> DataOption1:=xlSortNormal
> Range("H14").Select
> qout = ActiveCell.Value
> Range("h11").Select
> Do
> ActiveCell.Offset(-1, 0).Select
> Loop Until ActiveCell.Offset(-1, 0) <> ""
> ActiveCell = qout
> outren = ActiveCell.Offset(0, -1).Value
> Range("h11").Select
> Do
> ActiveCell.Offset(-1, 0).Select
> Loop Until ActiveCell <> ""
> dropcount = ActiveCell.Offset(0, -1)
> Range("d1").Select
> Do
> countup = 1
> Do
> ActiveCell = countup
> ActiveCell.Offset(1, 0).Select
> countup = countup + 1
> Loop Until countup > dropcount Or ActiveCell.Offset(0, -1) = ""
> Loop Until ActiveCell.Offset(0, -1) = ""
> Columns("A:D").Select
> Range("A49").Activate
> Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
> Key2:=Range("C1") _
> , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
> MatchCase:= _
> False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
> DataOption2 _
> :=xlSortNormal
>
> Do
> Range("h1").Select
> If ActiveCell = "" Then
> Do
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell <> "" Or ActiveCell.Offset(0, -1) = ""
> End If
> If ActiveCell <> "" Then
> target = ActiveCell.Value
> dropno = ActiveCell.Offset(0, -1).Value
> ActiveCell.ClearContents
> Range("c1").Select
> current = 0
> Do
> If ActiveCell.Offset(0, 1) = dropno Then
> current = current + ActiveCell.Value
> End If
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell.Offset(0, 1) <> dropno
> Do
> If current <> target Then
> If current > target Then
> xout = current - target
> Range("c1").Select
> Do
> If ActiveCell > xout Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until ActiveCell <= xout Or ActiveCell = ""
> If ActiveCell = "" Or ActiveCell.Offset(0, 1) <> dropno Then
> Range("C1").Select
> current = current - ActiveCell.Value
> ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + 10
> ElseIf ActiveCell <> "" Then
> ActiveCell.Offset(0, -2).Select
> Range(Selection, Selection.End(xlToRight)).Select
> Selection.Cut
> ActiveCell.Offset(1, 0).Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(1, 0).Select
> ActiveSheet.Paste
> ActiveCell.Offset(0, 3).Select
> ActiveCell = ActiveCell + 10
> xout = xout - ActiveCell.Offset(0, -1)
> current = target + xout
> End If
> ElseIf current < target Then
> xout = target - current
> Range("c1").Select
> Do
> If ActiveCell.Offset(0, 1) = dropno Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until ActiveCell.Offset(0, 1) <> dropno
> Do
> If ActiveCell > xout Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until ActiveCell <= xout Or ActiveCell = ""
> If ActiveCell = "" Then
> Range("c1").Select
> Do
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell.Offset(0, 1) <> dropno
> current = current + ActiveCell.Value
> ActiveCell.Offset(0, 1) = dropno
> ElseIf ActiveCell <> "" Then
> ActiveCell.Offset(0, 1) = dropno
> xout = xout - ActiveCell
> current = target - xout
> End If
> End If
> Range("d1").Select
> Columns("A:D").Select
> Range("A49").Activate
> Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
> Key2:=Range("C1") _
> , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
> MatchCase:= _
> False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
> DataOption2 _
> :=xlSortNormal
> End If
> Loop Until xout = 0
> Range("a1").Select
> Do
> If ActiveCell.Offset(0, 3) = dropno Then
> Range(Selection, Selection.End(xlToRight)).Select
> Selection.Cut
> ActiveSheet.Next.Select
> ActiveSheet.Paste
> ActiveCell.Offset(1, 0).Select
> ActiveSheet.Previous.Select
> End If
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell.Offset(0, 3) <> dropno
> Range(Selection, Selection.End(xlToRight)).Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Cut
> Range("A1").Select
> ActiveSheet.Paste
> End If
> Range("d1").Select
> Do
> If ActiveCell > 10 Then
> ActiveCell = dropno + 1
> End If
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell = ""
> Columns("A:D").Select
> Range("A49").Activate
> Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
> Key2:=Range("C1") _
> , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
> MatchCase:= _
> False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
> DataOption2 _
> :=xlSortNormal
> Range("a1").Select
> Loop Until ActiveCell = ""
> ActiveSheet.Next.Select
> Range("d1").Select
> Do
> If ActiveCell = outren Then
> ActiveCell = 99
> End If
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell = ""
> Columns("C:G").Select
> Selection.Insert shift:=xlToRight
> Columns("B:B").Select
> Selection.TextToColumns Destination:=Range("B1"),
> DataType:=xlFixedWidth, _
> FieldInfo:=Array(Array(0, 1), Array(4, 9), Array(6, 9),
> Array(14, 9), Array(16, 9), _
> Array(20, 9)), TrailingMinusNumbers:=True
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
> Range("B1").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(0, 1).Select
> Range(Selection, Selection.End(xlUp)).Select
> Selection.FillDown
> Columns("D:G").Select
> Selection.Delete shift:=xlToLeft
> Columns("A:B").Select
> Range("B1").Activate
> Range("C1").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> ActiveWindow.SmallScroll Down:=-75
> Range("F1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Columns("A:C").Select
> Range("C1").Activate
> Application.CutCopyMode = False
> Selection.Delete shift:=xlToLeft
> Range("A1").Select
> Do
> If ActiveCell.Offset(0, 1) = 99 Then
> ActiveCell.Rows("1:1").EntireRow.Select
> Selection.Delete shift:=xlUp
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until ActiveCell = ""
> Range("a1").Select
> Do
> ActiveCell.Rows("1:1").EntireRow.Select
> Selection.Insert shift:=xlDown
> ActiveCell.Offset(0, 2).FormulaR1C1 = "[zipcrrt]"
> ActiveCell.Offset(1, 0).Select
> curdrop = ActiveCell.Offset(0, 1).Value
> Do
> ActiveCell.Offset(1, 0).Select
> Loop Until ActiveCell.Offset(0, 1) <> curdrop
> Loop Until ActiveCell = ""
> Columns("A:B").Select
> Selection.Delete shift:=xlToLeft
>
> Thanks!!
>
> --
> goose
> ------------------------------------------------------------------------
> goose's Profile: http://www.excelforum.com/member.php...o&userid=30537
> View this thread: http://www.excelforum.com/showthread...hreadid=501919

--

Dave Peterson

[goose]

I know there are a few things that would make it fail. The trailingminusnumbers=true is not included in the code that I'm using in 97. I must have copied and pasted from the XP version. I had to replace the code for the sorts and the text to columns when I moved it to 97. Other than that everything is the same. It has never failed on my 97 machine, but it's still causing excel to crash on my coworkers' 97 machines.

Any other ideas?

[Dave Peterson]

None from me.

goose wrote:
>
> I know there are a few things that would make it fail. The
> trailingminusnumbers=true is not included in the code that I'm using in
> 97. I must have copied and pasted from the XP version. I had to
> replace the code for the sorts and the text to columns when I moved it
> to 97. Other than that everything is the same. It has never failed on
> my 97 machine, but it's still causing excel to crash on my coworkers' 97
> machines.
>
> Any other ideas?
>
> --
> goose
> ------------------------------------------------------------------------
> goose's Profile: http://www.excelforum.com/member.php...o&userid=30537
> View this thread: http://www.excelforum.com/showthread...hreadid=501919

--

Dave Peterson

[Mark Lincoln]

On the computers on which your code is failing, check to see if the
Analysis Toolpak and Analysis Toolpak/VBA add-ins are installed:

Tools|Add-Ins...

Most of my workbooks have problems with new XL97 installations that
don't have those checked.

[goose]

Everything was good to go, still no luck on any pc but mine.

I have also tried copying to a new workbook, and putting my working copy on the network and accessing it form other pcs. No go. It seems like an issue with excel itself and not the macro, but I can't figure out what could be different on my machine that allows it to work.

[Mark Lincoln]

You could make a list of the Task List items on your work PC and
compare it to the Task List on the next guy's PC on which the macro
bombs. Look for anything different and go from there.

If they are indeed built on the same image and running the same
software, I would suspect there's a configuration difference that's
causing the problem. User account or security configs in particular.

Antivirus programs are a good place to look for conflicts, although if
I have problems with these it usually involves running the workbook
from a network server. Try temporarily shutting off any
auto-protection and see if the problem goes away. (When McAfee's
equivalent to Norton's Auto-Protect is running, it just kills working
with workbooks on our Novell server. But I don't know for sure if it
would kill macro activity on the PC on which it's running. NAV's
Auto-Protect is never an issue.)

You might want to do a stare-and-compare on the configs between your
working copy of Excel at work versus one of those on which this macro
bombs. Remember to check the add-ins.

Other than that, I'd start looking for gremlins.

I have a PC at home running XL97 under Win98. If all else fails, you
could email your workbook to me (assuming it doesn't contain sensitive
info) and I could test it here.

[goose]

Can't believe it.

Our pcs are set to download windows updates automatically, but aside from myself no one at work seems to want to wait the extra five minutes in the morning to let the updates install. So everyone else whose pc I tried this on was running XP SP1, and I'm running XP SP2.

So after I made them sit through an hour's worth of windows updates, everyone else was able to use the script.

Beautiful.

[Mark Lincoln]

Apparently you *work* with gremlins! :-)

Glad to see you've solved the mystery.