Posts by coolhandphil

    Re: Application Enable Events?


    I found the error which was causing the problem so it isn't an issue now.

    However, you bring up an interesting point with the positioning of where to disable events.

    The reason that I have to disable events is that the code is triggered by a change to the worksheet. I disable the events while the code is executing to stop the changes that it makes calling the procedure again. I think this is the right thing to do???!

    Some sample code:


    I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off?

    My problem is this...

    At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through.

    I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!




    I hope this is a fairly easy question to answer but I can't find a solution from searching this site...

    I have created a message box and I want leave two blank lines in the middle of the text. Is this possible?

    At the moment my code for the message box line looks like:

    Msg = "This process will print each site for " & UserSlection & ".  Before continuing please ensure you have the right printer selected.  (I.E. a colour printer where available).  The process may take several minutes to complete.  Do you wish to continue?"

    I just want to move the "Do you wish to continue?" down two lines so that it is easier to read.




    I have an excel based report where I drop data into a blank spreadsheet each month and my formula (in a second worksheet) finds the relevant information.

    I do this by using the Match function on the first row of my source data. This finds the column numbers to the data I am interested in. (This is handy as additional columns of data appear almost every month).

    Most of my formula (in the second worksheet) works on vlookups using the column number from the match function.

    However, I need to do a sumif. Sumif requires cell references rather than a column number. How can I get around this?

    Due to the nature of the report, VBA isn't appropriate in this instance. I'm only producing the initial report; someone else will be updating it each month.

    Many thanks,


    Re: Countif For A Non Continuous Cell Range

    Thanks for everyone’s suggestions!!!!

    Unfortunately I'm still struggling to get the desired results...

    pike - your solution (the 1st one) will work and I think I will have to use it. It's a little tedious to enter, but once I've written it, it won't be a problem to maintain. (The formula I wrote was an example - in reality I have thirty columns of data to count!)

    Richard - the data in between each column does contain "#n/a" so I can't simply count the entire range.

    DWildman - I really like your solution but I need to drag the formula down 30 rows and as the cell references are in brackets they won't update when dragging down. So this is out.

    p45cal - I copied your code into a new module but when I put the user function into the cell it messed up my data, so I deleted it. I'm not sure what happened there!

    I think I'm going to go with the first solution simply because once it's done it's done.

    Thanks everyone!!!


    I want to count the number of cells with a “#n/a” in for a cell range which is non-continuous.

    For example my cell range is: “H5,J5,L5,N5,P5,R5,T5,V5".

    I’ve tried a few different things but I can’t get the function to work. Is this possible?


    I want to stop those little green error checking markers in the top corner of cells from appearing in my spreadsheet. My formula is correct however the error markers appear in 40(ish) cells and make it look very untidy.

    I know there is the option of turning background error checking off, however isn't appealing to me as I have other worksheets where I want to check for errors. Also, my report is used by 20 people so I can't change the options which might affect other reports they may have.

    What I'd like to do is either turn off the option for one specific worksheet, or add a few lines of code which sets the cell properties to ignore the error for each of the cells affected.

    I've tried to use macro recorder to work out how to ignore a cell but it doesn't record anything for this action.

    Is anyone aware of the correct piece of code to carry out this action?

    Many thanks,


    Re: Re-Define Name In Vba

    Hi Norie,

    Sorry, I took my actual data and deleted everything over than the part I wanted to get help with. I did check it but I missed something and forgot to update it to the example I was trying to show.

    I made the changes to the example to make it how I originally intended and suddenly the code worked.

    Looking over the code I see I missed out an "=" from the middle:

    ActiveWorkbook.Names.Add Name:="SalesManagers", RefersToR1C1:[COLOR="Red"]=[/COLOR]"=SalesManagersList!R2C1:R" & LastDataRow & "C1"


    I have a list of Sales Managers. Each month sales managers may be added to or be removed (Therefore the list shrinks and grows).

    When using my report, I have a drop down box which only allows valid Sales Managers from the Sales Managers list to be entered. (It's the inbuilt Data Validation rule).

    To make all this work I have a list of Sales Managers in one work sheet. I then defined the cells containing the names as "SalesManagers".

    This works fine for a month. However once a new manager joins I need to update my defined name so that it picks up the extra cell(s). I have some code already, so it would be very nice to simply add a few lines to update this change. I have added the code as follows:

    ActiveWorkbook.Names.Add Name:="SiteManagers", RefersToR1C1:"=SiteList!R1C1:R"& LastDataRow - 2 & "C1"

    However I get an error message: "Expected named parameter". It highlights "RefersToR1C1", so this is where the problem lies.

    Please could someone help me modify this line of code??!!!

    If you want I can attach an example?



    I've written some VBA which creates worksheets to temporarily store data in. At the end of the code I delete the worksheets. However, for some reason I am getting a message box which asks for confirmation. How can I avoid the user of my report having to click OK for each of the worksheets which need to be deleted?




    I have two tables of data. Each contains a column showing a date. I want my second table to show the maximum date from the first table which is less than or equal to the date from the second table.

    I have attached an example.

    I know I can do this with VBA but I'm still finalizing the layout of the data and don't have the time to write the VBA. If I could write some formula, it will be very convenient to simply amend it once I have my data in place.



    Re: Countif In Alternate Cells

    Okay, I'm 99% there.

    I need to change it from counting text to counting numbers. I tried removing the "T" function and the "+0" at the end but it gave me an error. What is the "+0" part at the end for?