Jay Petrulis (reporting back)

[SteveC]

Thank you for help on *any* part of this. I cannot figure out the VBA to do this. I'm using Excel 2002. Here's what I'm trying to do:

If there is data (a number) in a cell in either M13 thru M130 or in N13 thru N130
Then copy the cells in columns A, M & N on that same row
And paste into columns DM, DN & DO, respectively
(on any row from 194 thru 358, where the cells in columns DM, DN & DO are empty).
Then, in rows 194 thru 358, sort DM, DN & DO by DM (a date - mmddyy), with the earliest date at the top.

If all three cells to be copied will be an exact match of existing cells on a line in DM, DN & DO (194 thru 358), a duplicate must not be written. (This condition will happen often.)

Any ideas on *any part* of this?
Thanks
SteveC
This message was edited by SteveC on 2002-09-23 20:50
This message was edited by SteveC on 2002-09-26 23:19

 

[SteveC]

CORRECTION:

Jay,
Sorry - I reported that your code had sorted months. It did not sort anything. (I was fooled by my own test pattern.)
Steve
This message was edited by SteveC on 2002-09-20 19:43

 

[Jay Petrulis]

Hi,

Please do the following and report your results.

1. Run the routine as normal, with the Rng.Sort problem still commented out so that the code is ignored.

2. Turn on the macro recorder and manually sort the destination range as you desire.

3. Post the resulting code.

I cannot see why the code is failing on that part, but it is probably something quite simple.

 

[SteveC]

Jay,
I recorded the sorting macro and it sorted correctly as I recorded it. However, it errored out every time I ran it. I deleted it and re-recorded it 3 times with the same results. When I changed some of the data (in DM, DN, DO) and ran it, it errored out with the same error code (1004) and explanation (Sort method of Range class failed) as your code.

What do you think? Thanks for your time.
Steve

---------------
Sub SORTmacro()
'
' SORTmacro Macro
' Macro recorded 9/23/2002 by SC
'
' Keyboard Shortcut: Ctrl+s
'
Range("DM194:DO358").Select
Selection.Sort Key1:=Range("DM194"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
This message was edited by stevec on 2002-09-26 23:14

 

[SteveC]

This message was edited by SteveC on 2002-09-25 12:38

 

[SteveC]

deleted
This message was edited by SteveC on 2002-09-27 10:38

 

[SteveC]

deleted
This message was edited by SteveC on 2002-09-27 10:36

 

[SteveC]

deleted
This message was edited by SteveC on 2002-09-27 10:36

 

[SteveC]

Any suggestions as to why the code is creating errors?
----
----
Please ignore the duplicate messages above. I've been having browser problems - I will re-install.
Sorry -SteveC
This message was edited by SteveC on 2002-09-27 10:41

 

[Jay Petrulis]

Hi,

I don't understand why the code works when you record it, and not when you rerun the routine. There are no references that you need to set, as you would if you tried to use the Solver in a macro, for instance.

Are there blanks in row 194 at any time (the relveant cells, of course)? Does the sheet remain the active sheet. so that your referencing remains OK?

This seems weird in any event. I don't have much of an answer for you here. If you'd like, send the workbook (delete or hide and sensitive data), and I will have a look.

john.petrulis@notes.ntrs.com

 

[SteveC]

Jay,
Any luck with my sorting problem after I sent the full apreadsheet on 10/1?
If you don't have time, that's OK, too. Let me know. Thanks.
SteveC