PDA

View Full Version : Copy paste data based on criteria in a loop



rhudgins
03-24-2010, 05:39 AM
Column A:Q is how I would like the data copied for each of the 80 data sets that start in column T. I need a macro to loop through each example and copy and paste as values each row from columns T:AE into columns F:Q.

Next here is the copy and paste codeing for the data that does into Columns B:D

(Old Rating Code)
If column Z= "Overweight" then paste "Buy" into column D
If column Z= "neutral" then paste "Neutral" into column D
if column Z= "underweight" then paste "Sell" into column D

(New Rating Code) Next do the same copy and paste procedure for columns Y and C



The next part of codeing relies on a number of if statements

IF column C="Buy" and column D ="Neutral" then paste "Long" into column B
IF column C="Buy" and column D="Sell" then paste ""Long" into column B
IF column C="Sell" and column D="Neutral" then paste "Short" into column B
IF column C="Sell" and column D="Buy" then paste "Short" into column B
IF column C="Neutral" and column D="Buy" then paste "Short" into column B
IF column C= "Neutral" and column D "Sell" then paste "Long" into column B


Next identify the ticker in column F and look up the appropriate basket for that ticker in the Long/Short Baskets Tickers starting in column AK. Then if the code in column B="Long" the paste the basket into column F and call those tickers "Short Basket" in column B. If the code in column B="Short" then paste the basket tickers and call them "Long Basket" into column B.

In column A number each Long/Short followed by Short/Long Basket 1,2,3,4,ect..


Any help with this would be great! I have a lot of pressure on me and I really need this for a work project asap.Thank you so much it is greatly appreciated!

mdmackillop
03-24-2010, 06:38 AM
Welcome to VBAX

This needs more explanation



Next identify the ticker in column F and look up the appropriate basket for that ticker in the Long/Short Baskets Tickers starting in column AK. Then if the code in column B="Long" the paste the basket into column F and call those tickers "Short Basket" in column B. If the code in column B="Short" then paste the basket tickers and call them "Long Basket" into column B.
In column A number each Long/Short followed by Short/Long Basket 1,2,3,4,ect..


Also, you appear to be writing then onerwriting values in column B. If the first written values were preserved in another column, it looks like you can do what you require with nested If statements

=IF(Z9="Overweight","Buy",IF(Z9="Neutral","Neutral",IF(Z9="Underweight","Sell")))

=IF(AND(C9="Buy",D9="Neutral"),"Long",IF(AND(C9="Buy", D9="Sell"),"Long","and so on"))

rhudgins
03-24-2010, 07:03 AM
Thanks you are right, I can used IF Statements for Columns C and D.

But for column B in each example (column A- 1,2,3 ect.) There is a Long or Short code that is derived from the new rating code and old rating code in column C and D. For example 1, B6 says Long so I would like a macro to next identify the ticker(LCC) for the long position in F6 and then go to column AQ and pull the basket of tickers AAI:UAUA and paste those tickers in column F under ticker LCC.

I would like to call those tickers pasted AAI:UAUA "Short Basket" in column B. Essentially for every example the if it is Long then it will be Short Basket and if it is Short then it will be a Long Basket.

The basket sizes are different as you can see in column AK through AX.

Does this make more sense? Thank you so much for your help!!

rhudgins
03-24-2010, 01:06 PM
Can anyone help me with this please?