Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when changing the sign on numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when changing the sign on numbers
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 "tbmarlie" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when changing the sign on numbers
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. -- (please remember to click yes if replies you receive are helpful to you) "tbmarlie" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when changing the sign on numbers
On Oct 25, 6:23 pm, Carlo wrote:
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 "tbmarlie" wrote: 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- Hide quoted text - - 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when changing the sign on numbers
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) "tbmarlie" wrote: On Oct 25, 6:23 pm, Carlo wrote: 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 "tbmarlie" wrote: 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- Hide quoted text - - 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when changing the sign on numbers
On Oct 26, 8:36 am, SeanC UK
wrote: 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) "tbmarlie" wrote: On Oct 25, 6:23 pm, Carlo wrote: 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 "tbmarlie" wrote: 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- Hide quoted text - - 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.- Hide quoted text - - 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with changing cell contents with $ sign | Excel Worksheet Functions | |||
Changing the Negative Sign | Excel Programming | |||
+ sign changing to - in Excel 2000???? | Excel Discussion (Misc queries) | |||
changing sign | Excel Worksheet Functions | |||
Changing the sign of cell using a macro | Excel Programming |