le_johney
asked on
Macro for Excel 2011 on MAC - discover interaction, look into matrix for value and translate impact into a given range of cells
Hi Experts,
since I already know how powerful your brains are I would like to kindly ask you for extra help with building a macro to help me to run a specific evaluation of interactions btw different categories (in this case A - I).
Here, I also include the WB where I need to make this happen:
WorkbookSample-MAC.xlsm
What I need the macro to do is the following:
I have an input table that looks like this:
input-table.tiff
I want to use the checkboxes to do the following for me:
when unchecked - the effect of relevant category will be 0
when checked - the macro will scan the array with checkboxes (in my case column Q) and will search for other checked boxes. Two possibilities may appear:
1) there are no other checked categories and the intensity of the category effect will remain full
2) there are other checked boxes and the macro will look into a predefined matrix (in my case sheet InteractionMatrix) and will look if there is an interaction between the box I checked and other checked boxes / categories within the array.
2.1) if there is no interaction (represented by value 0) - intensity remains unchanged
2.2) if there is an interaction (represented by value 1) - intensity will be adjusted according to relevant values that the macro will search for in matrix in sheet ValueMatrix) - multiplier will be shown in the sheet Summary (2) column P (attributed to relevant category).
I think it's a lot of if statements combined together essentially that need to be coded.
The hard part is that the interaction effect should translate also to the category with which our "checked" category interacts - say A interacts with B - and if B is checked it should assume for the same interaction value.
This may be still easy to do BUT we know there are multiple interactions between the categories and so if I have checked A, B, E and they all interact - the effect should be recorded in the column P for all of them.
The interaction functionality may be attributed to a command button - however would be nice if could be automatic - whatever is easier though in terms of sequences of calculations.
I hope this post is clear and the goal with it.
I appreciate your help of any kind!
I hope we get this riddle solved!
Thank you in advance.
J.
since I already know how powerful your brains are I would like to kindly ask you for extra help with building a macro to help me to run a specific evaluation of interactions btw different categories (in this case A - I).
Here, I also include the WB where I need to make this happen:
WorkbookSample-MAC.xlsm
What I need the macro to do is the following:
I have an input table that looks like this:
input-table.tiff
I want to use the checkboxes to do the following for me:
when unchecked - the effect of relevant category will be 0
when checked - the macro will scan the array with checkboxes (in my case column Q) and will search for other checked boxes. Two possibilities may appear:
1) there are no other checked categories and the intensity of the category effect will remain full
2) there are other checked boxes and the macro will look into a predefined matrix (in my case sheet InteractionMatrix) and will look if there is an interaction between the box I checked and other checked boxes / categories within the array.
2.1) if there is no interaction (represented by value 0) - intensity remains unchanged
2.2) if there is an interaction (represented by value 1) - intensity will be adjusted according to relevant values that the macro will search for in matrix in sheet ValueMatrix) - multiplier will be shown in the sheet Summary (2) column P (attributed to relevant category).
I think it's a lot of if statements combined together essentially that need to be coded.
The hard part is that the interaction effect should translate also to the category with which our "checked" category interacts - say A interacts with B - and if B is checked it should assume for the same interaction value.
This may be still easy to do BUT we know there are multiple interactions between the categories and so if I have checked A, B, E and they all interact - the effect should be recorded in the column P for all of them.
The interaction functionality may be attributed to a command button - however would be nice if could be automatic - whatever is easier though in terms of sequences of calculations.
I hope this post is clear and the goal with it.
I appreciate your help of any kind!
I hope we get this riddle solved!
Thank you in advance.
J.
ASKER
Hi GowFlow,
this is just an add on to the work we have already done - didn't want to link whatever I am trying to do now to what we'd done previously.
OK, I will try to be more clear about this:
Essentially I have a column Q with checkboxes.
I need to find a way how to make the checkboxes interact - if A is checked (TRUE) and I also check B (TRUE) the macro will look into the matrix in the sheet InteractionMatrix to look if these categories A and B are related. If they are related (value 1 from the matrix), the macro will go search for their correlation in the matrix in sheet ValueMatrix.
Then if I check also box for category D, the macro will need to evaluate the correlation of D with A and D with B (and possibly every other categories that will be checked later) to come up with a) whether they are related, b) if they are what is the value of correlation from the ValueMatrix sheet.
Now what the macro does afterwards is that it multiplies the correlations (if they exist and puts the value in a relevant cell (right next to each category) in column P).
Again, in real world talk - category A stand alone performs on 100% but if it is correlated with B and I select B to add it to the portfolio, it will decrease the effect of A by a certain factor and make A less efficient in that mix.
I hope this helps.
I know it may sound simple to me but pls. do let me know if you are still lost in that taboulistan :)
Thanks
J.
this is just an add on to the work we have already done - didn't want to link whatever I am trying to do now to what we'd done previously.
OK, I will try to be more clear about this:
Essentially I have a column Q with checkboxes.
I need to find a way how to make the checkboxes interact - if A is checked (TRUE) and I also check B (TRUE) the macro will look into the matrix in the sheet InteractionMatrix to look if these categories A and B are related. If they are related (value 1 from the matrix), the macro will go search for their correlation in the matrix in sheet ValueMatrix.
Then if I check also box for category D, the macro will need to evaluate the correlation of D with A and D with B (and possibly every other categories that will be checked later) to come up with a) whether they are related, b) if they are what is the value of correlation from the ValueMatrix sheet.
Now what the macro does afterwards is that it multiplies the correlations (if they exist and puts the value in a relevant cell (right next to each category) in column P).
Again, in real world talk - category A stand alone performs on 100% but if it is correlated with B and I select B to add it to the portfolio, it will decrease the effect of A by a certain factor and make A less efficient in that mix.
I hope this helps.
I know it may sound simple to me but pls. do let me know if you are still lost in that taboulistan :)
Thanks
J.
sample code, you can finish yourself. This is code only for step 1 and checkbox A (assign the macro there):
if you look through the code I think you can first finish the code (and matrix values), and then slightly adjust it for all checkboxes (copy & paste for each checkboxes, then make minute changes, making a total of 9 subs, each with 8 if statements)
After you're done, we can start with the other points.
Sub CheckBox2_Click()
'checkbox A code
'checking checkbox B
If LCase(ActiveWorkbook.Sheets(1).Cells(62, 2).Value) = "true" Then
MsgBox "b is checked"
If ActiveWorkbook.Sheets(2).Cells(3, 3).Value = "1" Then
MsgBox "matrix = 1"
'add more interaction value code
End If
End If
'checking checkbox C
If LCase(ActiveWorkbook.Sheets(1).Cells(63, 2).Value) = "true" Then
MsgBox "c is checked"
If ActiveWorkbook.Sheets(2).Cells(3, 4).Value = "1" Then
MsgBox "matrix = 1"
'add more interaction value code
End If
End If
'checking other checkboxes etc etc totalling 8 statements
End Sub
if you look through the code I think you can first finish the code (and matrix values), and then slightly adjust it for all checkboxes (copy & paste for each checkboxes, then make minute changes, making a total of 9 subs, each with 8 if statements)
After you're done, we can start with the other points.
ASKER
Thank you very much! Will definitely try this.
Although this seems to be a little long for what VBA can really do. Is there a way to go around this and make it shorter? String type maybe?
Thanks
J.
Although this seems to be a little long for what VBA can really do. Is there a way to go around this and make it shorter? String type maybe?
Thanks
J.
ASKER
...this is only supposed to show me that they are related right?
ASKER
is there a way to work with the range of the matrix instead of particular cell positions?
Yes, you can have each checkbox have this code
checkboxb will have Clicked(2) etc.
the Clicked code would look like this:
Sub CheckboxA_click()
Clicked(1)
End Sub
checkboxb will have Clicked(2) etc.
the Clicked code would look like this:
Sub clicked(clicked_val As Integer)
For i = 1 To 9
If Not (i = clicked_val) Then
a = 60 + i
If LCase(ActiveWorkbook.Sheets(1).Cells(a, 2).Value) = "true" Then
b = 2 + clicked_val
If ActiveWorkbook.Sheets(2).Cells(b, 1 + i).Value = "1" Then 'this is your matrix!!!
MsgBox i & "matrix = 1"
'more interaction value code
End If
End If
End If
Next
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Spot on GowFlow!!!!
You really are a Genius....Mastermind!
Thanks so much for this. It does just what I wanted it to do!
Have a great weekend and thanks for staying up late!
All the best!
J.
You really are a Genius....Mastermind!
Thanks so much for this. It does just what I wanted it to do!
Have a great weekend and thanks for staying up late!
All the best!
J.
Your welcome my pleasure. Did you try it on MAC ?
gowflow
gowflow
ASKER
I haven't,but I read the code and it seems like it will need the same work as last time - worksheet labeling/naming.
Again,thank you very very much!
Again,thank you very very much!
ok so if the question is done for you and no help needed I would appreciate you close it as you feel appropriate. Meantime pls feel free to post here any link to any question you may need help with,
gowflow
gowflow
ASKER
GowFlow did it for me again! Good job!
My pleasure will keep this question monitored in case you need help down the line pls post a link in here will be glad to assist.
Rgds/gowflow
Rgds/gowflow
If you understood something of what I wrote, then you are better off with my understanding of what you want to achieve !!!
Second, having worked on this file, I do not see the essential macro that was developed already un der Record button which again leave me more lost !
gowflow