dolphinideas
07-03-2015, 11:42 AM
I am having trouble performing a certain action in excel 2007 using VBA.
I have several columns containing stock information which I would like to separate by stock ticker. I may have anywhere from 2 to 8 rows of information relating to a single stock ticker.
I would like to copy and paste these values to another part of the spreadsheet. For example I have the following VBA code which uses nested if then statements to compare cells in the AO column for the same stock ticker and copies the correct number of rows to CA4.
When I try to use separate if then statements to compare AN3 to AN4 and copy one row if true, compare AN4 to AN5 and copy one row if true, etc.
Once it compares two cells that don't match it still keeps copying them as if the result was true. I can only get it to work by checking from the bottom, starting with false comparisons and working my way up to end with true comparisons.
Also I would like to be able to copy each stock ticker to a separate area, such as copying the rows for AAPL to CA4, and the rows for AIG to CA37 for example.
Please see the attached excel file for reference.13857
Thank you so much for your help,
Alex.
Sub Macro1()
'
' Macro1 Macro
'
'
If Range("AO3") = Range("AO12") Then
Range("AN3:BE12").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO11") Then
Range("AN3:BE11").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO10") Then
Range("AN3:BE10").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO9") Then
Range("AN3:BE9").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO8") Then
Range("AN3:BE8").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO7") Then
Range("AN3:BE7").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO6") Then
Range("AN3:BE6").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO5") Then
Range("AN3:BE5").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO4") Then
Range("AN3:BE4").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO3") Then
Range("AN3:BE3").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
Else
Selection.Copy
End If
I have several columns containing stock information which I would like to separate by stock ticker. I may have anywhere from 2 to 8 rows of information relating to a single stock ticker.
I would like to copy and paste these values to another part of the spreadsheet. For example I have the following VBA code which uses nested if then statements to compare cells in the AO column for the same stock ticker and copies the correct number of rows to CA4.
When I try to use separate if then statements to compare AN3 to AN4 and copy one row if true, compare AN4 to AN5 and copy one row if true, etc.
Once it compares two cells that don't match it still keeps copying them as if the result was true. I can only get it to work by checking from the bottom, starting with false comparisons and working my way up to end with true comparisons.
Also I would like to be able to copy each stock ticker to a separate area, such as copying the rows for AAPL to CA4, and the rows for AIG to CA37 for example.
Please see the attached excel file for reference.13857
Thank you so much for your help,
Alex.
Sub Macro1()
'
' Macro1 Macro
'
'
If Range("AO3") = Range("AO12") Then
Range("AN3:BE12").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO11") Then
Range("AN3:BE11").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO10") Then
Range("AN3:BE10").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO9") Then
Range("AN3:BE9").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO8") Then
Range("AN3:BE8").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO7") Then
Range("AN3:BE7").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO6") Then
Range("AN3:BE6").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO5") Then
Range("AN3:BE5").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO4") Then
Range("AN3:BE4").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
ElseIf Range("AO3") = Range("AO3") Then
Range("AN3:BE3").Select
Selection.Copy
Range("CA4").Select
ActiveSheet.Paste
Else
Selection.Copy
End If