| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find, Copy and Paste | Excel Programming | |||
| find, copy and paste | Excel Programming | |||
| Find first empty cell in column J. Copy, paste special, value from | Excel Programming | |||
| Find/Copy/paste.. then Find/Paste - not working ... at all.... | Excel Programming | |||
Linear Mode
