changing - to + within text in cell

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hye there,

i data within an export from MS QuickBooks that has negative values within a cell. the format of the cell is Custom (#,##0.00###;-#,##0.00###).

what wold be the best VB code to use to change all of the values within the cells from neg to positive? they alway reside in column K.

Thanks
TukTuk
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
What happens with positive values? Do they become negative (e.g. *everything* gets multiplied by -1)? Or do they stay positive (e.g. take the absolute value of each value)?

edit: fixed typo
 
Upvote 0
The easiest way would be to type -1 in a blank cell, copy it and then paste special it over your range selecting multiply. Then you can delete the -1 as it is no longer needed.
 
Upvote 0
Using code if you really want:

Code:
Sub test()

Dim myRange As Range

For Each myRange In Range([K2], [K65536].End(xlUp))

If myRange.Value < 0 Then myRange.Value = myRange.Value * -1

Next myRange

End Sub

Dom
 
Upvote 0
actually there will not be any positive values within the column......so it appears that everything can be multiplied by -1.

the number of rows from K2 to lastrow will be changing everytime i run the macro..


i was thinking of usng the modifying the following code but would need a different method that ".filldown"......so that is applies the *(-1) to all of the necessary data in column K.

If Range(Cells(2, "K"), Cells(Cells(Rows.Count, "N").End(xlUp).Row, "K")).Rows.Count = 1 Then Exit Sub

does that make sense?
Range(Cells(2, "K"), Cells(Cells(Rows.Count, "N").End(xlUp).Row, "K")).FillDown
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top