MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy numeric value rows and paste to new sheet

  Asked By: Oscar    Date: Dec 21    Category: MS Office    Views: 1177

I am using the following code to copy and paste data from one sheet
to another sheet. My requiremnet is thus as follows:
Want to copy the col C of active sheet to target sheet I want to
copy and paste the entire row for those cells in col C having
numbers only. (To skip those rows which contain 0 or text)

The code is:
Sub test()
Dim r As Range, n As Long
n = 2
With ActiveSheet
For Each r In .Range("c3", Range("c" & Rows.Count).End(xlUp))
If IsNumeric(r.Value) * r.Value <> 0 Then
r.EntireRow.Copy Sheets("stmt").Rows(n)
n = n + 1
End If
End With
End Sub

But the code is not running and showing as error message at this

Run time error 13, Type mismatch
If IsNumeric(r.Value) * r.Value <> 0 Then



1 Answer Found

Answer #1    Answered By: Amy Brown     Answered On: Dec 21

You are using a multiplication of the Booleans for some reason. This means
that both sides of the equation need to be evaluated and you'll get a type
mismatch when you try to check the non-zero'ness of a non-numeric.

Use "and" to combine the two Booleans. It'll stop processing after the
first "false" and won't get to the <> 0 for non-numerics.

In general, performing arithmetic on Boolean values is "a bad thing". There
are times when it is useful for special tricks, but it should not be used as
the normal case.

Didn't find what you were looking for? Find more on Copy numeric value rows and paste to new sheet Or get search suggestion and latest updates.