Find cell - Copy & Paste

[Kell2604]

Hi guys,

I'm trying to get a macro which will find the first cell in column B which
contains #N/A. I have a macro which already does some calculations and sorts
by column B and so puts all of my #N/A's together at the bottom. Also, this
row is always changing, it could be row 110 one day and 127 the next. Once
it finds the first #N/A (in column B row 242 for example) I need it to copy
all of the data in the remaining rows (Row 242 - 263 for example) and paste
into another worksheet.

Hope this makes sense...thanks so much for your help!
Kelley

 

[Bernie Deitrick]

Dim myCell As Range
Set myCell = Range("B:B").Find(What:="#N/A", LookIn:=xlValues)
If myCell Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
If myCell(2, 1).Value <> "" Then
Range(myCell, myCell.End(xlDown)).EntireRow.Copy
Else
myCell.EntireRow.Copy
End If


Then use code to paste in the other workbook....

HTH,
Bernie
MS Excel MVP

 

[Joshua Fandango]

Hi Kell,

This will paste the N/As in cell A1 of Sheet2 (change as necessary)
and with inform you when there are no N/As to be found.

Sub Copy_NAs()
Dim FirstNA As String
On Error GoTo SkipError
FirstNA = Columns("B:B").Find(What:="#N/A",
LookIn:=xlValues).Address
Range(FirstNA, Range(FirstNA).End(xlDown)).Copy Sheets
("Sheet2").Range("A1")
Exit Sub
SkipError:
MsgBox "There are no #N/A values to copy"
End Sub

HtH,
JF

 

[Kell2604]

Thanks so much Bernie. But, I'm getting an error "Type Mismatch" and the
debugger points to this line...

If myCell(2, 1).Value <> "" Then

 

[Bernie Deitrick]

Oops, sorry - forgot that it would be an error value. (That's what I get for just writing code....)
Anyway, use

If myCell(2, 1).Text <> "" Then

HTH,
Bernie
MS Excel MVP

 

[Kell2604]

Great - thanks, that worked!!

Oops, sorry - forgot that it would be an error value. (That's what I get for just writing code....)
Anyway, use

If myCell(2, 1).Text <> "" Then

HTH,
Bernie
MS Excel MVP

 

[Kell2604]

Bernie...I have one last question.

After playing with this I realized that I don't need (want) it to copy the
entire row. I need it to copy the data from colum C over (C, D, E, F, etc.).
Is there a way to work this into your code??

Thanks again!!

 

[Bernie Deitrick]

Change to:

If myCell(2, 1).Text <> "" Then
Range(myCell, myCell.End(xlDown).End(xlToRight)).Copy
Else
Range(myCell, myCell.End(xlToRight)).Copy
End If

This will not work if you have blank cells or columns....

HTH,
Bernie
MS Excel MVP

 

[Kell2604]

I hate to keep bothering you, you have been very helpful. But...this works,
in that it has moved over 1 column (to B) when grabbing the data. But I need
it to get to C and I need it to grab all data from C on. When I run this it
is grabbing column B and 3 other columns. The report has 10 or so additional
columns. (C - AE)

Any thoughts?

 

[Bernie Deitrick]

Kelley,

Try:

If myCell(2, 1).Text <> "" Then
Intersect(Range("C:AE"), Range(myCell, myCell.End(xlDown)).EntireRow).Copy
Else
Intersect(Range("C:AE"), myCell.EntireRow).Copy
End If

And I don't mind being bothered.... ;-)

HTH,
Bernie
MS Excel MVP

 

[Kell2604]

Yeah!!! That did it.

Thank you sooo very much!

Kelley,

Try:

If myCell(2, 1).Text <> "" Then
Intersect(Range("C:AE"), Range(myCell, myCell.End(xlDown)).EntireRow).Copy
Else
Intersect(Range("C:AE"), myCell.EntireRow).Copy
End If

And I don't mind being bothered.... ;-)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Yeah!!! That did it.

Thank you sooo very much!

Glad to hear it - and you're quite welcome!

Bernie
MS Excel MVP