Posts by Trebor76

    You are using the wrong function i.e. CInt should be CDate like so:



    I believe you also had the Exit Sub in the wrong spot.


    HTH


    Robert

    Hi Pinecroft,


    At a guess I'd say the issue is that the tab you originally run the code from has nothing in cell B8. When you run the code a second time the "VolunteerDistance" has been selected which has a value in cell B8 so all is good. Try moving this line of code...


    Code
    1. val = Range("B8").Value


    ...underneath this line to solve the issue:


    Code
    1. Sheets("VolunteerDistance").Select


    Note - you need to wrap any code you post within the appropriate tags as I have done like so:


    [NOPARSE]

    Code
    1. Your code here

    [/NOPARSE]


    Regards,


    Robert

    Hi Johnnyv015,


    Hard to know without having your workbook but the SaveAs syntax line looks correct. At a guess I'd say the FileSaveName variable is blank i.e. there's no text in cell R2 of the active sheet. If there is make sure the three variables that you've defined make a proper full path by putting them in a message box or the immediate window.


    HTH


    Robert

    Hi jl2509,


    Try this while on the sheet in question:



    Regards,


    Robert

    Quote

    I mean, when i change the data in 'Formula G&H', the Sheet1 AM raw is not updating automatically. It is only updating when I clear the data's in AM, then change the tabs.


    Ah now I think I see what you mean. Try this (notice the new variable varMyArray which needs to contain every possible outcome the formula can produce):


    What's not working?? It is for me :confused:


    Follow these four steps and let me know how you go:


    1. Clear any data in Col AM of Sheet1 from cell AM8 down
    2. Put some random data in Col. AM
    3. Click any other tab and click back into Sheet1
    4. Note how only blank cells will be populated not any you filled in from step 2


    HTH


    Robert

    Try this worksheet event macro on Sheet1:


    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

    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

    Files

    • Lenissa.xlsm

      (18.62 kB, downloaded 173 times, last: )

    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

    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.

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