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
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