PDA

View Full Version : [SOLVED] Macro copy & paste into new sheet if condition is met?



roxnoxsox
07-23-2015, 06:55 AM
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
07-23-2015, 09:40 AM
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
07-29-2015, 05:53 AM
This doesn't appear to be working ? :/ It copies one or two which say #N/A but doesn't search the entire column?

vcoolio
08-08-2015, 11:49 PM
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
08-09-2015, 06:49 AM
Hi,
Does anyone know how to generate random numbers without repetition in VBA excel.
Thanks
Ali

roxnoxsox
08-15-2015, 07:40 AM
Hi vcoolio,

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

vcoolio
08-16-2015, 01:01 AM
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 :crying:

Cheerio,
vcoolio.