Editing cells

**smcardle** · 03-14-2010, 07:13 PM

Hi All.

I want to be able to enter a value into a cell (does not matter what value probably an X ) then have the cell display a value based on a calculation of 2 other cells.

i.e. IF this cell is not empty, multiply 2 values and replace the content in this cell with the result.

Any idea how to do this?

I cannot put a formula in the cell directly as when the user enters an X into the cell the formula is errased.

Regards

Steve

**teylyn** · 03-14-2010, 07:23 PM

Hi smcardle,

welcome to the forum.

Excel functions can not replace values in other cells.
Excel cells hold either values or formulas.

What you describe above is not doable with functions. Maybe you can post a sample file and explain what you want to achieve in context.

cheers

**smcardle** · 03-14-2010, 08:01 PM

Hi

I can't attach a worksheet because I can't do what I am trying to do.

Let me see if I can explain it a little better.

I have a range of cells C7 - G10 that I want to apply special handling for.

I only want the user to be able to either clear the cell or place an X in the cell, sort of an active or non-active status.

If the user places an X in the cell, I want to replace the X with a calculated value such as
=C6*B7 and if they clear the cell then just leave it cleared.

I have 2 possible scenarios:
1. Do the calculation on leaving each cell
2. Put a button on the worksheet that does the whole range in one go replacing each cell with an X with a calculated value

Any idea on how I can achieve this?

Regards

Steve

**teylyn** · 03-14-2010, 08:15 PM

Attach a worksheet with what you have so far and then let's take it as a starting point.

So, what formula needs to go into each of the cells in C7 to G10? Is it the same formula for all cells?

It looks as if you'll require a macro, but without more details about the required outcome, it will be hard to write.

**smcardle** · 03-14-2010, 08:41 PM

Hi.

I have attached a work sheet for you.

As you can see the top matrix has a X in each cell I want the user to indicate should have a calculated value.

The bottom matrix shows the desired result where the values are calculated by multiplying the Weight values in the respective row and column.

The Value cells are just a SUM of the relevant row and column.

Hope this helps.

Regards

Steve

**teylyn** · 03-14-2010, 09:06 PM

OK, try this:

the code is

Code:

Option Explicit
Sub CalcX()
    Dim cCell As Range
    For Each cCell In Range("C7:H16")
        If cCell.Value = "x" Then
            cCell.Value = Cells(cCell.Row, 2) * Cells(6, cCell.Column)
        End If
    Next cCell
    
End Sub

and is assigned to a button. See attached. You must have macros enabled.