Macro will work on one computer but not on another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gillian - 12 Jun 2007 15:33 GMT

I have recently set up three Excel spreadsheets for formatting data imported
from a text file generated in a DOS program. Once the data is pasted into
the master spreadsheet it is renamed with a date and a set of two or three
macros that manipulate the data, inserting/hiding columns, adding up,
subtotals, formatting, etc.

The files are held on a shared drive on our network.

On my computer (using Excel 2000 on WinXP) each of the four files and their
macros work perfectly. On my colleague's computer (using the same version of
Excel and XP) only two of the macros will work. On the other two we get a
Debug message. When we look at the macro using F8 to step through we can see
that the only functions to work are those where text is typed in. For
example, the first few rows of the first macro are this:

Range("A1").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Supplier"
Range("B1").Select
ActiveCell.FormulaR1C1 = "SageRef"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Item"
Range("D1").Select

What happens, though, is:
No row is inserted
All the above text is written into A1 instead of moving to B1, C1, D1,
and so on.
So of course the macro soon fails altogether.

I am completely stumped!
Any suggestions very gratefully received.

Reply to this Message

PCLIVE - 12 Jun 2007 15:42 GMT

Not sure, but I would simplify your code a little.

Rows("1:1").Insert Shift:=xlDown
Range("A1").Value = "Supplier"
Range("B1").Value = "SageRef"
Range("C1").Value = "Item"
Range("D1").Select

HTH,
Paul

>I have recently set up three Excel spreadsheets for formatting data
>imported
[quoted text clipped - 32 lines]
> I am completely stumped!
> Any suggestions very gratefully received.

Reply to this Message

Gillian - 12 Jun 2007 15:55 GMT

Thanks,
I'll try that.
Incidentally, I didn't write the actual code - that's what Excel put in for
me when I did a simple 'Record Macro' and it recorded my keystrokes.

> Not sure, but I would simplify your code a little.
>
[quoted text clipped - 43 lines]
> > I am completely stumped!
> > Any suggestions very gratefully received.

Reply to this Message

Gillian - 14 Jun 2007 11:27 GMT

I have 'solved' this problem... see the post submitted today in response to bj.
Regards,
Gillian

> Not sure, but I would simplify your code a little.
>
[quoted text clipped - 43 lines]
> > I am completely stumped!
> > Any suggestions very gratefully received.

Reply to this Message

bj - 12 Jun 2007 16:01 GMT

when you step through it does it show B1,C1 etc being selected?
it sounds as though his computer may have a problem with "select"
do other macros work OK?

> I have recently set up three Excel spreadsheets for formatting data imported
> from a text file generated in a DOS program. Once the data is pasted into
[quoted text clipped - 28 lines]
> I am completely stumped!
> Any suggestions very gratefully received.

Reply to this Message

Gillian - 12 Jun 2007 16:09 GMT

The step through process shows that it refuses to budge out of cell A1.
But yes, other macros work perfectly well and they have a very similar set
of instructions except for the first 'insert row' bit.
Also, exactly the same errors happen on another pc in another office as well!
Could it be an MS update issue?

Gillian

> when you step through it does it show B1,C1 etc being selected?
> it sounds as though his computer may have a problem with "select"
[quoted text clipped - 32 lines]
> > I am completely stumped!
> > Any suggestions very gratefully received.

Reply to this Message

bj - 12 Jun 2007 16:41 GMT

go to the computer which has the problem
record a macro for the first couple of steps and see what it says and if it
works.
if it works copy the selection and insert lines and paste into the old macro.
See if it works now?

> The step through process shows that it refuses to budge out of cell A1.
> But yes, other macros work perfectly well and they have a very similar set
[quoted text clipped - 40 lines]
> > > I am completely stumped!
> > > Any suggestions very gratefully received.

Reply to this Message

Gillian - 14 Jun 2007 11:01 GMT

I have finally tracked down the problem, although I still don't understand
why it should happen on one pc but not on another. I was at the computer
where the error was happening and trying once more to step through the macro
before trying your suggestion.

At first I thought it was working as actually went through the first 20 or
so commands. It fell over at the section of macro shown below.

When I stopped the debugger and tried to run it again, that's when it
refused to move out of cell A1 as initially reported in this post and Excel
collapsed to the extent that I had to use the Task Manager to shut it down.

The guilty part of the macro is:

Columns("Y:Y").Select
Selection.Cut
Range("U1").Select
ActiveSheet.Paste

It failed at the paste option, with a run time error.

I just changed the macro to:

Columns("Y:Y").Select
Selection.Copy
Range("U1").Select
ActiveSheet.Paste

And everything works fine. Why? I don't know. But at least it works now.

Thanks for your suggestions.

> go to the computer which has the problem
> record a macro for the first couple of steps and see what it says and if it
[quoted text clipped - 46 lines]
> > > > I am completely stumped!
> > > > Any suggestions very gratefully received.

Reply to this Message

bj - 14 Jun 2007 13:30 GMT

If you want column Y to be removed you might want to add

Columns("Y:Y").Select
Application.CutCopyMode = False
Selection.Clear

it is an oddity. I could not make your code fail on my computer 2003 XP

> I have finally tracked down the problem, although I still don't understand
> why it should happen on one pc but not on another. I was at the computer
[quoted text clipped - 78 lines]
> > > > > I am completely stumped!
> > > > > Any suggestions very gratefully received.

Reply to this Message

Susan - 15 Jun 2007 13:34 GMT

another thing to check, too (even though you've solved it)......
on YOUR computer, open vb editor & look at Tools, Options, and choose
the General tab.
to the (R) you'll see an "Error Trapping" frame. write down which
option your computer has selected. go check the other computers.
if they're not set the same, that may be why it errored out on theirs,
but not on yours. this is an option that's individual to each
computer and is not carried along by macros.
hth
susan

> If you want column Y to be removed you might want to add
>
[quoted text clipped - 88 lines]
>
> - Show quoted text -

Reply to this Message

bj - 15 Jun 2007 20:22 GMT

what version of EXCEL dop you Have? 2003 Xl does not appear to have this option

> another thing to check, too (even though you've solved it)......
> on YOUR computer, open vb editor & look at Tools, Options, and choose
[quoted text clipped - 99 lines]
> >
> > - Show quoted text -

Reply to this Message

Peo Sjoblom - 15 Jun 2007 20:57 GMT

Yes it does, press alt + F11, then do tools>options, select the general tab

Signature

Regards,

Peo Sjoblom

> what version of EXCEL dop you Have? 2003 Xl does not appear to have this
> option
[quoted text clipped - 126 lines]
>> >
>> > - Show quoted text -

Reply to this Message