Solved

change cell value in excel

Posted on 2014-07-22
15
226 Views
Last Modified: 2014-07-28
Hi,

I have this situation :
       A
A1   1
A2   2
A3   3
A4  

In A4 i need to sum above cells, means A4=SUM(A1:A3)
but if i go manually and change the A4 cell value, I need to run a formula (A4=A4*A3)
in other way, I need to have A4 value calulated automacially using a formula, but i change the cell phyiscally i need to it run another formula.
0
Comment
Question by:drtopserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211340
Why would you want to do this?  Normally you would lock your formula cells then have additional ones for user input
0
 

Author Comment

by:drtopserv
ID: 40211364
I just wonder if it possible to do , it will be lots of dozen cells in the same screen and I need to have small looking worksheet.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211389
Anything is possible,  You could trigger on the worksheet_change event and compare the range to see if it is the row and column and if it is no longer a formula then manually set the value or add a different formula on the cell
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:helpfinder
ID: 40211395
if you have formula in A4 you can not insert any other value into the same cell because you will replace formula with value. I do not think it is possible as you describe it.
Maybe using macro, but that is not my playground unfortunatelly
0
 

Author Comment

by:drtopserv
ID: 40211457
Well seems I should use A5 for calculation.
Well, Is it possible then to let user fill only A1:A3 OR A4 , can`t fill both.
Or more to say, if user fill some of the cells A1:A3 I need to reset A4.value to 0 , vise virsa (if A4<>0 then reset A1 TO A3 with 0)
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211461
You can do that via VBA using the worksheet_change event and doing your compare and calculation there.
0
 

Author Comment

by:drtopserv
ID: 40211464
how to?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211500
Enter the VBA editor (ALT-F11)
Select the sheet you want to work with to the left, normally sheet1
then paste in the following code:
Dim inuse As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c, r As Integer
    If Not inuse Then
        inuse = True
        c = Target.Cells.Column
        r = Target.Cells.Row
        'column 1 is A
        If c = 1 Then
            'The change came from A1-A3
            If r > 0 And r < 4 Then
                Me.Cells(4, 1) = 0
            End If
            'the change came from A4
            If r = 4 Then
                Me.Range("A1", "A3") = 0
            End If
        End If
        DoEvents
        inuse = False
    End If
End Sub

Open in new window

0
 

Author Comment

by:drtopserv
ID: 40211576
wow,it`s working, may a small fix , if i need this thing to work the same but for column B and Column F ,, etcc
shoud i copy the code for each cell into the change_event?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211610
That would be the easiest way to handle it.  The If c=1 then to its End If is the code that you need to duplicate per column,  you may want to replace it with select case or modify it so that it calls a sub instead. Depends on your knowledge of VB
0
 

Author Comment

by:drtopserv
ID: 40211633
could you help me in that?, I`ll apprepiate it .
you may give me sample for maybe column 5 and 10 for instance.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211661
Dim inuse As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c, r As Integer
    If Not inuse Then
        inuse = True
        c = Target.Cells.Column
        r = Target.Cells.Row
        'column 1, 5, 10 handle the change
        If c = 1 Or c = 5 Or c = 10 Then
            HandleChange r, c
        End If
        DoEvents
        inuse = False
    End If
End Sub
Private Sub HandleChange(r As Integer, c As Integer)
    'The change came from Row 1-3
    Dim st As String
    If r > 0 And r < 4 Then
        Me.Cells(4, c) = 0
    End If
    'the change came from row 4
    If r = 4 Then
        st = Chr(64 + c)
        Me.Range(st & "1", st & "3") = 0
    End If
End Sub

Open in new window


In this instance if you want to add the same functionality to other columns just append the column to the if statement
If c = 1 Or c = 5 Or c = 10 Then

Open in new window

0
 

Author Comment

by:drtopserv
ID: 40213765
compile error :
ByRef argument type mismatch

in
 HandleChange r, c
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 500 total points
ID: 40214199
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Integer
    Dim r As Integer
    If Not inuse Then
        inuse = True
        c = Target.Cells.Column
        r = Target.Cells.Row
        'column 1, 5, 10 handle the change
        If c = 1 Or c = 5 Or c = 10 Then
            HandleChange r, c
        End If
        DoEvents
        inuse = False
    End If
End Sub
Private Sub HandleChange(r As Integer, c As Integer)
    'The change came from Row 1-3
    Dim st As String
    If r > 0 And r < 4 Then
        Me.Cells(4, c) = 0
    End If
    'the change came from row 4
    If r = 4 Then
        st = Chr(64 + c)
        Me.Range(st & "1", st & "3") = 0
    End If
End Sub

Open in new window

Replace the code with this
0
 

Author Closing Comment

by:drtopserv
ID: 40225935
Randy Poole, Thanks you very much for your help .
it works:}
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question