Change sign of cell value based on another cell content

Topic

New Reply

I download financial accounting data from a website weekly. Instead of positive and negative values, the numbers are all positive in a column with another column telling me whether it is an Expense or Income. I transfer this data to my finance spreadsheet for budgeting, cost analysis, etc.
I need to change the Expense values to a negative number based on the word ‘Expense’ in the next column. The number of values downloaded is variable.

Any help?

Reply | Quote

Replies

If your numbers are in column A and the indication is in column B, try this (adjust as needed for where the data resides):

Code:

Sub NegateExp()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
        If Range("B" & i).Value = "Expense" Then
           Range("A" & i).Value = -Range("A" & i)
        End If
    Next i
End Sub

Reply | Quote

Well, that was easy… works great. Thanks much. Seems easy after seeing it…

Reply | Quote

..we can make it more complicated if you want to:

Sort the data so all the Expenses are in one block.
Enter -1 into a spare cell.
Copy this cell with Ctrl-C
Then, select the range of expense values to be converted, and use Paste Special>Multiply
..then re-sort your data back into it’s original order.

zeddy

Reply | Quote

From an accounting perspective you should have two columns and positive numbers – there is no negative money that I know of.

cheers, Paul

Reply | Quote

Hi Paul

..my wallet is full of negative money.

zeddy
(actually, I just have photos of the kids, to remind me where my money used to be)

Reply | Quote