Need a accumulator driven from second cell

[Jay108]

I am new to VBA, just got book yesterday.
Found some code for entering on a column works get all by itself, but does
not help with problem.
Also found code for a single cell accumulator, also work real well all by
itself.
Need to marry these two codes, so that I input on column C and get an
accumulative total on column F. Here are the codes:

Sub ChkColC()
If ActiveCell.Column = 3 Then
If Not IsEmpty(ActiveCell) And ActiveCell <> "" Then
MsgBox "Has Data"
Else
MsgBox "No Data"
End If
End If
End Sub

-AND-

Dim Val
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address = "$F$4" And IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target + Val
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address = "$F$4" And IsNumeric(Target) Then
Application.EnableEvents = False
Val = Target
Application.EnableEvents = True
End If
End Sub

Need help please; thank you,
Jay

 

[Wouter HM]

Hi Jay

Assuming you want to accumulate on the F column try this:

Dim Val(65536) As Double

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 6 And IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target + Val(Target.Row)
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 6 And IsNumeric(Target) Then
Application.EnableEvents = False
Val(Target.Row) = Target
Application.EnableEvents = True
End If
End Sub

HTH,


Wouter

 

[Jay108]

Hello Wouter HM,

That works for F column only; works beautifully by the way; but I'm trying to
get the input from C column with the accumulation in F Column (all in the
same row; so I enter 5 in C4 and in F4, 5 will be added to whatever is in F4
then return the total back to F4)

Thanks,
Jay

 

[Wouter HM]

Hello Wouter HM,

That works for F column only; works beautifully by the way; but I'm trying to
get the input from C column with the accumulation in F Column (all in the
same row; so I enter 5 in C4 and in F4, 5 will be added to whatever is in F4
then return the total back to F4)

Thanks,
Jay

Hi Jay,

Now it was clear to me what you really wanted.
Try this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 3 Then
If IsNumeric(Target) Then
If IsNumeric(Target.Offset(0, 3)) Then
Target.Offset(0, 3).Value = Target.Offset(0, 3).Value +
Target.Value
End If
End If
End If
Application.EnableEvents = True
End Sub

Wouter

 

[Jay108]

Thank you, thank you, many thank you's.
Works exactly like I want; But you have solved a major problem for me.
I'm going to study this book a little harder,
hopefuly I will be able to do stuff like this as well.

Sincerely,
Jay