Possible or Impossible excel question w/macro

[juvio]

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.

 

[Zaphodski]

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

 

[DonkeyOte]

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

 

[straus]

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

 

[Zaphodski]

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

 

[DonkeyOte]

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.

 

[straus]

yep...but i still think, the safest way is to copy and paste the data manually . ( depending on the number of columns