Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

Find cell - Copy & Paste
 

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

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

Find cell - Copy & Paste
 

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

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

Find cell - Copy & Paste
 

Yeah!!! That did it.

Thank you sooo very much!

"Bernie Deitrick" wrote:

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

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

Find cell - Copy & Paste
 

Yeah!!! That did it.

Thank you sooo very much!

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

Bernie
MS Excel MVP