Solved

change cell value in excel

Posted on 2014-07-22
15
225 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Make a Cell act like a Date 7 39
VBA Works in Excel 2010 Not 2016 Help! 5 18
Excel Drop Down List 13 32
excel formula to sum column 13 12
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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