Solved

change cell value in excel

Posted on 2014-07-22
15
224 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now