+ Reply to Thread
Results 1 to 11 of 11

VBA code for inserting a value according to Zone

  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    86

    VBA code for inserting a value according to Zone

    Sir

    i am trying to do a performance chart in excel


    Insert value to corresponding cells ( Row 27) according to Zone wise.

    i have difference zone A,B,C,D,E,F .There is some description which have rate. Rate total will be in cell " F24 "..

    i want to add this rate total to corresponding zones A,B,C,D,E,F ( Row 27) when i am chaging the zone in cell " E5 ".

    Insert value to Corresponding Zone

    file attached
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: VBA code for inserting a value according to Zone

    Looking at your chart I'm assuming you are trying to work out an over all percentage compliance where a score of 3 or more = 100% (and as a consequence a score of 2 = 66% and a score of 1 = 33%). You need to divide the total score by the number of applicable elements and then insert the result in the appropriate cell in row 27?

    If my assumption is correct, let me know, it's a 10 minute job to set that up

  3. #3
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: VBA code for inserting a value according to Zone

    vba-code-for-inserting-a-value-according-to-zone-audit.xlsx

    Okay, the first thing I did was to take the scoring and work it out into percentages in Column G (Hidden) so N/A = <Blank>, 0 = 0%, 1 = 33%, 2 = 66% and 3 and 4 both equal 100% (this is taking the view that 3 is the required standard and 4 is above requirements).

    In cell O15 (hidden) I've taken the number of N/A's away from the Total number of requirements
    In cell O16 (hidden) I've added all the calculations from cells G9 to G23
    In cell O17 (hidden) I've calculated the percentage by dividing the number of in-scope requirements by the total score

    Finally, I've entered an "if statement" in cells D27 to J27 (not including G27) I've entered an IF statement that looks at your entry in E5 and if it matches the entry in D26 to J26 it enters the value calculated in O17 in the appropriate cell.

    All this has been done without VBA. While I appreciate the value of VBAs, I am of the opinion that if you can achieve what you need without them then you don't have to rely on others, who may use the spreadsheet, having macros enabled.

    Finally, I'd recommend that you lock out everything that doesn't need editing in daily use. As a minimum, this will help prevent accidental data corruption.

    Hope this helps,

    Terry

    If it's what you want, can you do the right thing and mark as solved and hit the reputation button
    Last edited by MagicMan; 12-06-2014 at 10:16 AM. Reason: Basic grammatical errors

  4. #4
    Registered User
    Join Date
    02-17-2014
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: VBA code for inserting a value according to Zone

    thanks for your reply

    can u do this formula what u discuss with me in my excel sheet, its will be great thankful if you can help me

  5. #5
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: VBA code for inserting a value according to Zone

    It's in the attachment in my reply mate, open it and take a look

  6. #6
    Registered User
    Join Date
    02-17-2014
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: VBA code for inserting a value according to Zone

    thanks for your reply

    Once value enter to each cells ( zone ) ,its should be constant.. even if i change the cell (E5)


    eg


    if Zone A ,,, value will be added to Zone A ( Cell No. D27)..


    if i change the Cell ( E5) to Zone B , the value added to zone A ( D27) .. should be constant, new value need to add in Zone B ( e27)...

    so that i have two values ( Zone A & B )

  7. #7
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: VBA code for inserting a value according to Zone

    That'll likely be a macro, give me a bit of time to think about it ...........

    I need to work out how to initiate the macro so it copies the value from O17 into the relevant cell in Row 27 as a value. It may be a button to press when you get to the end of the audit.

    Anyone got the answer to a macro
    if E5 = "A" Copy O17 and Paste as value into D27
    if E5 = "B" Copy O17 and Paste as value into E27
    if E5 = "C" Copy O17 and Paste as value into F27
    if E5 = "D" Copy O17 and Paste as value into H27 (note H27 not G27)
    if E5 = "E" Copy O17 and Paste as value into I27
    if E5 = "F" Copy O17 and Paste as value into J27

    Thanks guys

    Terry
    Last edited by MagicMan; 12-06-2014 at 03:32 PM.

  8. #8
    Registered User
    Join Date
    02-17-2014
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: VBA code for inserting a value according to Zone

    thanks , its will be great thankful, and support

  9. #9
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: VBA code for inserting a value according to Zone

    vba-code-for-inserting-a-value-according-to-zone-audit.xlsm

    Hello again,

    Well thanks to the assistance of another couple of other members (Alan Sidman and Greg M), I think I've solved your problem.

    Make sure you enter the Zone as a Capital Letter (or select from Pick List) and then when you've entered your audit values click the "Click here to set Zone Sub-Total" button and it will insert your the percentage as previously discussed.

    Hope this helps,

    Terry

  10. #10
    Registered User
    Join Date
    02-17-2014
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: VBA code for inserting a value according to Zone

    Thanks for your great support...... and thanks to team member Alan and Greg , for doing a great job for me

  11. #11
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work Office 365 Home 2019
    Posts
    424

    Re: VBA code for inserting a value according to Zone

    20141208 insert a value according to zone audit.xlsm

    Hello again,

    I've been thinking about the entry for your zones and how it is dependent upon the entry being made in capital letters.

    As a result, I have inserted some code in the tab to make sure that even if the entry is made in lower case, it forces it to be capitalized.

    I hope this helps.

    Terry

    If this has achieved what you wanted, please mark the thread as Solved and consider clicking on the reputation button.
    Last edited by MagicMan; 12-08-2014 at 08:15 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel time zone by zip code sheet?
    By Ryan Nejman in forum Excel General
    Replies: 7
    Last Post: 05-02-2013, 12:52 PM
  2. Replies: 3
    Last Post: 02-10-2011, 11:41 AM
  3. inserting code into code module crashes
    By Kris in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-25-2006, 01:30 PM
  4. How to convert phone area-code to state name and time zone
    By NoYouShmoopie in forum Excel General
    Replies: 2
    Last Post: 10-07-2005, 10:05 AM
  5. I need to use area code to determine time zone
    By Dick in forum Excel General
    Replies: 1
    Last Post: 07-11-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1