create 'Once only' functions not formulas

barton

New Member
Joined
Jul 22, 2010
Messages
3
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try a macro

Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here's an example:

Code:
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

New Member
Joined
May 25, 2010
Messages
21
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. Web
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,013

ADVERTISEMENT

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

New Member
Joined
Jul 22, 2010
Messages
3
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

Legend
Joined
Jun 19, 2002
Messages
63,650
You would need something like

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

Watch MrExcel Video

Forum statistics

Threads
1,132,679
Messages
5,654,714
Members
418,149
Latest member
tjanok

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