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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,031
Office Version
  1. 365
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
 

Thorin

Board Regular
Joined
Feb 8, 2007
Messages
243
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.
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,448
Members
425,547
Latest member
Iano

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
Top