• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying VBA to other cell (loop?)

Status
Not open for further replies.
K

kohli.jd

New Member
Hi all,

So I am stuck on how to go about finishing this sheet I created. The logic of this sheet works like this:

Ok, I have wrote a section of code that is fairly long and includes many different cells. My question is this: Can I use the VBA code that I have written and push that code to another cell (just the logic of how it works)? I do not just want to simply copy and paste the logic into a cell, but I want the logic to be changed to look at the cells and reformat all the cells to match the new location.

Here is part of my VBA code below. It basically consist of cell D21 with a dropdown selection of 1,2, or 3. It merges and changes cells based on the selection made in the dropdown. However, I want to move the "logic" part of it to D28. So now D21, if not touched, would not be merged with any other cells. Now I go to the next (D28) option and maybe I want that to be a 3 slot breaker. It would use the logic to merge all three together and so on. Doing this the "old fashion" way, I would have to go through the entire code and change each cell to it's appropriate new location. This is EXTREMELY time consuming and leads to a lot of error. There HAS to be an easier way to achieve this. I want the code to change based on the new location. So this is why a simple copy and paste will not work. Can this be done?




When a "1" is selected in cell D22 it will show the following:

1710168662782.png



When a "2" is selected in cell D22 it will show the following:

1710168803695.png



When a "3" is selected in cell D22 it will show the following:

1710168855472.png
 
vletm said:

kohli.jd​

Those three pictures can play with this sample.
Click to expand...
Yes, that seems to be much simpler code, however, take that logic and now add it to every dropdown. Does that make sense?

Red needs the same logic but it needs to now work correctly. It's not just a simple copy and paste. I believe I need a loop.
Blue the same...
Green the same...

1710860168755.png
 

kohli.jd​

... simpler ... what?
Did You write that You'll need that functionality with every dropdown? ... did You?
Where are every dropdowns? ... those next three dropdowns or what do You mean?
 
I'm sorry, I didn't explain it very well. I may have missed some information with what I am trying to explain. My apologies! Ok hopefully this makes sense below:

So, from D22 cell, there is a dropdown list that consist of (3) different options to choose from: 1,2, or 3. Below I selected (1).
1710863960327.png

On this one, I selected (2) on cell D22 and it merged the cells and showed me that the breaker is not a 2 slot breaker.
1710864056157.png

Lastly, I selected (3) on D22 and it merged all three together indicating it was a 3 slot breaker.
1710864122996.png

However, if I don't want a (3) slot merged breaker, then just select (1) from the D22 dropdown list (here below) and it will unmerge all cells and start from the original format again.
1710864424110.png




Now, what I want it to do....

Notice, I am now on D29 cell and it's dropdown. I need the same logic to work as above. If I select the options it will merge them. While doing this, D22 cell will be unchanged. As to say, once I'm done with D22 selection, I move on to the next break which may be different (number of slots) than D22.

1710864197222.png
 

kohli.jd​

Now, what I want it to do....
Why did You skip above in the beginning?
... too many things matters - how to try to solve this kind of cases.
Now, You named ... two dropdowns ... is that all?
While wondering ... I modified my version that there can be more of those (dropdowns).
Each could be single, double or triple something.
Of course, no need to use all of those. There could add more of those too.
 

Attachments

  • kohlijd.xlsb
    27.1 KB · Views: 3
vletm said:

kohli.jd​

Now, what I want it to do....
Why did You skip above in the beginning?
... too many things matters - how to try to solve this kind of cases.
Now, You named ... two dropdowns ... is that all?
While wondering ... I modified my version that there can be more of those (dropdowns).
Each could be single, double or triple something.
Of course, no need to use all of those. There could add more of those too.
Click to expand...
Ok, I tried the code and it is exactly what I want it to do. So, for the dropdowns, I need the last dropdown list to be cell 267.

1710872030387.png


Your statement: "Each could be single, double or triple something." This is correct. This is how I want it to be used.

Would it help if I share the file with you?
 

kohli.jd​

Can You count - how many times I've asked - how many dropdowns ... can You?
I can check later something.
... hmm? ... okay
I modified code.
You gotta modify Yourself number of datavalidations to that code.
Now, default value is there six (6).
 

Attachments

  • kohlijd.xlsb
    30.6 KB · Views: 5
Ok perfect, I changed the default value to 36 and it works for the whole sheet. Can you tell me why this is happening? See below, when I select (3) it merges correctly with correct fill color BUT when I select back to (1) it leaves the areas that I put a red box around, those need to go back to "NO FILL" areas.

1710933062722.png

1710933075846.png
 
Why ...
My file has had 'colors' like below - okay?
Screenshot 2024-03-20 at 18.19.19.png
You've commented
#7 reply Ok, I tried the code and it is exactly what I want it to do - okay?
#9 reply Ok perfect, I changed the default value to 36 and it works for the whole sheet. - okay?
Those two Ok-sentences gives strong image that ... okay.
You've skipped to write - that You would like to have those colors too - or have You?
Why? ...The reason is Merge ... UnMerge swapping, which has already make a lot of take care.
... still I don't have clear image ... why do You ... need many rows there?
 
So far, you have helped me achieve what I want but I have more that I need help with. I'm sorry, I was trying to go in steps so that I could better explain this.

"You've skipped to write - that You would like to have those colors too - or have You?
Why? ...The reason is Merge ... UnMerge swapping, which has already make a lot of take care."

I still need the cells be "no fill" when they are unmerged. I have not fixed this yet.
 
So far Your step has meant to do three different versions instead of one.
Do You ... need those red frames too?
One challenge is that You show - what You do not want ... instead that other, which could be something else.
Without Your replies, it's challenge to guess - what do You really would like to get?
 
I'm so sorry for the confusion! I do not need the red frames, they are just areas that I wanted "no fill". Can you change it so that they go back to "no fill" when they are not all merged together?
 
I can change many things...
but as I tried to explain in my the latest question ... it's a challenge.
 

Attachments

  • kohlijd.xlsb
    27.1 KB · Views: 7
EXCELLENT, you are an expert!! Can I ask you for one more favor now that you have fixed my issues. I have one more problem and I believe it may be an easy one to fix.

I applied the most recent code that you provided me to my sheet and it works great. My last request is this: see where I put a "RED" box around the right side of the sheet (it is only there to represent the area that needs worked on). So, the logic that you have given me just a few moments ago now needs to be mirrored to the other side (area in RED). I believe this will be a simple task but I am not sure how to combine the logic so that it works on both side.

So, when I originally asked you for help we started at D22 cell and then made our way down to D267 with the logic. Now the logic will also need to be applied to AZ22 and so on until it reaches AZ267. So the final sheet can be used on both sides all the way down the list.

I hope I am making sense! I cannot thank you enough for all the help you have given me. I greatly appreciate it!!!

1710962296215.png
 
You skipped my questions ... here few more of those:
You're using interesting terms:
# what have I fixed?
# what is problem?
# easy means normally something double or more times same
# the most ... if not everything then ... are You over 50% sure that skipped code ... no need? ... wow!
# my last request ... I've read those words many times ... it actually means that there will be more and more
# mirrored ... means if D22 value will change then AZ22 value will change too ... or how?
# originally ... You skipped something ... and then ...
# sense ... as written ...
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
In this time, You should able to give an answer at least one of my question, which would give 50/60 possible to guess correct.
 
vletm, I am sorry that you are having a difficult time understanding what I am asking. That is not my intention and I do not want to confuse you in what I am asking for.

# what have I fixed? You helped me get the sheet working properly. I did not understand how to write the logic and you were able to help me achieve it.

# what is problem? I didn't know how to write the logic.

# the most ... if not everything then ... are You over 50% sure that skipped code ... no need? ... wow! Since your help, I am 80% complete

# mirrored ... means if D22 value will change then AZ22 value will change too ... or how? Mirrored may not have been the best choice of wording. AZ22 has a dropdown list that would allow me to select (1, 2, or 3) and it would merge based on the selection. The dropdowns would start at AZ22, AZ29, AZ36, until it reaches AZ267.



The code that you have provided me allows the merging and unmerging of cells from D22 - D267 (based on the selection of the dropdown lists - 1, 2, or 3) and what you have written in the vba code allows this action to be performed. I spent a little time and wrote the code below so that it would work for column AZ. In column AZ, I would also need it to merge and unmerge cells (based on the selection of the dropdown lists - 1, 2, or 3). It would perform this independent from column D.
 
Difficult? ... who?
You've tried to give something step-by-step.
It's okay - if those steps would come in logic order ... otherwise it'll make mess. The mess would be okay .. if 60$ per hour.
# the most ... ouch!
# mirrored ... This sample's those D- & AC-column dropdowns work separately ... but it could modify to work as pairs too.
I gotta change some 'minor' things too.
I gotta guess ... what do You really need?
If those three blue sentences have followed better, then this would have gone more smoothly
This file works with my settings.
Maybe You should do some 'minor' modifications.
My 'minor' could be same as Your 'easy'.
 

Attachments

  • kohlijd.xlsb
    30.8 KB · Views: 5
Can you change something in the code that you attached? This is not your fault, this is mine. I gave you the incorrect information! Can you please see the picture?

I attached the file so that you can view it.

1711029462515.png

D22:D26, D29:D33, D36:D40, ...until it reaches D267:D271

AZ22:AZ26, AZ29:AZ33, AZ36:AZ40, ...until it reaches AZ267:A271
 

Attachments

  • WIP-Panel.xlsm
    148.6 KB · Views: 2
Last edited:
What is Your ... ?
As I wrote
This file (my own file) works with my settings.
Maybe You should do some 'minor' modifications.
Did You those 'minor' modifications?
I could notice based above snapshot that You ... maybe ... have copy and pasted something.
That my file works smoother for many reasons.
Should I use to modify my code again back to something myself?
... that would take more that 11 minutes, which I have time now.
 
I tried to do the modifications but I failed and it did not work correctly. So, maybe we will try a different approach. I attached the file that you wrote and it works on the sheet for the LEFT side of the sheet. What needs to be done is to use the same logic and use it on the RIGHT side of the sheet. Yes, can you please modify this for me?

1711034506107.png
 

Attachments

  • kohlijd (11).xlsm
    28.9 KB · Views: 2
Above file is Your ..
What is Your challenge with my previous or this file?
If You really would like to us it Your way
then You could see - how I have done it.
 

Attachments

  • kohlijd.xlsb
    38.2 KB · Views: 7
Status
Not open for further replies.
Back
Top