Solved

change cell value in excel

Posted on 2014-07-22
15
223 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You can do that via VBA using the worksheet_change event and doing your compare and calculation there.
0
 

Author Comment

by:drtopserv
Comment Utility
how to?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
compile error :
ByRef argument type mismatch

in
 HandleChange r, c
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 500 total points
Comment Utility
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
Comment Utility
Randy Poole, Thanks you very much for your help .
it works:}
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now