Macro working on my pc but no one else's

G

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!!
 
D

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.


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!!
Click to expand...
 
G

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?
 
D

Dave Peterson

None from me.
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?
Click to expand...
 
M

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.
 
G

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 workin
copy on the network and accessing it form other pcs. No go. It seem
like an issue with excel itself and not the macro, but I can't figur
out what could be different on my machine that allows it to work
 
M

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.
 
G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can Vba workout if a selection is part of a group? 0
Inserting 2 columns when cells don't match 6
Run Time Error "13": Type mismatch 6
Entering data from a userform into multiple sheets 2
Issues with UserForm Box 5
Variable counting of rows 7
Between Dates Criteria 4
Copy filtered data to sheet 2 1

Top