min and max values b/w two alternate strings(every one is seeing please reply)

[jayaram]

I have posted the excel file.. here i have a Coloum A, B, C, D and E.
i) i have numerical values in coloum A & B( A= High values , B= Low values),
ii) In coloumn C i have the two characters namely "S" letter and "B" letter ..but at certain intervals only i have for example C1= S, C6=B, C9= S, C16= B, C23=S,,like that but alternatively i have B and S .
Coloumn D: in this coloumn i need max and min values at for every B and S signal generated from coloumn C. ( For example i need D6 =min(B2:B6)( because C6= B,) and i need D9= max(A5:A9)(because C9=S)..like that i need to generate a common function so that i can apply for long data instead of doing each cell min and max by seeing B and S or S and B from coloum C.
Coloumn E:: in this coloum i just need difference between two values generated from coloumn D. but difference is something like that for example if C6= B, C9= S, E9=(D9-D6), if C9=S, C16=B then E16= D9-D16) thats it..like that i can do alternatively by observing Coloumn C and D , we can get Coloum E. So for simple understanding u can see the excel file give any common fucntions for Coloum D and Coloum E results automaticaly..so that i can apply for any lengthy data..i am very hope ful if some one helps..in this regard. Thank you very much

[mrice]

Try this macro.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

[jayaram]

Hi.. firstly u have done a wonderful job.. keep it up.. i cant avoid your stuff simple saying thanks. u did a good job and keep it up. i have a small correction in this macro program that u have given me. i) coloumn D is working fine as the format i gave you.ii) coloum E is also ok but slight logic is there that is..correct values for E9, E23, E27, E32...etc all for S signal is correct . But for B signal is difference of max and min is quite opposite. if you open the file i have gave you note also for E coloumn. iii) Coloumn F is similar to Coloumn E but you are finding difference bettween two coloumn Cells instead one coloum cells in coloumn E. can u just make it recorrect of macro for Coloumn E and additonal coloumn F. iv) And finally give me the hint that if my coloumn A B C D E F are somewhere else let us say if they starts from Coloumn I, J,K. L.M,N(treating I=coloum A, J=coloumn B, K=coloumn C, L= coloumn D, M= Coloumn E, N= coloumn F. what modifcation will happen for actuall macro program you gave me. show with hilight mark in you macro file only if u treat same data is existing in I , J,K,L,M,N coloumns. I am very proud of you..if i get a solution form you. Thanks and Regards ,Jayaram

[mrice]

Try this variation

Please Login or Register  to view this content.

If you wanted to make the code more generic and to be able to start in any column on the worksheet, it would be sensible to change the column numbers into variables and have a constant at the start.

e.g.

Please Login or Register  to view this content.

[jayaram]

wow..Sir. u did a good work.. Thanks alot for spending in valuable time for me... i think you are the master in macro algoritham generation..whichever the way we need..it... i hope continue the same and blessings from god..doing this stuff and immediate action to the posts.. YES my problom is solved.. i will do some try as u said..when the coloumn changes or in b/w any coloumn changes.. i am totally no idea about macro programming..but interested to know..about this..for my future work..good sir.. Thanks and Regards..Jayaram

[jayaram]

Hi i have tried but i am little confused.. u just help the program that u have posted above ..with higlight mark. so that i can get clear idea..where the changes has been made. the above algoritham that u posted when my data is in Coloumn A(1), B(2), C(3), D(4), E(5), F(6). Now i just have my data exactly as D(4), E(5), N(14), Q(17), R(18), S(19). Please just modify the macro program that u posted as per my existing data. so that i will come to know. what changes has been made.

[jayaram]

Please Login or Register  to view this content.

i tried this macro programming.. to my execl fle ..i could not get the result as i want. can some one help me what is erro. For your reference i have given the format of cells that i want to generate. please see make any modificaton to the above program by seeing the attached file.
Please see the excel file ..i just need as it is.. no need to change any coloumn... I just want the same format as u said..but i just modified the macro program based on my requiremnt that i have adjusted in my attached excel file final.xls. Please check the file and tell me where i did the mistake in the macro program that i attached. Hope i did this stuff just changing coloumn from starting 4 and also in middle and little changes in my excel. but i tried accordingly to my requirement. but i did not get where is error. Please respond by just observing the excel file.. Hint: as per my rule that i made in macro program Coloumn Q= must depend on D, E and N columns, Column R= must depend on N and O columns, Column S = must depend on N, O and Q columns only. I hope u did a already good stuff. and understand my simple variation . I AM VERY HIGLY APPREICTAED FOR YOUR VALUABLE TIME..JUST TO CHECK THE FILE..AND SEND IT BACK AS A I AM WATING RESPONSE FROM YOUR SIDE.. thanks and regards Jayaram

[mrice]

I've made a few changes, most notably by adding some constants refering to column numbers at the start of the code whic are referred to later. If you need to change the position of the columns, you should only need to change the values of these constants.

Please Login or Register  to view this content.

[jayaram]

Hello sir thank you for your Kind helping nature. Thank you very much. now i can concentrate on my work successfully. i ran the macro program successfully as i want and eventhough i just did a small correction that i want in this macro program and executed perfectly. The corrections is very small thing only THREE LINES i shown in hilighted colour in below program.

Please Login or Register  to view this content.

Thanks alot sir..

modifications:

Please Login or Register  to view this content.