PDA

View Full Version : Solved: Using ElseIF



Klartigue
06-06-2012, 08:42 AM
Below is the general idea of what I want to happen, althought this expression does not work because I am not sure how to incorporate the ElseIf function.

Sub FIDO()
' Copy and paste fidelity allocations into new sheet in workbook

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "X").Value = "FIDELITY" Then

ElseIf .Cells(i, "X").Value = "BANK OF NEW YORK MELLON (BONY)" Then

With Range(.Cells(i, "A"), .Cells(i, "X")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With

End Sub

Right now this expression only copies the row that has "Bank of New York Mellon" and pastes it into sheet 1. How do I get it to copy the rows with Fidelity and the rows with Bank of New York Mellon and paste those all in sheet1?

Opv
06-06-2012, 08:49 AM
Below is the general idea of what I want to happen, althought this expression does not work because I am not sure how to incorporate the ElseIf function.

Sub FIDO()
' Copy and paste fidelity allocations into new sheet in workbook

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "X").Value = "FIDELITY" Then

ElseIf .Cells(i, "X").Value = "BANK OF NEW YORK MELLON (BONY)" Then

With Range(.Cells(i, "A"), .Cells(i, "X")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With

End Sub
Right now this expression only copies the row that has "Bank of New York Mellon" and pastes it into sheet 1. How do I get it to copy the rows with Fidelity and the rows with Bank of New York Mellon and paste those all in sheet1?

The code currenly has no instruction to do anything after the original IF statement, so it currently proceeds directly to the ElseIf condition.

Opv
06-06-2012, 08:52 AM
If you want the same thing to occur if both conditions are true you could eliminate the ElseIf line and just use:


If .Cells(i, "X").Value = "FIDELITY" Or _
.Cells(i, "X").Value = "BANK OF NEW YORK MELLON (BONY)" Then

Klartigue
06-06-2012, 08:55 AM
Thats works great, thank you!!!

Sub FIDO()
' Copy and paste fidelity allocations into new sheet in workbook

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "W").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "W").Value = "FIDELITY" Or _
.Cells(i, "W").Value = "MERRILL LYNCH" Then

With Range(.Cells(i, "A"), .Cells(i, "X")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With

End Sub