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

Modify vba code

I

IKHAN

Member
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: 
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
 

Attachments

Last edited:
@ 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
 
Send that file here which has 'challenge'.
and explain there
before
and
after cases!

What do that 'Macro' do?
 
@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 %.
 
@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"

let me know..If you're unable to download test file.
 

Attachments

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.
 
@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.
 
@vletm..

Doesn't work.

Still adding numbers in your sheet column J22

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: 
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
 

Attachments

@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.
Your .xlsx has different layout!
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'!
 

Attachments

It was Your file!
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!
 
@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
 

Attachments

Okay ...
I have tried to ask about that 'J20' few times...
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..
 

Attachments

@IKHAN
Where do You insert row?
If You insert row above original J20 then ...
original code no work!
Try ReRead #12 - red text!
What about 'hours'?
I cannot get idea without clear vision = file, good file!
 
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
 

Attachments

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

Attachments

@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..
 
@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)
 
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?
 
You must log in or register to reply here.
Back
Top