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

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


"Kell2604" wrote in message
...
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

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" wrote:

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


"Kell2604" wrote in message
...
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

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" wrote in message
...
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" wrote:

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


"Kell2604" wrote in message
...
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

Great - thanks, that worked!!

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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


"Kell2604" wrote in message
...
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...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!!

"Kell2604" wrote:

Great - thanks, that worked!!

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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


"Kell2604" wrote in message
...
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

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


On 16 Dec, 15:29, Kell2604 wrote:
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