Results 1 to 7 of 7

Possible or Impossible excel question w/macro

This is a discussion on Possible or Impossible excel question w/macro within the Excel Questions forums, part of the Question Forums category; Here's the scenario. I have a list of about 70-80 different investment funds located in column A. Column B is ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Location
    San Diego, CA
    Posts
    4

    Default

    Here's the scenario.
    I have a list of about 70-80 different investment funds located in column A. Column B is empty. Column C will have some 15 or so investment funds, all of which will also be located in column A. the column next to Column C will have a dollar amount that corresponds to the investment account name in column c. Additional columns to the right of column c will demonstrate the same concept, just additional fund names with investment amounts. The objective was to set up a macro that by using the search and find functions, of the edit menu would allow me to search column c for each of the names of the funds located in column a. When one of the funds was not found, then skip that and go to trying to find the next fund. Then move the amount column and copy that value to the clipboard. then move to column a and do the same search and find function thereby finding the named fund, move the curso to column b and paste the value into that location.

    If anyone can get back to me on this, I would really appreciate it.

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Posts
    86

    Default

    It's a LOT easier if you could put all the source fund names in one column - say C, and values in D. IS that a possibility?

    Do I get you right: you will select a portfolio for study in column A, and the rest of the sheet provides reference data listing all the funds you know and their current(?) value / what you have invested in them / whatever. You then want to run the macro and it will hunt through the reference material and assign values from it to the corresponding funds in A. These values will go into B.

    If you do have to use several columns for reference, so long as there are no duplicate values you could use a simple but ugly series of SUMIF formulae:

    in cell B3:

    =SUMIF(C1:C100,A3,D1:D100)+SUMIF(E1:E100,A3,F1:F100)+.....etc

    Z

  3. #3
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default

    You need to run a double loop

    eg

    Sub DATALOOP()

    Application.ScreenUpdating = False

    DATA = Range("A1:A200")
    CROW = 1

    For Each LDATA In DATA

    If LDATA = "" Then Exit For

    Data2 = Range("C1:C50")
    CROW2 = 1

    For Each LDATA2 In Data2

    If LDATA <> LDATA2 Then
    CROW2 = CROW2 + 1
    GoTo 100
    End If

    If LDATA = LDATA2 Then Range("B" & CROW) = Range("D" & CROW2)

    100

    Next LDATA2

    CROW = CROW + 1

    Next LDATA

    Application.ScreenUpdating = True

    End Sub


    That should do the trick.

    Luke
    Does my a$$ look big in this picture ?

  4. #4
    Board Regular
    Join Date
    Oct 2002
    Posts
    192

    Default

    yep...i agree with lasw10. it does the trick for sure

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    86

    Default

    I agree too, but for this to work comprehensively you need to have all your reference funds in Column C and all the values in D - like I first suggested. I read your original to mean you would have references in C1:D15 & E1:F15 & G1:H15 etc. You'll need a third loop in the code offered to calculate all this.

    Z

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default

    Didn't see that - easiest thing as someone suggested would be to simply copy and paste data from E&F, G&H to the next blank cell in C etc... this can be automated at the beginning of the macro (though you may need to adjust the range of Data2)

    i.e

    Range("E1:F15").Copy
    Range("C16").PasteSpecial xlPasteValues

    Range("G1:H15").Copy
    Range("C31").PasteSpecial xlPasteValues

    then remainder of code.
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Oct 2002
    Posts
    192

    Default

    yep...but i still think, the safest way is to copy and paste the data manually . ( depending on the number of columns
    Confucius says: Browse through the threads of Mr. Excel. You'll learn much more than you think you know

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com