Excel VBA - Type mismatch during While

S

sub_pop

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
 
T

Tom Ogilvy

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
 
S

sub_pop

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
 
S

sub_pop

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
 
T

Tom Ogilvy

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 > said:
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[/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 > said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

If that works for you use it.

As I said, the previous code, as I changed it, worked fine for me.
 
Top