Macro copy & paste into new sheet if condition is met?

[roxnoxsox]

I have a list of fund names in column A and prices for these in column B (the length of this will vary each day as some are added or removed).

Some fund names will not have a price and will just show: #N/A

I need a macro which will search column B and if it says: #N/A then the corresponding cell in column A and C will be copied and pasted into A1 of sheet2.

Does this make sense at all? Let me know if you need any further clarification at all! Many thanks for any help!! :D

[mperrah]

Something like this might work for you.
If you have headers in Sheet1 and Sheet2 this can modified.
Also not sure if you wanted B copied from Sheet1 or just A and C, this does A:C

Sub findNA()
Dim r, x, lr1, lr2 As Long
Dim vNA
Dim ws1, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row

    For r = 2 To lr1 ' assuming you have headers in row 1 (make 1 to lr1 if no header)
        If ws1.Cells(r, 2).Text = "#N/A" Then
            If ws2.Cells(lr2, 1) = "" Then
            ws1.Range("A" & r & ":C" & r).Copy ws2.Cells(lr2, 1)
            ElseIf ws2.Cells(lr2, 1) <> "" Then  ' not needed if headers on sheet2
            ws1.Range("A" & r & ":C" & r).Copy ws2.Cells(lr2, 1).Offset(1) 
            End If
        End If
    Next r

End Sub

[roxnoxsox]

This doesn't appear to be working ? :/ It copies one or two which say #N/A but doesn't search the entire column?

[vcoolio]

Hello Roxnoxsox,

Does the following help?

Sub CopyIt()


Application.ScreenUpdating = False


Sheet2.UsedRange.Offset(1).ClearContents


With ActiveSheet
    .AutoFilterMode = False
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter 1, "#N/A"
        On Error Resume Next
        .Offset(1).EntireRow.Copy
        Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    .AutoFilterMode = False
End With


Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheet2.Select


End Sub

I've attached my test work book for you to peruse.

I hope that this helps.

Cheerio,
vcoolio.

[alihadi]

Hi,
Does anyone know how to generate random numbers without repetition in VBA excel.
Thanks
Ali

[roxnoxsox]

Hi vcoolio,

This worked perfectly ! And thanks for the reference test workbook; it was very helpful Thank you!

[vcoolio]

Hello Roxnoxsox,

I just found your reply by accident (I didn't receive notification of your reply) but I'm glad that I was able to help.

In mourning now, having lost The Ashes again

Cheerio,
vcoolio.