Excel VBA - Type mismatch during While
On a worksheet with 4 columns and ~300 rows, where the 4th column is th
same formula in each row, returning a value for the first ~10-20, want to paste the contents of the 1st column into the 5th column of th same row, so long as the result of the formula in column 4 is no "#N/A" What I have looks like this: While ActiveCell < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend This works well for adding the 5th column, but errors out as a Typ Mismatch when it reaches the first row where the result in column 4 i #N/A. Any help is greatly appreciate -- Message posted from http://www.ExcelForum.com |
Excel VBA - Type mismatch during While
While ActiveCell.Text < "#N/A"
ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend -- Regards, Tom Ogilvy "sub_pop " wrote in message ... On a worksheet with 4 columns and ~300 rows, where the 4th column is the same formula in each row, returning a value for the first ~10-20, I want to paste the contents of the 1st column into the 5th column of the same row, so long as the result of the formula in column 4 is not "#N/A" What I have looks like this: While ActiveCell < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend This works well for adding the 5th column, but errors out as a Type Mismatch when it reaches the first row where the result in column 4 is #N/A. Any help is greatly appreciated --- Message posted from http://www.ExcelForum.com/ |
Excel VBA - Type mismatch during While
Thanks Tom,
Unfortunately, even when the result - #N/A, condition is met and th copy and paste take place. Is there an ActiveCell.Result? Thanks, Marc Tom Ogilvy wrote[/i] *While ActiveCell.Text < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend -- Regards, Tom Ogilvy "sub_pop " wrote in message ... On a worksheet with 4 columns and ~300 rows, where the 4th column i the same formula in each row, returning a value for the first ~10-20, I want to paste the contents of the 1st column into the 5th column o the same row, so long as the result of the formula in column 4 is not "#N/A" What I have looks like this: While ActiveCell < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend This works well for adding the 5th column, but errors out as a Type Mismatch when it reaches the first row where the result in column is #N/A. Any help is greatly appreciated --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
Excel VBA - Type mismatch during While
What did work is this:
While ActiveCell = ActiveCell.Offset(0, -2) On Error GoTo Sort ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend Sort: Columns("A:E").Select Selection.Sort Key1:=Range("E1") Is this a problem? Is there some reason it should not be used? Should move Sort: past End? Thanks, Mar -- Message posted from http://www.ExcelForum.com |
Excel VBA - Type mismatch during While
Code worked fine for me as I changed it.
While ActiveCell.Text < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend Apparently there is something else wrong with your worksheet. -- Regards, Tom Ogilvy "sub_pop " wrote in message ... Thanks Tom, Unfortunately, even when the result - #N/A, condition is met and the copy and paste take place. Is there an ActiveCell.Result? Thanks, Marc Tom Ogilvy wrote *While ActiveCell.Text < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend -- Regards, Tom Ogilvy "sub_pop " wrote in message ... On a worksheet with 4 columns and ~300 rows, where the 4th column is the same formula in each row, returning a value for the first ~10-20, I want to paste the contents of the 1st column into the 5th column of the same row, so long as the result of the formula in column 4 is not "#N/A" What I have looks like this: While ActiveCell < "#N/A" ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend This works well for adding the 5th column, but errors out as a Type Mismatch when it reaches the first row where the result in column 4 is #N/A. Any help is greatly appreciated --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ [/i] |
Excel VBA - Type mismatch during While
If that works for you use it.
As I said, the previous code, as I changed it, worked fine for me. -- Regards, Tom Ogilvy "sub_pop " wrote in message ... What did work is this: While ActiveCell = ActiveCell.Offset(0, -2) On Error GoTo Sort ActiveCell.Offset(0, -3).Select ActiveCell.Copy ActiveCell.Offset(0, 4).Select ActiveCell.PasteSpecial ActiveCell.Offset(1, -1).Select Wend Sort: Columns("A:E").Select Selection.Sort Key1:=Range("E1") Is this a problem? Is there some reason it should not be used? Should I move Sort: past End? Thanks, Marc --- Message posted from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 06:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com