Case Statement

[PierreT]

Good morning,

I need help to amend the following code.

What should happen is this: the first thing that I do in my worksheet is to type in the activity name (ie run) in a cell.

Then I type in the distance in the cell below (ie 10).

When both values are typed in, I would like the calculation to take place and display in the cell below.

Currently, when I type in “run” the calculation takes place however, since there are no value in the Target.Offset(1) cell yet, Target.Offset(2) cell displays “0” which is fine, but after entering the distance in Target.Offset(1) cell, the calculation does no longer works and Target.Offset(2) cell remains at 0.

How can I change it so Target.Offset(2) cell gets updated once a value is entered in Target.Offset(1) cell?

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Row = 9 Or 13 Or 21 Or 25 And Target.Column > 1 Then
        Application.EnableEvents = False
        Select Case LCase(Target.Value)
            Case "run"
                Target.Offset(2).Value = Target.Offset(1).Value * 1
            Case "row", "hockey"
                Target.Offset(2).Value = Target.Offset(1).Value * 2
        End Select
        Application.EnableEvents = True
    End If
End Sub

 

[Joe4]

Re: Help with Case Statement

Instead of hard-coding in values, why not populate the cell with a formula instead? Then it will be dynamic as entries change.

        Select Case LCase(Target.Value)
            Case "run"
                Target.Offset(2).FormulaR1C1 = "=R[-1]C*1"
            Case "row", "hockey"
                Target.Offset(2).FormulaR1C1 = "=R[-1]C*2"
        End Select

 

[MickG]

Re: Help with Case Statement

Perhaps this:-
You need to type "run", "row" or Hockey in Rows 9,13,21 or 25

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Row = 10 Or Target.Row = 14 Or Target.Row = 22 Or Target.Row = 26 And Target.Column > 1 Then
     application.EnableEvents = False
        Select Case LCase(Target.Offset(-1).Value)
            Case "run"
                Target.Offset(1).Value = Target.Value * 1
            Case "row", "hockey"
                Target.Offset(1).Value = Target.Value * 2
        End Select
        application.EnableEvents = True
    End If
End Sub

 

[PierreT]

Re: Help with Case Statement

thanks you all, both method works perfectly.

If I may, this spreadsheet is for monitoring how many points a person accumulated while running, playing hockey, rowing... during a month. Is there any way I can capture the value of all the "run" data (as an example) and add them all together to have a grand total display on a other sheet?

 

[Joe4]

Re: Help with Case Statement

Take a look at the SUMIF and SUMIFS formulas.
https://www.exceltrick.com/formulas_macros/excel-sumif-and-sumifs/

 

[PierreT]

Re: Help with Case Statement

got it, thanks for your help, much appreciated.

 

[Joe4]

Re: Help with Case Statement

You are welcome!

 

[Jasmin7]

Re: Help with Case Statement

I don't know how to solve this problem.

 

[Joe4]

Re: Help with Case Statement

I don't know how to solve this problem.

You didn't ask the question (someone else did), and it has already been solved.
So I really don't understand the purpose of your post...