Excel VBA nested if statements

[dolphinideas]

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

[Aussiebear]

Try the following,



Sub Macro1()
If Range("AO3").value= Range("AO12").value Then
Range("AN3:BE12").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO11").value Then
Range("AN3:BE11").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO10").value Then
Range("AN3:BE10").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO9").value Then
Range("AN3:BE9").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO8").value Then
Range("AN3:BE8").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO7").value Then
Range("AN3:BE7").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO6").value Then
Range("AN3:BE6").Copy
Range("CA4").Paste

ElseIf Range("AO3").value= Range("AO5").value Then
Range("AN3:BE5").Copy
Range("CA4").Paste

ElseIf Range("AO3").value = Range("AO4").value Then
Range("AN3:BE4").Copy
Range("CA4").Paste

Else
Range("AN3:BE3").Copy
Range("CA4").Paste
End If

[SamT]

AussieBear,

Bump me so I can come back to this. Thanks

[dolphinideas]

AussieBear,

Your code works when I replace:
Range("CA4").Paste with:

Range("CA4").Select
ActiveSheet.Paste
What I would like to do is to copy each group of rows with the same stock ticker to a separate range, such as copying the rows for AAPL to CA4, the rows for AIG to CA37, the rows for APA to CA69 and so on. How can I do this?

Thanks,

Alex

[Paul_Hossler]

Can you post a small workbook with a sample before and after?

[SamT]

Try this. To use, doubleclick any Sticker in column AO that you want to copy all of.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(StickerColumn, Target) Is Nothing And Target.Row >= 3 _
Then CopyBySticker Target
End Sub

Private Sub CopyBySticker(Cel As Range)
Dim LR As Long 'Used when pasting
Dim BR As Long 'Used when Copying
Dim TopSticker As Range

LR = Cells(Rows.Count, "CA").End(xlUp).Row
If LR < 3 Then
LR = 3
Else: LR = LR + 2
End If

Set TopTopSticker = Range("AO:AO").Find(Cel.Value, After:=Range("AO1"), _
SearchDirection:=xlNext).Offset(0, -1)

BR = Range("AO:AO").Find(Cel.Value, After:=Range("AO1"), _
SearchDirection:=xlPrevious).Row

Range(TopSticker, Range("BE" & BR)).Copy Range("CA" & LR)

End Sub