1

enter image description here

How can I make it so that if I enter anything into cell range B3:E3 the value will be whatever I input, plus the value of the above cell.

example: If I input 20 into cell C3, once I hit return or click away the value will be updated to 40.

So far I know I need to have something similar to this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("B3:E3").Value = Range("B2:E2").Value

End Sub

And I know I need to somehow access the offset value, but VBA is not my forte and I cant seem to word this question correctly.

Improve this question
4
  • Is this of any help? – LondonRob May 30 '17 at 21:08
  • no because its updates the cell everytime you click anywhere on the sheet – selectDistinct May 30 '17 at 21:09
  • You can make the event range-specific by doing something like this – LondonRob May 30 '17 at 21:11
  • Rather than changing your question, it might be better for future readers of this question if you left it as it was and asked a new question instead. You can link to this question for reference. (p.s. let's hope the people of Whitby don't mind you sharing their soft drink preferences!) – LondonRob May 30 '17 at 21:32
1

You'll need to add a load of error-checking to this, but here's the bare bones:

Private Sub Worksheet_Change(ByVal Target As Range)

  ' If we're not in the range of interest
  ' do nothing.
  If Intersect(Target, Me.Range("B2:E2")) Is Nothing Then
    Exit Sub
  End If
  ' We don't want this event to fire itself!
  Application.EnableEvents = False
  ' Now add the row above (rowOffset = -1) to the value.
  Target.Value = Target.Value + Target.Offset(-1, 0).Value
  ' Don't forget to re-enable events, otherwise there'll
  ' be no more VBA events fired.
  Application.EnableEvents = True

End Sub
Improve this answer
6
  • This works but if I click around the sheet, the cell in question continues to increment, I want to only increment if the cell in question has been changed – selectDistinct May 30 '17 at 21:13
  • Note that my function is Worksheet_Change not Worksheet_SelectionChange – LondonRob May 30 '17 at 21:16
  • this works, can it be applied to the below rows too? – selectDistinct May 30 '17 at 21:29
  • Just change the value of the row offset from -1 to 1 – LondonRob May 30 '17 at 21:31
  • the first line though, IS NOTHING THEN Exit Sub, means any additional logic below is not run. And I cant duplicate the sub and changed the code as I cant have the same ambigious function – selectDistinct May 30 '17 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.