Solved

Adding to a VBA?

Posted on 2016-10-12
6
82 Views
Last Modified: 2016-10-12
I have the following VBA that I want to add the exact same execution to, for cells B23 through O34. I am a complete newbie rookie to doing anything with these types of things ... but I do know the below works, and I just want to know how to add these cells to the same operation. Can you tell me how to it edit? Thanks so much in advance!:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
        On Error GoTo FallThrough
        Application.EnableEvents = False
        Dim a As Range
        For Each a In Intersect(Target, Range("A7:O34"))
            If IsNumeric(a.Value) Then _
                If a.Value > 1 And a.Value Mod 100 < 60 And Int(a.Value / 100) < 24 Then _
                    a = TimeValue(Int(a.Value / 100) & ":" & Format(a.Value Mod 100, "00"))
        Next a
    End If
FallThrough:
    Application.EnableEvents = True
End Sub
0
Comment
Question by:Cactus1994
  • 3
  • 3
6 Comments
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41841312
Notice the Range mentioned in the following line of code...
If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
The code will be effective for this range so if you want this code to work for the range B23:O34, change the range portion like this...
If Not Intersect(Target, Range("B23:O34")) Is Nothing Then
0
 

Author Comment

by:Cactus1994
ID: 41841316
Hi Subodh. Thanks -- I should have added more detail.

I only want the cell ranges B7:O18 and also B23:O34 in this VBA.  (I do not want cells B19:O22 included.)

Thanks!
0
 

Author Comment

by:Cactus1994
ID: 41841322
My question should have looked like this:


I have the following VBA I want to exclude cells A19:O22 from. How do I do this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
        On Error GoTo FallThrough
        Application.EnableEvents = False
        Dim a As Range
        For Each a In Intersect(Target, Range("A7:O34"))
            If IsNumeric(a.Value) Then _
                If a.Value > 1 And a.Value Mod 100 < 60 And Int(a.Value / 100) < 24 Then _
                    a = TimeValue(Int(a.Value / 100) & ":" & Format(a.Value Mod 100, "00"))
        Next a
    End If
FallThrough:
    Application.EnableEvents = True
End Sub
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41841329
Change that line to this...
If Not Intersect(Target, Range("B7:O18,B23:O34")) Is Nothing Then
0
 

Author Closing Comment

by:Cactus1994
ID: 41841333
Wow ... it was that easy. I told you I was a complete rookie newbie to VBAs!

Thank you!
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41841334
Yes it was that easy. :)
You're welcome. Glad to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question