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

    IKHAN Member

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

    Last edited: Feb 15, 2017
  2. vletm

    vletm Well-Known Member

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

    Marc L Excel Ninja

    Messages:
    3,027
    Hi !
    Do not need a code but just formulas !
  4. IKHAN

    IKHAN Member

    Messages:
    217
    @ 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. vletm

    vletm Well-Known Member

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

    What do that 'Macro' do?
  6. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @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. IKHAN

    IKHAN Member

    Messages:
    217
    @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.

    Attached Files:

  8. vletm

    vletm Well-Known Member

    Messages:
    2,544
    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. IKHAN

    IKHAN Member

    Messages:
    217
    @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. vletm

    vletm Well-Known Member

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

    I used my file - check again!

    Attached Files:

  11. IKHAN

    IKHAN Member

    Messages:
    217
    @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 (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
     

    Attached Files:

  12. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @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'!

    Attached Files:

  13. IKHAN

    IKHAN Member

    Messages:
    217
    Thanks...

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

    Please read my first post
  14. vletm

    vletm Well-Known Member

    Messages:
    2,544
    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!
  15. IKHAN

    IKHAN Member

    Messages:
    217
    @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

    Attached Files:

  16. vletm

    vletm Well-Known Member

    Messages:
    2,544
    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..

    Attached Files:

    Thomas Kuriakose and IKHAN like this.
  17. IKHAN

    IKHAN Member

    Messages:
    217
    @vletm

    Excellentae... Thank you much....
  18. IKHAN

    IKHAN Member

    Messages:
    217
    @vletm

    If any row inserted , Breaks the code for J20

    Is there a way to always reference that cell to input hours
  19. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @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!
  20. IKHAN

    IKHAN Member

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

    Attached Files:

  21. vletm

    vletm Well-Known Member

    Messages:
    2,544
    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...

    Attached Files:

  22. IKHAN

    IKHAN Member

    Messages:
    217
    @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. vletm

    vletm Well-Known Member

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

    Attached Files:

    Thomas Kuriakose likes this.
  24. IKHAN

    IKHAN Member

    Messages:
    217
    @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. vletm

    vletm Well-Known Member

    Messages:
    2,544
    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.

Share This Page