PDA

View Full Version : Update two cells by changing another



nsukari
11-17-2008, 01:51 PM
I am attempting to update three columns of data by entering information into one. If they enter the information into either column, the other two should calculate automatically. The code below works, but only if they enter the information into each cell. I want the user to be able to enter the information by copying and pasting from another sheet or doing a fill down. Basically, if anything about the cell changes, I want to calculate. I am not familiar with VB, so I hope this is an easy fix.

Here's my code.

Private Sub Salary_Update(ByVal Target As Excel.Range)
Dim nCol As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("F6:I1000")) Is Nothing Then
On Error GoTo ErrorOrExit
Application.EnableEvents = False
nCol = .Column
With Cells(.Row, 1).Resize(1, 4)
Select Case nCol
Case 2
.Cells(3).Value = .Cells(1).Value * .Cells(2).Value
.Cells(4).Value = .Cells(1).Value + .Cells(3).Value
Case 3
.Cells(2).Value = .Cells(3).Value / .Cells(1).Value
.Cells(4).Value = .Cells(1).Value + .Cells(3).Value
Case 4
.Cells(3).Value = .Cells(4).Value - .Cells(1).Value
.Cells(2).Value = .Cells(3).Value / .Cells(1).Value
End Select
End With
ErrorOrExit:
Application.EnableEvents = True
End If
End With
End Sub

MaximS
11-17-2008, 02:52 PM
can you please paste sample workbook as the code needs to be written again to do the job.

code you currently have will only work when you change one cell at the time.

nsukari
11-17-2008, 02:57 PM
Here's my workbook

Thanks!

Bob Phillips
11-17-2008, 03:12 PM
I played about with your code, but I don't think I changed it much.

This code, in the worksheet code module, seems to work fine



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nCol As Long

If Target.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("G6:I1000")) Is Nothing Then Exit Sub

On Error GoTo ErrorOrExit
Application.EnableEvents = False
nCol = Target.Column
With Me.Cells(Target.Row, 1).Resize(1, 9)

Select Case nCol

Case 7
.Cells(8).Value = .Cells(3).Value * .Cells(7).Value
.Cells(9).Value = .Cells(3).Value + .Cells(8).Value

Case 8
.Cells(7).Value = .Cells(8).Value / .Cells(3).Value
.Cells(9).Value = .Cells(3).Value + .Cells(8).Value

Case 9
.Cells(8).Value = .Cells(9).Value - .Cells(3).Value
.Cells(7).Value = .Cells(8).Value / .Cells(3).Value
End Select
End With

ErrorOrExit:
Application.EnableEvents = True
End Sub

MaximS
11-17-2008, 03:26 PM
I don't think you really need any macro code to archive your goal.

simple formula will do the job.

see attachment for details and correct me if you wish to archive something else.

nsukari
11-17-2008, 03:29 PM
Thanks you, but the formulas won't work because I need to have the ability to enter a percentage, the total increase, or the total new salary. I have the macro working in another file as it is, with the ability to fill down and copy and paste, I just can't figure out why it is not working now. I copied the code from the other workbook.

MaximS
11-17-2008, 05:01 PM
Yes you were right about formulas, they won't work for you.

You can try small macro wich will match names and then cooperate with your code.

See "Data" Sheet for details.