?
Solved

Adding to a VBA?

Posted on 2016-10-12
6
Medium Priority
?
128 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:Tim Jackoboice
  • 3
  • 3
6 Comments
 
LVL 34

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:Tim Jackoboice
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:Tim Jackoboice
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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:Tim Jackoboice
ID: 41841333
Wow ... it was that easy. I told you I was a complete rookie newbie to VBAs!

Thank you!
0
 
LVL 34

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

621 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