• 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.

Trying to automate excel file with Macro

patilbhush007

New Member
Hello Everyone

Below I have attached some screenshots regarding the output I am trying to achieve.

What I am trying to do is:
1) Based on sheet 1 and sheet 2 common column which is ID. Selecting ID from first column(sheet1) and filtering with same id in sheet2(column1).
2) Once I get the filter data check for checkbox value from home tab in below example its 1 Auth if true then find the value in sheet2(column3). With respect to checkbox value if found in column 3(Sheet2) then only go to column 12(sheet2) and update value do this multiple time if checkbox value occurs more than 1. Do this till last row in sheet1(column1).
3) After updating value in column12(sheet2) check for next column13 if has similar value then change to the value from column 12.

Any help is much much appreciated.

76627 76628 76629
 
Hi Thanks for reply I have attached sample file.
Just to me more specific. In home tab selecting Flow and ceiling is mandatory. User will select Band+flow or Band+ceiling or band+flow+ceiling.
If selected band+flow sheet2(BandingRule) column 3 and column (Discretion % From (>=)) should get change.
If selected band+ceiling sheet2(BandingRule) column 3 and column (Discretion % To (<)) should get change.
If selected band+ceiling+flow sheet2(BandingRule) column 3 and column (Discretion % From (>=)) and (Discretion % To (<)) both should get change.
 
patilbhush007
eg Based on sheet 1 and sheet 2. Use terms which can see ... not guess.
I cannot follow Your writing.
Your specific has more interesting terms ... my Excel's columns has letters - instead of Your Excel.

Is somewhere expected results?
You're using ActiveX-components - I cannot use those as well as Your codes.

Maybe someone else could have more ideas for You.
 
patilbhush007
eg Based on sheet 1 and sheet 2. Use terms which can see ... not guess.
I cannot follow Your writing.
Your specific has more interesting terms ... my Excel's columns has letters - instead of Your Excel.

Is somewhere expected results?
You're using ActiveX-components - I cannot use those as well as Your codes.

Maybe someone else could have more ideas for You.
Thank you so much for looking into it.

I have shared just a sample I was trying to if you have better idea you can share. I am just trying to update data based on option choose on Home screen which are Active X controls. Based on that control the values should update in sheet2(bandingrule tab). So i am looking for macro that will sort and find and then update value. Let me know if you want more detail i can try explaining in different way.
 
patilbhush007
I tried to figure that You would like to do filtering based those controls.
I did new controls for those as well as cleared all of Your code
... as I wrote - I cannot use.
Select any of those - do wanted combination and select BandingRule1-sheet to get results.
... I gotta guess those Flow and Ceiling effects...
You wrote something about ... updating ... hmm? ... I skipped that part.
 

Attachments

  • Sample testing.xlsb
    44.7 KB · Views: 6
patilbhush007
I tried to figure that You would like to do filtering based those controls.
I did new controls for those as well as cleared all of Your code
... as I wrote - I cannot use.
Select any of those - do wanted combination and select BandingRule1-sheet to get results.
... I gotta guess those Flow and Ceiling effects...
You wrote something about ... updating ... hmm? ... I skipped that part.
Thank you so much you did a great job its not 100% what I wanted but it helped me 60% to achieve my result. Much appreciated your help.
 
Based what should G-column value change?
Which similar value to find from H-column?
What should update and with which value?
... my code keeps all same so far.
Could You give clear example - what?
... based Your snapshot ... there are many blue 'lines' ... what has updated?
 
Based what should G-column value change?
Which similar value to find from H-column?
What should update and with which value?
... my code keeps all same so far.
Could You give clear example - what?
... based Your snapshot ... there are many blue 'lines' ... what has updated?
G-column value should change based on user input. I have input box in Home tab called points to reduced. e.g if user inputs 3 column G is changed from 1 to 4 and then it should find 1 in both column column G column H and change all1 to 4.
 
I underlined one sentence ...

If Home-sheet has below selections ...
Screenshot 2021-10-14 at 20.45.49.png

It'll show below ...
Screenshot 2021-10-14 at 20.46.34.png
What will happen to G-column values?
( column G is changed from 1 to 4 )
... You could able to show values after that
What will happen to H-column values?
( it should find 1 in both column column G column H and change all1 to 4. )
... You could able to show values after that
I could not see Your clear example ... yet.
 
I underlined one sentence ...

If Home-sheet has below selections ...
View attachment 76656

It'll show below ...
View attachment 76657
What will happen to G-column values?
( column G is changed from 1 to 4 )
... You could able to show values after that
What will happen to H-column values?
( it should find 1 in both column column G column H and change all1 to 4. )
... You could able to show values after that
I could not see Your clear example ... yet.

If users input is adding 3 points to current value with selected 3ME in column 3 than the result should be as follows
7666076659
 
If You've used same settings as I -
Your expected results looks ... wild
... or ...
You've other kind of logic, which You've written.

I had there only 3 ME 's ...
You seems to have ... many kind of Bands and 2x -11.99 have kept same ... hmm?

What is ... current value?
 
If You've used same settings as I -
Your expected results looks ... wild
... or ...
You've other kind of logic, which You've written.

I had there only 3 ME 's ...
You seems to have ... many kind of Bands and 2x -11.99 have kept same ... hmm?

What is ... current value?
To clear your doubt when 3 ME is selected with flow and ceiling it should not filter instead it should look for that particular Band and then change the value. Filter is only applicable to column 1 Rule Set ID after than look for selected band and change all value available in that band firstly in column G then look for same value in in both column G and H then change accordingly. even the band is different it should change value if available in selected band. only Rule ID in column 1 should be unique.
2x -11.99 have kept same because there is no-11.99 in 3 ME. If 3 ME don't have that value it will not change it will only change values available in 3ME doesn't matter if the same value is under 2 or 1 or 4 or 5 it should match with 3ME.
 
You had Your own selection - I got only 3 ME.
Your snapshots 2nd rows H-column value changed and last five rows has neither 3 ME.
Seems that there are current values for You.
This's clear ... maybe for You ... that's need to be.
Seems You would like to clear Yourself those missing 40%,
because not clear rules for others than You.
 
You had Your own selection - I got only 3 ME.
Your snapshots 2nd rows H-column value changed and last five rows has neither 3 ME.
Seems that there are current values for You.
This's clear ... maybe for You ... that's need to be.
Seems You would like to clear Yourself those missing 40%,
because not clear rules for others than You.
Hi Thank you for your help it was great help I figured out with the help of your code.

I need one more help if you don't mind.

I have data in 2 column If 2nd column has same value as 1st column it should changed based on 1st column.
e.g Column 1 has 2,3,2,3 and if Column 2 also has 2,3,2,3 then If I change column 1 to 3,4,3,4 then column 2 should also change to 3,4,3,4.
 
patilbhush007
Did You read my previous text at all?
If Your original case is not ready, then how could I add 'one more' something which is as clear as Your original.
eg I cannot see any 1,2,3,4 -values anywhere with any clear logic - can You?
 
patilbhush007
Did You read my previous text at all?
If Your original case is not ready, then how could I add 'one more' something which is as clear as Your original.
eg I cannot see any 1,2,3,4 -values anywhere with any clear logic - can You?
Hi I have read your previous text but that was not the 100% solution I was looking for as I already said I needed something else. If you can help on the one I just posted it would be a great help.

Thank you
 
patilbhush007
How my given sample could give same as Your, if You cannot give clear rules and valid expected results with Your given rules?
Now You're asking something
... seems You skipped my the last line.
Ps. There are none columns like 1 and 2.
 
patilbhush007
How my given sample could give same as Your, if You cannot give clear rules and valid expected results with Your given rules?
Now You're asking something
... seems You skipped my the last line.
Ps. There are none columns like 1 and 2.
Column G and Column H are 2 column. If i make changes in column G so the same value should get updated in column H. Same as the example i have given in above reply.
 
Back
Top