BostonBob
asked on
Combining my code not working so good....
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:
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sounds good. I'll be watching this thread.
ASKER
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!!!
You just saved me so much time and worry. Thanks so much!!!
ASKER
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.
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.
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.
ASKER