Solved

Adding to a VBA?

Posted on 2016-10-12
6
103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 31

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
Technology Partners: 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 31

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 31

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

627 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