VBA, copy lines with specific value
 

Hi All,

From an investment portfolio I am trying to exclude a certain asset
class (swaps)
My idea was to put a simple =IF() statement in column A that
identifies if it concerns a plain asset (result=0) or a swap asset
(result=1)
I defined the below VBA statement but when I run it all formulas
(=IF()) are deleted from column A and the results are paste as values.

'Exclude all Swaps
Dim iLastRow As Long, iNextRow As Long
Dim i As Long
With Worksheets("Positions")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i, "A").Value = "0"
iNextRow = iNextRow + 1
.Cells(i, "A").Resize(, 21).Copy _
Worksheets("Cut-Out").Cells(iNextRow, "A")
End If
Next i

End With


Can someone tell me what is wrong with this statement and how to
adjust it in order not delete the formulas in column A?

Many thanks!!!!!

Rgds,
Robert

VBA, copy lines with specific value
 

Hi Robert

You have an end if statement at the end of the code, but I can not see any
IF statement.

After the FOR statement I think you are looking for this:

If Cells(i, "A").Value = "0" then

Regards,

Per
"Robert" skrev i en meddelelse
...
Hi All,

From an investment portfolio I am trying to exclude a certain asset
class (swaps)
My idea was to put a simple =IF() statement in column A that
identifies if it concerns a plain asset (result=0) or a swap asset
(result=1)
I defined the below VBA statement but when I run it all formulas
(=IF()) are deleted from column A and the results are paste as values.

'Exclude all Swaps
Dim iLastRow As Long, iNextRow As Long
Dim i As Long
With Worksheets("Positions")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i, "A").Value = "0"
iNextRow = iNextRow + 1
.Cells(i, "A").Resize(, 21).Copy _
Worksheets("Cut-Out").Cells(iNextRow, "A")
End If
Next i

End With


Can someone tell me what is wrong with this statement and how to
adjust it in order not delete the formulas in column A?

Many thanks!!!!!

Rgds,
Robert