Error when changing the sign on numbers

[tbmarlie]

I'm have the following code which is being run as part of a macro.
I'm trying to change the sign on all of the values in this column so
if its a negative, I want it to be positive and vice versa. When I
run my macro, it gives me a "Run-time error '13': Type Mismatch"
message and when I debug, it highlights the 2nd line below in my
code. Whats confusing to me, though, is that it appears to make all
of the changes (that I intended) to all of the cells in my data down
to my very last row. The cells in this column are formated as type
number. There are null cells in the data which get changed to 0.00,
if that means anything. Thanks.

For k = Cells(Rows.Count, "j").End(xlUp).Row To 1 Step -1
Cells(k, "j").Value = Cells(k, "j").Value * -1
Next k

 

[Guest]

hi tbmarlie

have you ever tried it this way:
For k = 1 to Cells(Rows.Count, 10).End(xlUp).Row
Cells(k, 10).Value = Cells(k, 10).Value * -1
Next k

never tried your approach, which starts from behind!
Maybe it works, who knows.

Carlo

 

[Guest]

Hi,

If all the values are numeric you shouldn't really have a problem. Check to
see what the value of k is when the error returns, and check the value in
that row. You can use this addition to your code which will check the values
for you:

Dim k As Long
For k = Cells(Rows.Count, "j").End(xlUp).Row To 1 Step -1
If IsNumeric(Cells(k, "j")) Then
Cells(k, "j").Value = Cells(k, "j").Value * -1
Else
MsgBox ("non-numeric in row " & k)
End If
Next k


I hope this helps,

Sean.

 

[tbmarlie]

hi tbmarlie

have you ever tried it this way:
For k = 1 to Cells(Rows.Count, 10).End(xlUp).Row
Cells(k, 10).Value = Cells(k, 10).Value * -1
Next k

never tried your approach, which starts from behind!
Maybe it works, who knows.

Carlo






- Show quoted text -

Thanks Sean, That worked. The critical piece that I needed was the
"IsNumeric". I was pretty sure that all of my data was numeric, but
apparently not.

Carlo. Your code worked also (with the "IsNumeric). I just took
someone elses suggestion for starting from the end - I've always
wondered why start at the bottom?

Thanks again.

 

[Guest]

Glad it helped. It's always good to double check things are what you expect,
especially if someone else may be using your code, or amending your
spreadsheets. At some point someone will mess it up!

One reason to start from the bottom is when you are deleting rows. If you
start from the top and are looping through a row counter, you have to
remember not to increment the count if you delete a row. If you start from
the bottom then you never need to remember to do this as you only affect rows
that you've already processed and not the ones above.

There may be other reasons, and sometimes good reasons to start at the top,
but in general a lot of people will work upwards because of this.

Sean.

 

[tbmarlie]

Glad it helped. It's always good to double check things are what you expect,
especially if someone else may be using your code, or amending your
spreadsheets. At some point someone will mess it up!

One reason to start from the bottom is when you are deleting rows. If you
start from the top and are looping through a row counter, you have to
remember not to increment the count if you delete a row. If you start from
the bottom then you never need to remember to do this as you only affect rows
that you've already processed and not the ones above.

There may be other reasons, and sometimes good reasons to start at the top,
but in general a lot of people will work upwards because of this.

Sean.

--
(please remember to click yes if replies you receive are helpful to you)








- Show quoted text -

Sean,

Thanks for the explanation on why to start from the bottom on .

I figured out why it this wasn't working. Because I was starting from
the bottom going to row 1, it would change all of the data and then
bomb out at the end because my row 1 is my header row which is non-
numeric. I redid the code to end at row 2 and excluded the
"IsNumeric" function and it worked.

Thanks again.