Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Combining my code not working so good....

Posted on 2014-01-06
6
Medium Priority
?
432 Views
Last Modified: 2014-01-07
Hi All,

I hope your holidays were terrific and you are looking forward to 2014!

I am having some difficulty combining my code in different sheets.

I have attached a work sheet with all of this but I will start by saying this seems to be one area where I am having difficulty.

Here is the code:
Private Sub Worksheet_Calculate()
    Dim rngUpdate As Range
    Dim sFormula As String
    On Error GoTo NoRangeFound
    sFormula = "If('" & Me.Name & "'!H10:H1000=1,Row('" & Me.Name & "'!H10:H1000)&"":""&Row('" & Me.Name & "'!H10:H1000))"
    Set rngUpdate = Me.Range(Join(Filter(Application.Transpose(Evaluate(sFormula)), False, False), ","))
    If Not rngUpdate Is Nothing Then
        Set rngUpdate = Union(rngUpdate, rngUpdate)
        Intersect(rngUpdate.EntireRow, Columns("I")).Value = 1
        Set rngUpdate = Nothing
    End If
NoRangeFound:
End Sub

Open in new window

What it does in this case is if I have a formual in h that is:  =if(f10>g10, 1, 0).  This formula is copied down the entire range.

What the code does is if the formula evaluates to "1" then another "1" appears in the corresponding cell in the "I" column and this triggers the code (see sheet).  On its own it works fine.

Now when I do another similar piece of code in another sheet it doesn't quite want to work the way it should.  I have to play around with the macros and hit run and do things that I don't want to do whenever that magic "1" appears.

So I think I am having some naming convention problems even though these are both private subs.

In the second part of the code the VBA compiler does not like some of the naming conventions of the variables (I think) between the "brains" and "basic" code.  See sheet.

Finally, when I call the sub "placeFlag" the macro box pops up.  I just want "placeFlag" to run and can't figure out why. The sub works fine in and of itself, it is in another sheet in the same work book and is not private so I am not sure what the problem might be.

Finally, in the attached sheet I have not included every single other sheet in the workbook because it is some irrelevant to this problem except as the "placeFlag" sub is concerned...but I think I am just doing something silly that I should have learnt by now but have not.

thanks for all of your help!
Test.xlsm
0
Comment
Question by:BostonBob
[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 15

Accepted Solution

by:
Berkson Wein earned 2000 total points
ID: 39760572
I don't have time right now to review your worksheet, but see that you requested more attention, so I'll try to give you a quick shove in the right direction.

In terms of naming conventions, you've got
Intersect(rngUpdate.EntireRow, Columns("I")).Value = 1

If you wanted to look on a sheet named Sheet2, you could do it like this:
Intersect(rngUpdate.EntireRow, Sheets("Sheet2").Columns("I")).Value = 1


Is that what you need?
0
 

Author Comment

by:BostonBob
ID: 39760718
I'll sure give that a run. I would see how that would work.  I'll let you know. thanks for your help!
0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 39760977
sounds good.  I'll be watching this thread.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:BostonBob
ID: 39761198
Just the tonic that the doctor ordered!!  Can I award you 5000 points?  So awesome.  

You just saved me so much time and worry.  Thanks so much!!!
0
 

Author Comment

by:BostonBob
ID: 39761201
A+     & Gold Star!

It is just so awesome how you top notch experts can look at something and see the essence of the what the problem is.  I can't tell you how much time you saved me.

Thanks Again.
0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 39762249
Glad to be able to be of help.  It's always a pleasure to be able to use my experience to help others.  And of course, most of the experts also use EE as a site to ask questions too and the favor is returned.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

618 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