Programme doesn't work when sent to a different computer - Why?

[Wolfy71]

Hi,

I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying.

The part of the programme I'm having difficulty with is this:

Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Looking around on the net, I think the problem may lie with the fact I haven't yet named my project file and this is causing the above not to be recognized on the new computer - but I'm still confused about the steps I should take and what I should be looking for...

Any ideas about which proverbial tree I should be barking up, here?

Thank you in advance

Adam

 

[Andy B]

Just a thought, are you using Excel 2007 at work?

If so Range("SsNo1") is a cell reference (SSN1) as well as your range name, try giving this range a different name but watch out for names with 3 letters and a number!

 

[LucB]

Hi Adam,

This part of your code.

Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Delete the last statement.

Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Hope this helps.

 

[Wolfy71]

Well that's the strange thing both packages are Excel 2003.

I can understand named ranges getting confused but would it still do so with three letters and a number?

You don't think it's because I haven't named the project and assigned modules, then? (Andy B)

Thanks LucB I'll try that and keep you posted (So to speak.)

 

[Norie]

Adam

How does the Sort not work?

What VLOOKUP(s) are you referring to?

By the way the only naming issue I can imagine could be if you named 2 things the same,and I don't think that would even apply to projects/modules.

One thing you might want to check is the named ranges.

Also you could try not using Select/Selection.

Range("SsNo1").Resize(d, f).Offset(a, 0).Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Wolfy71]

Thanks Norie,
Will try your suggestions out and let you know.
Regards,
Adam

 

[Wolfy71]

No - thanks but your suggestions didn't work out. Having looked at it again though - I think it may be to do with not recognizing a date (I tried sorting manually and it messed up again - and I think that is something to do with add-ins.)

Sorry if I've been leading you up the primrose path...

Any ideas on add-ins and dates?

Regards,

Adam

 

[RoryA]

Where do addins come into this? There shouldn't be a problem with dates stored as dates (unless you go from Mac to PC or vice versa) but if you used a DATEVALUE formula, that could go wrong depending on the regional settings; a DATE formula is preferable.

 

[AdamL]

Where do addins come into this? There shouldn't be a problem with dates stored as dates (unless you go from Mac to PC or vice versa) but if you used a DATEVALUE formula, that could go wrong depending on the regional settings; a DATE formula is preferable.

This has turned into a bit of a cross-post

http://www.mrexcel.com/forum/showthread.php?t=388395

but I'm guessing formulae using eg "31/12/2008"+0 would also produce the same issues.

 

[Wolfy71]

I'm sure the problem is to do with the dates, Rory. I'm trying to sort a range of data automatically, within the range there are two columns; one lists dates and the other turns the dates into a general number - the latter is the list which goes wrong. Some of the numbers in the second column refer to the first column fine - others, however, return '#VALUE!' and I can't find the right formatting for it...