how to write a macro for a specific range?

[PierreT]

First off all, hello everyone! Secondly I'm new to formula and macro so please don't shoot the messenger.
I'm trying to build a calendar which keep track of my physical fitness activities.

In column A I have, Activity(s) (A9), Distance (A10), and Points (A11).
When I type in the word "run" in cell H9 for example, I have the Distance Cell (H10)'s value multiple by 1 which gets inserted in the Points Cell (A11), if I have the word "row" typed in, it multiples the Distance Cell (H10) value by 2 and so on... as such:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("h9").Value = "run" Then
Range("h11") = Range("h10") * 1
ElseIf Range("h9").Value = "row" Then
Range("h11") = Range("h10") * 2
ElseIf Range("h9").Value = "hockey" Then
Range("h11") = Range("h10") * 2
End If
End Sub

Now that works fine for H9, H10 and H11. How to make the formula work for all the other day (G,H,I...) within the calendar without typing the code for every single day?

If I could also get the 'explanation that would be much appreciated.

That is the first question! One more to come shortly.

Thank you.

 

[Norie]

So you would want the code to work if a value is entered in row 9 from column G onwards?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 9 And Target.Column > 6 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

 

[PierreT]

the code provided works if the Target.Offset(1).value is typed in before the any of the Case, otherwise the calculation does not happen. In other word, on my excel sheet, the "Distance" (A10) must be type in before the "Activity(s)" (A9) in order for the calculation to work, otherwise "Points" (A11) stay at 0.

Any idea?

Thank you so much for the help

 

[Norie]

Where are you actually entering values?

A9? G9? A10? G10?

When would you want the calculation to happen?

Only when the activity and distance are entered?

 

[PierreT]

I type in the activities in A9, ie run.
Then I type in the distance in A10, ie 10, A10 value is then multiple by 1.
Then the points get inserted in A11.

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

Thanks

 

[Norie]

So where do H9, H10 and H11, as mentioned in the original post, come into things?

 

[PierreT]

Ok, I will get the app so I can post a quick picture of my work but in the mean time, I will try to explain a little better, I'm sorry.

A9 = "Activity" B9 to O9 = empty cells where I enter the name of the activity "run", "row' or "hockey"
A10 = "Distance" B10 to O10 = empty cells where I enter the distance achieved, ie 10 for 10 kilometer
A11 = "Points" B11 to O11 = is when B10 to O10 multiply themselves by the value given in VBA, ie B10 * 1 on so on

A13 = "Activity" B13 to O13 = empty cells where I enter the name of the activity "run", "row' or "hockey"
A14 = "Distance" B14 to O14 = empty cells where I enter the distance achieved, ie 10 for 10 kilometer
A15 = "Points" B15 to O15 = is when B14 to O14 multiply themselves by the value given in VBA, ie B14 * 1 on so on

all the way to A25, A26, A27.

At the moment, if I type in "run" in O9 for example, then I type in "10" in O10, the calculation (O10 * 1) does not occur and does not show up in O11, it shows "0". However, if I type in "10" in O10 first, then type in "run" in O9, the calculation (O10 * 1) does work and does show up in O11.

So what I would like to happen is to type in "run" in O9 first, then "10" in O10 and having O11 showing the result.

I'm sorry if this is confusing.

 

[Norie]

Actually it's not that confusing, well apart from the previous post that seemed to be implying you were entering the activities and distances in column A.

I'm not at a computer right now but I think what I originally posted should work, after a bit of tweaking anyway.

I'll post back later.

 

[PierreT]

absolutely, than you very much for all the helps.
Cheers.

 

[PierreT]

Sorry to bother you Norie, but did you have a chance to look at the code and figure it out. Also, could you please explain it to me so I can learn from it.
Thanks