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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
365, 2019, 2016, 2010
Platform
Windows, 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
23,770

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,502
Messages
5,511,693
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top