VBA for auto update cell

  • Hi
    Am a beginner for VBA, hence pls help on below simple case.


    I have an excel shared file which is running with the VBA code. I want an VBA (not formula) for below case.


    I want to auto update the 'Sheet1 Raw AM8 to AM3000' based on the cell value of Sheet4 G8 to G3000 & H8 to H3000. Following way i would want to update the cell...


    If the Sheet4 G8 Columns value is more than 0, then the Sheet1 AM needs to update as Y'.
    If the Sheet4 H8 Columns value is more than 0, then the Sheet1 AM needs to update as Rejected.
    If both Sheet4 G8 & H8 Columns value is more than 0, then the Sheet1 AM needs to update as Rejected.
    If both Sheet4 G8 & H8 Columns value is 0, then the Sheet1 AM needs to update as Approved.


    Appreciate your soonest kind reply on this case... Thank you...

  • Hi Lenissa,


    Welcome to Ozgrid!!


    Could you please check if the following formula works as expected by putting it into cell AM8 of Sheet1 and copying it down to cell AM31:


    =IF(OR(Sheet4!H8>0,SUM(Sheet4!G8:H8)>0),"Rejected",IF(Sheet4!G8>0,"Y",IF(SUM(Sheet4!G8:H8)=0,"Approved","")))


    If it does I'll convert it to a macro for you. If not please amend it so it's correct and I'll convert that formula.


    Thanks,


    Robert

  • Hi Robert
    Thanks for your advise...


    I've amended the code as the Y' result was not coming. Below is the corrected formula & pls help to convert it to the VBA. Kindly note that file which am going to add,already has an VBA, So please help to advise how i will add this to the same file, thanks...



    =IF(OR(Sheet4!H8>0),"Rejected",IF(Sheet4!G8>0,"Y",IF(SUM(Sheet4!G8:H8)=0,"Approved","")))

  • Pls see the corrected new formula in below, as I've added one more criteria, thanks...


    =IF(OR(Formula!H8>0),"Rejected",IF(Formula!I8>0,"Not Required",IF(Formula!G8>0,"Y'",IF(SUM(Formula!G8:H8)=0,"Approved",""))))

  • Hi Lenissa,


    Here's some code putting your last formula into Sheet1:



    I see you've also changed the name of Sheet4 to Formula. Note you will have to change the macro if you change the sheet names going forward.


    Regards,


    Robert

  • HI Robert, Thanks for your help.


    I've added this code to the workbook, but it is not running automatically. But when i click the run button its working perfectly. Really appreciate if you can help me on this, thanks...

  • Hi Robert,


    I want to Mauro update the Sheet1 AM Cell whenever I change the numbers in formula sheet in G,H,I cells.
    When i use this VBA code it s not running automatically. But its is running perfectly when i click the running button in VBA page.
    Appreciate your help here...

  • Will the numbers in columns G and H be formula driven or manually keyed in? Why do you want to include changes in column I as well?


    I think the best way is for Sheet1 to automatically updated each time it's selected.

  • OK, automatically populating Col. AM of Sheet1 when it's selected (activated) is definitely the way to go then. Put this worksheet event macro* onto Sheet1:



    Regards,


    Robert


    *Note to install this macro follow these four steps:

    • Copy my code to the clipboard (Ctrl + C)
    • Right click on Sheet1 and click View Code from the shortcut menu
    • Paste (Ctrl + V) my code from step 1 into the Visual Basic Editor (VBE)
    • From the File menu select Close and Return to Microsoft Excel
  • Hi Robert
    Am really sorry to bother you a lot..But the sheet one is already running with the another VBA code, I've inserted this code as new module only. But its not updating automatically. Pls correct me if am wrong, thanks...

  • Quote

    I've inserted this code as new module only.


    No, it has to go onto Sheet1 - refer my four points above :confused:


    Have a look at the attached where when you open the file Col. AM is blank. Then click any other tab in the workbook and then click Sheet1 again. You then see the Col. AM is populated. This will always happen whenever you click back into Sheet1 from another sheet.


    Thanks,


    Robert

  • HI Robert, The file you sent is working perfectly...Thais what am looking for too..


    Pls note the below code is currently running in my original file Sheet1. Can you please help to advise me how can i insert the new code in the same sheet? thanks...




    Please use the appropriate tags when posting code as I have done for you this time. Thanks.


    Quote

    Can you please help to advise me how can i insert the new code in the same sheet?


    Just copy and paste my code above immediately beneath this code (except the line that says "Option Explicit" as that needs to be the very first line).


    Thanks,


    Robert

  • Hi Robert.


    Finally I managed to correct it. The code was perfectly fine & the error is just bcz of the incorrect inserts from my side only. Thanks a lot for your help on this case.


    Only one fine tune I required from your side,as I may needs to add some additional information in the same Sheet1 AM Cells. But with current method, Its not accepting any other texts on that cell. Appreciate if you find any way for this too, thanks...

  • Hi Lenissa,


    Not too sure what you mean as yes, each time Sheet1 is selected Col. AM is cleared and reset with with data from the Formula tab so any existing in that column will be cleared.


    Sorry but I'm signing out of this post as I feel your original request has been met. If not I'm sure another Ozgrid member maybe able to solve your last request.


    Regards,


    Robert

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!