• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting with Macro -Furmula dose not reflect previous page and mixes up results

F

Foxy17

New Member
Macros Created for all colums do be sorted , how ever they all work on Voyage A , but mix up the ansers on Voyage B and C, so I recon it's the frmula that being problematic and not the macro. I admit I have posted this recently butI've been assured that my macros are ok, so I can see the solution to the problem. A final call for some help if possible. thanks


http://speedy.sh/Ssc4j/Small-File.xlsm
 
Foxy17


Can you please step us through this step by step as I cannot see what is wrong ?


When Voyage A is sorted

Voyage B adjusts accordingly and maintains the references to the correct cells in Voyage A and the same for Voyage C to B
 
Ok Hui, I'll try and make myself clearer, click on Voyage a and sort by clicking on the grey macro "JOB" and you can see D8 hase the value of 9-Jan-12. If you go to Voyage B they are also sorted correctly with 9-Jan-12. On voyage B click on the Desription macro and the cell in question now is D13 it has changed the date to 10-Jan-12 Voyage C is the same wrong date with only voyahe a retaining the correct date. D11 in voyage C also gets lost. I hope this has made it abit clearer , sorry for not geting this out initially. thanks again
 
Foxy


That's quite Bizarre !!!


Unfortunately I don't have time to look at it tonight, but will tomorrow
 
Hi, Foxy17!


Maybe you want to give a look at this file:

http://dl.dropbox.com/u/60558749/Macro%20not%20working%20for%20sorting%20multiple%20worksheets%20-%20Small%20File%20%28for%20Foxy17%20at%20chandoo.org%29.xlsm


I made the following changes:

a) for existing worksheets, nothing

b) created a new worksheet (copied from Voyage A) to show alternatives 2 & 3

c) created a module Module0 with many suggestions about the other 8 modules

d) in Module 0, you'll find three alternatives:

1) no changes to your worksheets but unifying (and rectifying) 8 (actually 7) sort procedures

2) replacing your shape buttons with assigned macros (form control command buttons) by ActiveX command buttons with according code for click event

3) same as 2) but with only one command button, using active cell (selected cell) as ordering criteria


About the error you reported, yes, annoying but it was there. With first alternative it disappeared and remained so with second and third. I didn't corrected your 8 (that should be 7) sort procedures to check if rectifying them to proper adjusted columns they would work or not. If you opt for maintaining your original code structure, please update it as it should be for correct columns and check if the error continues.


Just advise if any issue no matter the path you choose.


Regards!
 
Hi SirJB7

Ok I've gon with option 2, although a bit out of my depth. Have alook at the file. I think I have created it correctly but errors are still there.The only time the error is not there is when sorting by column Voyage A in every sheet. Unless of cousre I haven't correctly done what you mentioned


http://speedy.sh/gSpQE/Small-File-V2.xlsm
 
It may be the formula that is in B and C sheets


http://speedy.sh/URpQd/Small-with-added-VBA.xlsm


Due On Completed

=IF('Voyage A'!E5=0,"",'Voyage A'!E5+30) 40945

=IF('Voyage A'!E9=0,"",'Voyage A'!E9+30) 40948

=IF('Voyage A'!E11=0,"",'Voyage A'!E11+30) 40948

=IF('Voyage A'!E12=0,"",'Voyage A'!E12+30) 40948

=IF('Voyage A'!E10=0,"",'Voyage A'!E10+30) 40948

=IF('Voyage A'!E6=0,"",'Voyage A'!E6+30) 40945

=IF('Voyage A'!E13=0,"",'Voyage A'!E13+30) 40948

=IF('Voyage A'!E7=0,"",'Voyage A'!E7+30) 40946

=EDATE('Voyage A'!D8,6) =IF(D13>TODAY(),"Not Due","Due")


I would have hoped that EDATE('Voyage A'!D8,6) would have changed to EDATE('Voyage A'!D13,6)


But I may be asking something that is not possible, thanks
 
Hi, Foxy17!


I took your Small-File-V2.xlsm (which BTW was finely modified), messed it up a little and uploaded as:

http://dl.dropbox.com/u/60558749/Sorting%20with%20Macro%20-Furmula%20dose%20not%20reflect%20previous%20page%20and%20mixes%20up%20results%20-%20Small%20File%20V2%20%28for%20Foxy17%20at%20chandoo.org%29.xlsm


I set up two alternatives (yes, yes, I know what you're thinking...):


First, sheet "Voyage B":

a) replaced search condition 'Voyage A'!E5=0 by:

ISTEXT(VLOOKUP(A5,'Voyage A'!A:G,5,FALSE))

Because of "Not Due" values.

b) replaced IF condition (which caused the problem or changing row because of relative changing addresses) by VLOOKUP functions:

VLOOKUP(A5,'Voyage A'!A:G,5,FALSE) if it wasn't text (ergo, previously completed)

VLOOKUP(A5,'Voyage A'!A:G,4,FALSE) if it was text (ergo, previously not due)

Which in fact looks for column A cell values within range A:G on previous sheet and retrieves 5th column value (E) -and 4th (D)-

c) replaced Date+30 days and EDATE+6 months by unified EDATE+N months, where N is the value for first character of column C cells or a 1 if it isn't numeric (so adopting the same criteria as it wasn't the same Date+30 days than D+1 month):

IF(ISNUMBER(VALUE(LEFT(C5,1))),VALUE(LEFT(C5,1)),1)


Second sheet, "Voyage C":

Same changes as above, but using OFFSET and ADDRESS functions for referencing cells, changing VLOOKUP of previous paragraph by:

INDIRECT(ADDRESS(ROW(),COLUMN()+1,4,1,"Voyage B"))

INDIRECT(ADDRESS(ROW(),COLUMN(),4,1,"Voyage B"))

INDIRECT takes a built address and returns the value of the reference

ADDRESS builds a string reference providing row number, column number, relative (4) format, A1 (1) notation, and related sheet.


It's up to you to choose between sheet 2 or 3 models.


Hope this helps and doesn't confuse you a lot. If so, please advise.


Regards!
 
Wow !!!!! I need a vodka to digest that one. I'll leave it until the morning , once my braincells have got otgether again after reading it.


thanks a million for that.

Have anice day
 
Hi, Foxy17!

Why only one vokda? Maybe I'm getting a little thirsty too...

Tell us if you succeeded... after the six-pack vodka bottles :)

Regards!
 
Hi, Foxy17!

I nearly forget it: what's up with column E? Should it have the formula for "Due"/"Not Due" or the actual completion date?

Regards!
 
When the job is completed the due will be overwritten with the date and the next pages will resume again, as they did in Voyage B


thank again for all your efforts . It is really appreciated.
 
Hi, Foxy17!

Glad to help you. Thanks for your feedback and for your kind words too. Just advise if any issue. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top