How to get multiple cells to multiply different figures

[Shesmi130]

I have used the formula below to get a specific set of cells to produce a multiplied figure, however it will only work for one row. Each time I change the details in the formula to include another row it deletes the info that was added for the previous row. Is there a way to use the formula on multiple rows that have different values? For example, I need C3:I3 to multiply by 10 but I need C5:I5 to multiply by 18.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const SET_VALUE As Long = 23 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsNumeric(.Value) Then .Value = .Value * SET_VALUE
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Joe4]

Welcome to the Board!

Just add a second block that emulates the first, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const WS_RANGE1 As String = "C3:I3"
    Const SET_VALUE1 As Long = 10
    Const WS_RANGE2 As String = "C5:I5"
    Const SET_VALUE2 As Long = 18

    On Error GoTo ws_exit
    Application.EnableEvents = False

'   Check first range
    If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
        With Target
            If IsNumeric(.Value) Then .Value = .Value * SET_VALUE1
        End With
    End If

'   Check second range
    If Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
        With Target
            If IsNumeric(.Value) Then .Value = .Value * SET_VALUE2
        End With
    End If

ws_exit:
    Application.EnableEvents = True
    
End Sub

 

[Shesmi130]

Thank you for the suggestion! I tried entering the info you provided but I received a syntax error. I then decided to omit the "check first range" and "check second range" phrases but it still didn't work.

 

[Joe4]

There are no syntax error in my code. I checked and ran it successfully.
Did you copy it "as-is", or try typing or changing anything.

 

[Shesmi130]

I copied from the code I had in already. Then I just added the number. 1,2,3 and so on. The syntax error said it was on the "check first range" phrase

 

[Joe4]

The problem is probably with the changes you made.
Please post your code that is returning the error, EXACTLY as you currently have it.

 

[Shesmi130]

After copying and pasting your work it worked fine. I Tgen tried to duplicate the wording ave adding a 3rd range. I'm getting the syntax error. Its highlighting the "Check third range" phrase. I'll see if I can post a pic.

 

[Shesmi130]

Scratch that last response. I realized I had left out the apostrophe. I've added that in and I'm no longer getting a syntax error, however, it's just not calculating.

 

[Shesmi130]

Ok! Scratch both of the last 2 responses! I finally got it to work properly!! Yay!! Now, on 3 of the fields the value is 1.5. This value is being rounded to the next whole number. Id's there a way to make it calculate at the 1.5?

 

[mtugahan]

Hi! Can you help me in creating a drop down list that can select multiple options. I got a code that can select multiple options but it is separated with comma. My client wants it separate in different cell under the same column. Please I need some help with this. Thank you in advance.