create 'Once only' functions not formulas

[barton]

OK, im not sure if excel is suitable for this, but i have a lot of data i need to format and then save as a csv.

What i need to do is pretty simple stuff, but i cant work out how to do it without creating extra columns with formulas.

Say i have 2 columns of data, A and B, and i want to subtract the value of A from B, so if to start with A contains the value 10 and B 30, afterwords B would then contain the value 20

I can start a new column C with =B1-A1 but i DONT want a new column with dynamic formula driven data, i just want to alter the data in column B!

This seems like such a simple thing to do, but i cant work it out.

Any help very much appreciated.

 

[VoG]

Try a macro

Sub test()
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        .Value = .Value - .Offset(, -1).Value
    End With
Next i
End Sub

 

[Andrew Poulsom]

Here's an example:

Sub Test()
    Dim Cell As Range
    For Each Cell In ActiveSheet.Range("A1").CurrentRegion.Columns(1).Cells
        Cell.Offset(, 1).Value = Cell.Value - Cell.Offset(, 1).Value
    Next Cell
End Sub

Make sure that you only run it once!

 

[maninweb]

Hi barton...

you can create the formula in column C (C1 = B1 - A1, Autofill), then copy the values
from column C and paste them by using "Paste special" and only insert the calculated
values in Column B. Then delete the formulas in Column C. Hope this helps.

Regards

 

[mikerickson]

Excel formulas don't do that.
A formula in a cell can't change the value of any cell.
Excel doesn't "put values" in cells, it "gets values" from cells, calculates with those values and shows the result.

Either VBA or a re-think of the plan of the spreadsheet are needed.

 

[barton]

Yeah im starting to think excel just isnt plain designed for what im trying to acheive, perhaps i should look at a database program.

However macros are very powerful (Thanks for the examples Vog & Andrew), and im so close to doing what i need to do.

Is it possible to delete all cells containing a negative value?
I have managed to do exactly what i want, but i do not need to store any values less than 0, so would like to just delete those values completely

A search and replace might do, but it doesnt seem ideal.

I have used this to delete empty cells:

Range("A1:F100").SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft

This obviously uses the 'identifier' (i do not speak VB sorry) xlCellTypeBlanks to find and delete the cells, could this be modified to find and delete cells with negative numbers?

Thanks

 

[VoG]

You would need something like

Dim c As Range
For Each c In Range("A1:F100")
    If c.Value < 0 Then c.Delete shift:=xlShiftToLeft
Next c

 

[barton]

Excellent thanks alot, im well on my way.