Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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 he 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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 -- goos ----------------------------------------------------------------------- goose's Profile: http://www.excelforum.com/member.php...fo&userid=3053 View this thread: http://www.excelforum.com/showthread.php?threadid=50191 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
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. -- goose ------------------------------------------------------------------------ goose's Profile: http://www.excelforum.com/member.php...o&userid=30537 View this thread: http://www.excelforum.com/showthread...hreadid=501919 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro working on my pc but no one else's
Apparently you *work* with gremlins! :-)
Glad to see you've solved the mystery. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using someone else's spreadsheet | New Users to Excel | |||
Using someone else's files | Excel Discussion (Misc queries) | |||
make macro run on someone else's desktop | Excel Programming | |||
use somebody else's self certificate to sign a macro | Excel Programming | |||
Total newbie question involving pasting someone else's Macro | Excel Programming |