+ Reply to Thread
Results 1 to 3 of 3

Need a query for select & replace the column values in vba

  1. #1
    Registered User
    Join Date
    06-10-2014
    Location
    Chennai
    Posts
    7

    Need a query for select & replace the column values in vba

    Hi All,

    I need to select a range of values of a column from one sheet and the same should be replaced with a set of range from another sheet. Consider I have sheet1 which has 5 set of column a,b,c,d,e and another Sheet Sheet2 with columns a,b,c,d. Now my scenario is i should replace column a of Sheet1 should be replaced with column d of sheet2 & column b of Sheet1 with column a of Sheet2. I have only 15 records in Sheet2.But Sheet1 have nearly 168 columns. I need like if first 15 columns sheet1 is replaced with sheet2 and the same set of values from Sheet2 should repeated for the next set of records in sheet1. Could any1 help on this please.


    Thanks in @dvance,

    K@V!...
    Last edited by kavitha.v; 06-23-2014 at 05:34 AM.
    K@V!....

  2. #2
    Forum Contributor
    Join Date
    02-16-2012
    Location
    London, England
    MS-Off Ver
    Excel mac 2011
    Posts
    238

    Re: Need a query for select & replace the column values in vba

    This is really confusing, I can't work out how many columns you have whether it is 5 or 168. When you say records, what do you mean? do you have 1 row per record, or 1 column per record. Do you need to replace one column at a time, or 15.

    It is quite straight forward to replace or swap data in excel, I tend to create a new sheet, to store temporary data, then it is just a matter of select data and cut and paste. It is often a good idea to set screen updating to false at the beginning and then to true at the end as this speeds up your macro. You can work out how to do this on your own with the record macro button, do what you need to do then stop recording go into VB editor and see what has occured.

    I am suspecting you are after something a little more complex though and without a little more detail, it will be a little hard to help with any conditional criteria or dynamic range selection that you may want.

    Kind regards

    Rob

  3. #3
    Registered User
    Join Date
    06-10-2014
    Location
    Chennai
    Posts
    7

    Re: Need a query for select & replace the column values in vba

    Thanks for ur Reply Rob . I have 5 columns and 168 rows in sheet1 & 4 columns and 15 rows in sheet2. But normal copy paste is a tedious thing. Why should people sit and waste their time to cut copy paste. rather if they have a button just like they can modify it in seconds.

  4. #4
    Forum Contributor
    Join Date
    02-16-2012
    Location
    London, England
    MS-Off Ver
    Excel mac 2011
    Posts
    238

    Re: Need a query for select & replace the column values in vba

    No no, you can do a macro to do all that, here is an example of something I wrote to do just that:
    Please Login or Register  to view this content.
    This macro is cutting and pasting two large blocks from one workbook and then updating another. This is taken from a much larger thing I did and was used in something that was already in a loop, hence the use of variable "i" as a counter. This is really simple if the bits you are copying from and to are set ranges, it is as simple as this. However, I am guessing that there is slightly more to what you are doing. What is the condition that decides what is being copied? And what decides where it goes?

+ 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. Macro to identify column by header and replace number values with string values
    By kang6789 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2013, 05:44 PM
  2. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  3. Select a range named and replace values
    By Kamusksn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-08-2013, 08:22 AM
  4. [SOLVED] Replace values in a selected column with different values
    By Mencae in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-04-2012, 04:28 PM
  5. Replies: 6
    Last Post: 08-18-2006, 10:15 AM

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