Can't set macro range in VBA

[Bobba the Fat]

Hello.

I need my macro to automatically multiply the value I enter in the cell (by *9.81) and to return the result to the same cell. I have gotten this code, but I can't set the Range of my macro to work in. I would like this macro to work just in columns BH:BJ, BM:BO, BR:BT, BW:BY, CB:CD, CG:CI, CL:CN and CQ:CS.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "" Then
Application.EnableEvents = False
Target.Value = Target.Value * 9.81
End If
errhandler:
Application.EnableEvents = True
End Sub

Thank you for your cooperation!
May the force be with you

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, e As Range
Set d = Union(Range("BH:BJ"), Range("BM:BO"), Range("BR:BT"), Range("BW:BY"), Range("CB:CD"), Range("CG:CI"), Range("CL:CN"), Range("CQ:CS"))
Set e = Intersect(Target, d)
If e Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In e
    If c <> "" And IsNumeric(c) Then c = c * 9.81
Next
Application.EnableEvents = True
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BH:BJ,BM:BO,BR:BT,BW:BY,CB:CD,CG:CI,CL:CN,CQ:CS")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * 9.81
Application.EnableEvents = True
End If
End Sub

 

[snjpverma]

Scott would you please explain how this code works? I m a beginner in vba

 

[Scott Huish]

It assigns multiple ranges to one range object.
It then checks to see if Target intersects with any of the cells in the range stored in variable d.
If it returns Nothing the values weren't in the range, and then exits the sub
It then loops through every cell that was in your range, this would allow you to say copy a value into multiple cells and still have it function.
If the value is numeric and not blank it multiples that value by 9.81

 

[snjpverma]

thanks Scott

 

[Bobba the Fat]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BH:BJ,BM:BO,BR:BT,BW:BY,CB:CD,CG:CI,CL:CN,CQ:CS")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * 9.81
Application.EnableEvents = True
End If
End Sub

Thank you, it works perfectly!
All the best

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you, it works perfectly!
All the best