• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

change cell value in excel

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
drtopserv
Asked:
drtopserv
  • 7
  • 7
1 Solution
 
Randy PooleCommented:
Why would you want to do this?  Normally you would lock your formula cells then have additional ones for user input
0
 
drtopservAuthor Commented:
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
 
Randy PooleCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
helpfinderIT ConsultantCommented:
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
 
drtopservAuthor Commented:
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
 
Randy PooleCommented:
You can do that via VBA using the worksheet_change event and doing your compare and calculation there.
0
 
drtopservAuthor Commented:
how to?
0
 
Randy PooleCommented:
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
 
drtopservAuthor Commented:
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
 
Randy PooleCommented:
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
 
drtopservAuthor Commented:
could you help me in that?, I`ll apprepiate it .
you may give me sample for maybe column 5 and 10 for instance.
0
 
Randy PooleCommented:
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
 
drtopservAuthor Commented:
compile error :
ByRef argument type mismatch

in
 HandleChange r, c
0
 
Randy PooleCommented:
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
 
drtopservAuthor Commented:
Randy Poole, Thanks you very much for your help .
it works:}
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now