Case Statement

PierreT

New Member
Joined
Nov 7, 2014
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
Code:
        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
 
Last edited:
Upvote 0
Re: Help with Case Statement

Perhaps this:-
You need to type "run", "row" or Hockey in Rows 9,13,21 or 25
Code:
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
 
Upvote 0
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?
 
Upvote 0
Re: Help with Case Statement

got it, thanks for your help, much appreciated.
 
Upvote 0
Re: Help with Case Statement

You are welcome!
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top