+ Reply to Thread
Results 1 to 3 of 3

Macros in excel

  1. #1
    Registered User
    Join Date

    Macros in excel

    I have several data in column A. How can I sort the data that I wanted and paste it to next column? For eg: I have data 1 to 100. I only want the data > 50 and loop & paste them to next column? Please give me simple result. Thanks

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Hello Yana,

    For this example I'll use columns "A" and "B". Column "A" has random numbers in cells A1:A100 with values from 1 to 100. Column "B" will have only the sorted numbers from 51 to 100 in cells B1:B50.

    Sorting the Numbers:
    ActiveSheet.Range("A1:A100").Sort Order1:=xlAscending

    Copying the Data:
    ActiveSheet.Range("A51:A100").Copy Destination:=ActiveSheet.Range("B1")

    Leith Ross

  3. #3
    Forum Guru
    Join Date
    North America
    MS-Off Ver
    2002/XP and 2007
    It depends on exactly what your data set looks like, and how you want the data returned. Here's how I think I would do the example situation:

    Given a list of 100 random numbers from 1 to 100 in column A. I don't know how it applies to your real problem, but I'm going to allow duplicate entries, which means I may get more or fewer than 50 values returned in the final analysis

    I would sort the numbers in descending order rather than ascending order, since I'm interested in numbers >50. That would put the number of interest at the top of the list. At this point, I'm not sure if I'd put it in a macro, or just call the sort command from the menu. It would depend on how frequently I had to do it.

    Then, in column B, I would use the IF worksheet function. B1=IF(A1>50,A1,""). The result would be an ordered list of all the numbers >50 in my list of random numbers. As mentioned, I have allowed for duplicate entries. This also means that column doesn't contain pasted values, but rather formulas that evaluate to the desired value, if you understand the distinction. I don't know if that's allowed or not.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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