1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

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

# Modify vba code

Discussion in 'VBA Macros' started by IKHAN, Feb 15, 2017.

1. ### IKHANMember

Messages:
221
Hi,

Require assistance to modify code below

As data is entered in column J6 : J19 cells , each cell in Column K to divide J6 :J19 by " J20"

Example : J20 has data= 120

user inputs in J6 as 10
Column K6 result = J6/J20

user inputs in J7 as 20
Column K7 result = J7/J20

and so on...

Much Appreciated...

Code (vb):
Private Sub Worksheet_Change(ByVal Target As Range)
ay = Target.Row
ax = Target.Column
If ax = 10 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Output")
Select Case ay
Case 5
x = WorksheetFunction.Match(Sheets("Output").Range("\$J\$5"), Sheets("Dropdown").Range("\$G\$1:\$G\$13"), 0) - 2
.Range(.Cells(6, 12 + x * 3), .Cells(20, 13 + x * 3)).Copy Destination:=.Range("\$J\$6")
Case 6 To 20
yy = WorksheetFunction.Sum(.Range("\$J\$6:\$J\$19"))
.Range("\$K\$4:\$K\$19").NumberFormat = "0%"
For y = 6 To 20
.Cells(y, 11) = .Cells(y, 10) / yy
Next y
x = WorksheetFunction.Match(Sheets("Output").Range("\$J\$5"), Sheets("Dropdown").Range("\$G\$1:\$G\$13"), 0) - 2
.Cells(5, 12 + x * 3) = Sheets("Dropdown").Cells(2 + x, 5)
.Range("\$J\$6:\$K\$19").Copy Destination:=.Cells(6, 12 + x * 3)

End Select
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
Call Macro1
End Sub

#### Attached Files:

• ###### Test drop.xlsx
File size:
12.1 KB
Views:
7
Last edited: Feb 15, 2017
2. ### vletmWell-Known Member

Messages:
2,811
hmm... @IKHAN
Last time You wanted those values away and now back again!
Do You know what do You want?
3. ### Marc LExcel Ninja

Messages:
3,175
Hi !
Do not need a code but just formulas !
4. ### IKHANMember

Messages:
221
@ vletm ...Need values in col k , but calculations in col K is incorrect.

Code worksheetfunction is suming up the range j6 :j9

Require in col K to calculate based on adjacent col j cell and divide by col J20

@Marc L .. I did try the formula before posting query, the code above overwrites the formula when data retrieved from other months dropdown list in J5
5. ### vletmWell-Known Member

Messages:
2,811
Send that file here which has 'challenge'.
and explain there
before
and
after cases!

What do that 'Macro' do?
6. ### vletmWell-Known Member

Messages:
2,811
@IKHAN
1) That 'Worksheet_Change' works ONLY-AND-ONLY with SAME LAYOUT which it has done. If You have changed LAYOUT then You have to modify Macro too.
2) It NO NEED fixed formula to solve those %.
7. ### IKHANMember

Messages:
221
@vletm Test file is attached to the first post (Test drop.xlsx) and code above works for my layout for the attached file.

Rattaching file..

Only change reqd. in col K to calculate based on adjacent col J cell and divide by cell " J20"

File size:
12.6 KB
Views:
4
8. ### vletmWell-Known Member

Messages:
2,811
1) Why do You use .xlsx-files? then no Macros! No calculations!
2) Is that Yellow [J21] or [J41] ALWAYS fixed (120)?
3) Is that row 5 in correct format? Previous has more 'normal' looking.
9. ### IKHANMember

Messages:
221
@vletm

1) Why do You use .xlsx-files? then no Macros! No calculations!
For some reason - was unable to upload macro file, so have saved in .xlsx and posted code in thread.

2) Is that Yellow [J20] or [J41] ALWAYS fixed (120)?
- Yellow "J20" and 120 is not always fixed (User inputs that number monthly in J20) Thts the reason asking to ref. J21cell
- J41 is sample field to show the output reqd. for this group

3) Is that row 5 in correct format? Previous has more 'normal' looking.
Yes , Its correct format and works for above code.
10. ### vletmWell-Known Member

Messages:
2,811
1) hmm...?
2) You have used sum - not any more fixed as in Your samples

I used my file - check again!

File size:
20.9 KB
Views:
2
11. ### IKHANMember

Messages:
221
@vletm..

Doesn't work.

Only change reqd. in col K to calculate based on adjacent col J cell by dividing cell " J20"

If you're testin , Please use the attached test file and code.

Code (vb):
Private Sub Worksheet_Change(ByVal Target As Range)
ay = Target.Row
ax = Target.Column
If ax = 10 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Output")
Select Case ay
Case 5
x = WorksheetFunction.Match(Sheets("Output").Range("\$J\$5"), Sheets("Dropdown").Range("\$G\$1:\$G\$13"), 0) - 2
.Range(.Cells(6, 12 + x * 3), .Cells(20, 13 + x * 3)).Copy Destination:=.Range("\$J\$6")
Case 6 To 20
yy = WorksheetFunction.Sum(.Range("\$J\$6:\$J\$19"))
.Range("\$K\$4:\$K\$19").NumberFormat = "0%"
For y = 6 To 20
.Cells(y, 11) = .Cells(y, 10) / yy
Next y
x = WorksheetFunction.Match(Sheets("Output").Range("\$J\$5"), Sheets("Dropdown").Range("\$G\$1:\$G\$13"), 0) - 2
.Cells(5, 12 + x * 3) = Sheets("Dropdown").Cells(2 + x, 7)
.Range("\$J\$6:\$K\$19").Copy Destination:=.Cells(6, 12 + x * 3)

End Select
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub

File size:
15.4 KB
Views:
1
12. ### vletmWell-Known Member

Messages:
2,811
@IKHAN - as I wrote in #6 reply:
1) That 'Worksheet_Change' works ONLY-AND-ONLY with SAME LAYOUT which it has done. If You have changed LAYOUT then You have to modify Macro too.
2) Find out why You cannot send .xlsb-files which worked.
3) If You want/need to modify layout then You have to modify code too!
It is not so called 'automatic'!

File size:
20 KB
Views:
5
13. ### IKHANMember

Messages:
221
Thanks...

I give up with this thread..Its not what am looking for.

14. ### vletmWell-Known Member

Messages:
2,811
Delete from that file which You don't want to see!
Add to that file what You want to see!
Then I could see what is missing or what is too much!
15. ### IKHANMember

Messages:
221
@vetlm

Have highlighted in red for incorrect output and highligted in green for reqd. results and commented for details.

Require Formula j27/j\$41 integrated in macro for col K range

Note : ROW 26 TO 44 is for reference only- example of output reqd.

Actual file to test starts from row 5 to row 20

File size:
20.4 KB
Views:
7
16. ### vletmWell-Known Member

Messages:
2,811
Okay ...
Now, You clearly wrote that 'User Inputs number to J20'!
Was that only challenge; case 'J20'?

>> Now, User have to InPut that number ... or message will come!

>> If that 'J20' could be in 'J4' then this could use with more rows ...
needs some changes to code..

#### Attached Files:

• ###### Test drop IK.xlsb
File size:
21.3 KB
Views:
5
Thomas Kuriakose and IKHAN like this.
17. ### IKHANMember

Messages:
221
@vletm

Excellentae... Thank you much....
18. ### IKHANMember

Messages:
221
@vletm

If any row inserted , Breaks the code for J20

Is there a way to always reference that cell to input hours
19. ### vletmWell-Known Member

Messages:
2,811
@IKHAN
Where do You insert row?
If You insert row above original J20 then ...
original code no work!
Try ReRead #12 - red text!
I cannot get idea without clear vision = file, good file!
20. ### IKHANMember

Messages:
221
Correct - Exisiting code will not work attached in test file .So needs to modified with your expertise.

- If user adds a row between row 5 and 20, The "J20" cell must also move down (where user inputs number)

See attached file

File size:
17 KB
Views:
3
21. ### vletmWell-Known Member

Messages:
2,811
This would be more easier for You.
Those 'fixed' manually inputted blue values are above of 'Months'
and
You could add 'as many rows as needs' under 'Month' ...and so on...

File size:
21.8 KB
Views:
2
22. ### IKHANMember

Messages:
221
@vletm

Have other header files and data above row 5 in my original file, So can't have user input cell on top.

- If user adds a row between row 5 and 20, The "J20" cell must also move down (where user inputs number)

Thanks for assisting again..
23. ### vletmWell-Known Member

Messages:
2,811
... would it better that "J20" would be in other 'workbook'?
... or 'K5'
... then 'users' could find it!

#### Attached Files:

• ###### Test drop IK.xlsb
File size:
22.4 KB
Views:
6
Thomas Kuriakose likes this.
24. ### IKHANMember

Messages:
221
@vletm

Sorry No...Have that format for a specific reason...putting together a project with puzzles..

That's the reason require user input in J20 and as user's also if needed will be adding rows or deleting rows between row5 to to row 20.

If rows added/deleted - J20 cell must always be referenced in your code in post #16 (Good file)
25. ### vletmWell-Known Member

Messages:
2,811
No ...
If You cannot tell enough details BEFORE
then I won't just going to make more guesses!
Or
Would it be same way easier for You that
You would tell BEFORE what I won't do next time?
>> maybe that 'J20'-cell would be in other workbook alone?
Chirag R Raval likes this.