+ Reply to Thread
Results 1 to 38 of 38

macro to move data from one cell to the adjacent,then to the third , then to the fourth

  1. #1
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    macro to move data from one cell to the adjacent,then to the third , then to the fourth

    I have a workbook with five sheets. on sheet one, i have filled data in colmn C. when i Run the macro it should be moved to cell D.
    on the second sheet, when i run the macro the data in colmn D should move to col E and from col C to D.
    on the third sheet when i run the macro the data in col E should move to col F and D to E and C to D.

    It should be continued in the same way from col M and W ( entirely different items)
    on the fourth sheet the macro should be forced to effect the formulea in different columns which have been feeded.
    on the fifth sheet the macro should erase all the data from specified columns.


    data will be entered in columns C,M and W manually by the user.

    kindly provide macros to automate the process. thanks in advance. kindly see the attachment.
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    To run the program has made use of Range A1 initialized with a zero.
    I couldn't see that day 4 required any action -
    so with a zero in A1 for day 1 run - a 1 for day 2 run etc.
    Try this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Hi XLADEPT, thank you very much for the programme.
    But i think it is halfway mark(May be my ignorance,pls instruct)
    actually, the macro should work from the sheet 1. the other sheets are for the guidance of the programmer only.
    when i add data to the column c,m and w for the first three days the macro should move them to the adjecent cell. it is doing ia have checked. fine.
    But after the first four columns are filled, for example from C:F, when i run the macro it should calculate the formula and should give the results. In the present programme , i cannot find that working. Please see columns G:L for example.
    after filling all the four columns, for example C:F, and getting all the calculations, i want to delete all the data. the present macro does not do it.
    Please see to it and instruct me what to do? thanks once again for the co-operation and effort. have a nice day.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    I guess you need the program to install the formulae - I'll see what I can do.

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 03-07-2018 at 01:31 PM. Reason: Details

  5. #5
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thank you very much for the reply.
    But your new macro works partially. please see the new attachment wherein i have shown the day 4 sheet only to illustrate my idea.
    Attached Files Attached Files

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Hi Sumesh,

    Remember that a1 must contain the day number of the last run - this version alters slightly the clearing:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thanks for the help.
    "Remember that a1 must contain the day number of the last run - this version alters slightly the clearing":

    i am not able to follow the above instuction.
    can you pls apply the macro to my sheet and show me how does it work?

    since you have used more than one macro , is it possible to rename it with sumesh1(). sumesh2() ?
    Last edited by sumesh56; 03-09-2018 at 03:20 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Please Login or Register  to view this content.
    "ThankyouFor Attention * And Your Help!!"

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Hi Sumesh,

    If you start with day1 the macro will take care of the day assignment in A1. You can name the macros any thing acceptable for a macro name.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Quote Originally Posted by xladept View Post
    Hi Sumesh,

    If you start with day1 the macro will take care of the day assignment in A1. You can name the macros any thing acceptable for a macro name.
    fantastic !
    the code works as per my wish. thanks a lot for your effort.
    can i ask you one more favour?
    i want to extend the period from 4 days to ten days. pls see the attached file. if you can find some time pls provide a code to work with . no fundamental change in this workbook. after the ninth day column i have added a column for "average" for each catagory. thats all.
    Attached Files Attached Files

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Here's another version for your larger file:

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    [QUOTE=xladept;4863861]Here's another version for your larger file:

    [CODE]Sub SumeshIV()


    Thanks for the new code.
    I am sorry to state that i forgot to add one more sheet named DAY 10 AFTER DAY9. I have added it in the new attached file.pls edit the code to suit the present scenario.

    another thing i want to accomplish is that

    can it be possible to make the formulae work from the second day itself. for example it should compare D with C.
    on the third day when we fill the data and run the macro it should give formula result also. compare E with D and D with C . it should continue like this till day 10.

    pls see the attached file.
    Attached Files Attached Files

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Try this:

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    [QUOTE=xladept;4864584]Try this:

    [CODE]Sub SumeshV():
    thanks for the code.
    but it may be noted that the comparison the code makes is erraneous.
    there will be no comparison on day1 sheet.
    on day2 sheet the comparison can only be done between E and D. see the column heading .there is data only on these two columns at present (after we run the code data from C move to D. then only the comparison should work.)

    but the present code compares F2>E2 in col N
    compares G2>F2 in column O
    there is no data in these two columns at present.

    pls see to it. thanks

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Maybe:

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    when i run the code it gives error report as

    compile error
    expect End Sub

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thanks for the response. but the formula excecuted in sheet day(2) is erraneous.In column O2, it should be
    IF(E2>D2,"Y","N")

    Where as the code takes it as

    IF(G2>F2,"Y","N")

    Pls see the col heading from N:V where as i have shown which columns are to be compared. formula should be according to that direction.

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    I changed formula 1:

    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thanks for the quick response.


    i need some more help in this matter.
    pls see that i have sheets named MY STOCK SAMPLE (DAY1)......MY STOCK SAMPLE (DAY2) ..ETC
    READ MY STOCK SAMPLE (DAY1) as MSSDAY(1)

    I have column header like DAY1.....DAY2...ETC

    after i run the code at sheet mssday(9),.NOW I will be having only the baseprice column (col C) base vol , and base del per columns empty.

    the resultant content should be copied to sheet named MSSDAY(10), here i will fill the details in the baseprice column.,
    then i run the code NOT to get the data transferred to the adjecent column, but to excecute the formula only.

    Now i will be having all the columns filled with data and the formulae excecuted.Now i have ten days data. ( nine days plus the base col)

    Now i want the contents of this sheet MSSDAY(10)be transferred to sheet named MSS RESET, Here i will run the code and all the data will be erased.
    at present when i run the code at sheet mssday(9) the data is erased.
    pls see sheets named MSSDAY(8)MSSDAY(9) and MSSDAY(10) of the newly attached file for clarification. thanks.
    Attached Files Attached Files
    Last edited by sumesh56; 03-20-2018 at 04:12 AM.

  21. #21
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    My code is designed to do everything on just one sheet - have you been creating a sheet for each day?

    I thought that the multi-sheet samples were just to provide an example of each days results.

    So, I've been pondering exactly what is needed. This is my quandary.

  22. #22
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Quote Originally Posted by xladept View Post
    My code is designed to do everything on just one sheet - have you been creating a sheet for each day?
    certainly not. i want that the whole process be done in just two sheets. i will explain. pls see the attached files.

    I thought that the multi-sheet samples were just to provide an example of each days results.
    you were right.
    So, I've been pondering exactly what is needed. This is my quandary.

    allow me,

    on reaching the ninth day, i will be having the base columns empty as well as the first column of the comparison ( col N, col W and col AQ).
    After entering the data in base columns,when i run the code i am supposed to get the columns N,W AND AQ filled with the results.

    but at present when i run the code all data is erased.
    That is why i demanded that when i run the code on sheet named day9 the result in col N.W, AND AQ be filled and all the entries be copied to the next worksheet named day10.


    now only i will be having the full data of ten days with all the calculations of formulae.
    there i will run the code just to get all the data erased.

    plse see the attached file for clarification. pls run the code on the first sheet and find the result. first sheet is supposed to be the Day9 sheet where i have entered the base values.now we have to run the code to get the empty columns of comparison to be filled with results.
    the second sheet is supposed to be the DAy10 sheet where i should get all the data filled. and when i run the code all data should be erased.
    Last edited by sumesh56; 03-26-2018 at 11:53 AM.

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    See if this does it:

    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    is there any prerequisite to run the code? i tested this code on a single sheet named day9.



    I run the code and get this error

    it says
    Runtime error (9)
    subscript out of range
    debugged to see this

    Cells.Copy Sheets("MY STOCKSAMPLE-RESET").Cells(1, 1)



    I tested it on a sheet named My stock sample-Reset.when i run the code all data were erased. what the code should do is to copy all the data from day9 sheet and paste it on another sheet named mystocksample-reset. then on that sheet when i run the code the data should be erased.
    pl see the attached file
    Attached Files Attached Files
    Last edited by sumesh56; 03-27-2018 at 11:46 AM.

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    That just means that there is no sheet named "MY STOCKSAMPLE-RESET".

  26. #26
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    away with a busy work. will be coming back in a week. thanks for the reply ,have a nice day

  27. #27
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Quote Originally Posted by xladept View Post
    That just means that there is no sheet named "MY STOCKSAMPLE-RESET".
    Hi good morning. I have comeback. pls help me in solving out the problem.you are just near the finishing point.
    I have added one sheet named RESET. pls run the code and see yourself and then send to me.pls see the attachment

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Well - I changed the sheet name to correspond to the latest RESET sheet and it seems to work nicely:

    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    [QUOTE=xladept;4881108]Well - I changed the sheet name to correspond to the latest RESET sheet and it seems to work nicely:


    Thanks for the resonse.

    I run the code and get the contents copied to the new sheet named RESET.
    But some of the columns are not filled with data.
    Columns N,W.AQ,AZ and CC are left blank. whereas these are supposed to be filled when i run the code.

  30. #30
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    There is no data in those fields.

  31. #31
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thanks for the reply.

    But pls see that the fields are supposed to be filled with data of the result of the formula we apply.

    for example



    col N is supposed to be filled with the result of the formula IF(D2>C2,"Y","N")
    COL W IS supposed to be filled with the result of the formula =sum(D2-C2)

  32. #32
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    You had me stop including those fields several posts ago

  33. #33
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Quote Originally Posted by xladept View Post
    You had me stop including those fields several posts ago
    sorry for that confusion.

    every time i am quoting the file. pls be kind enough to look through the file .

    let me explain once again.

    In my worksheet, there are three sections viz the price of the shares, the volume and the delivery percentage.

    in each catagory there are three parts. col C:AE covers the first catagory regarding the price.
    col AF:BH covers the second catagory regarding the volume
    col B1: CK covers the thrid catagory regarding the delevery percentage.

    first cell of each catagory is filled by the user and other columns will be filled by the execution of the code.







    (1) column C is always filled by the user.
    col N:V and col W:AE is supposed to be filled by the result of the formula applied which will be brought by the code




    (2) column AF is always filled by the user.
    col AQ:AY and col AZ:BH is supposed to be filled by the result of the formula applied which will be brought by the code



    (3) column B1 is always filled by the user.
    col BT:CB and col CC:CK is supposed to be filled by the result of the formula applied which will be brought by the code
    Last edited by sumesh56; 04-24-2018 at 01:20 PM.

  34. #34
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    From your post #14:

    but it may be noted that the comparison the code makes is erraneous.
    there will be no comparison on day1 sheet.
    on day2 sheet the comparison can only be done between E and D. see the column heading .there is data only on these two columns at present (after we run the code data from C move to D. then only the comparison should work.)
    It seems pretty clear!

  35. #35
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thanks for the reply. Pls don't be irritated. since my initial post many changes have occured in the original demand. there were ten sheets which have come to two. there has been an evolution of my request, some minor changes.
    at present we have data on column C, so there is a possibility of comparison with col D. at the time of post No.14, the col C was empty. so comparison was not possible at that time........
    anyhow,we have travelled a long way together. we have reached just near the far end of our journey. based upon my last post , please help to resolve the issue.
    Last edited by sumesh56; 04-25-2018 at 10:15 AM.

  36. #36
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Try this - let me know:

    Please Login or Register  to view this content.

  37. #37
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    thanks for the reply.

    when i run the code , it copies the present sheet .but actually the code should bring the result of the formula in certain columns, then after filling all the columns of the sheet only it should be copied to another sheet named reset.

    IN RESET SHEET , i get blank columns in N,W,AQ,AZ,BT,CC

    iN COLUMN N I shluld get the result of the formula If (D2>C2,"Y","NO")
    IN COLUMN W= SUM(D2-C2)
    IN COLUMN AQ= IF (AG2>AF2,"Y" 'N")
    IN COLUMN AZ=SUM(AG2-AF2)
    IN COLUMN BT=IF (BJ2>BI2,"Y" "N")
    IN FOLUMN CC=SUM(BJ2-BI2)

  38. #38
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: macro to move data from one cell to the adjacent,then to the third , then to the fourt

    Hi Sumesh,

    The Reset sheet is just a copy of the day 9 sheet - is the day 9 sheet now incorrect?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. move every fourth line to next column
    By crazyrobin in forum Excel General
    Replies: 3
    Last Post: 05-11-2016, 03:41 AM
  2. Move last word to adjacent cell .
    By Abinash4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2014, 09:32 PM
  3. [SOLVED] Match two celles and fill the third cell with data from the fourth
    By DiskusJon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2012, 01:12 PM
  4. Move Zip code to adjacent cell.
    By smithtec in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2011, 12:05 PM
  5. Move to next cell (not adjacent) for data entry
    By Winon in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-11-2007, 06:48 AM
  6. [SOLVED] tab does not move to adjacent cell in excel
    By Karen Boyd in forum Excel General
    Replies: 1
    Last Post: 09-21-2005, 03:05 PM
  7. excel tab key to move to right adjacent cell
    By dwillie in forum Excel General
    Replies: 2
    Last Post: 01-26-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1