Posts by Batman

    Re: Counting how many times a task takes between dates and then counting how many tim


    I'm struggling to understand why you need to get rid of the seconds, so I have put that to one side.

    Dates and times are stored in Excel as numbers: 1 day (or day 1 in its calendar) is stored as the number 1, 1 hour is stored as 1/24, 1 minute as 1/24/60, etc. So, to find a duration, you just deduct one number from another.

    If you really wanted to get rid of seconds, you would just round the number (or format the cell if just for display purposes), but I can't see how that is relevant to this exercise.

    In the attached, I have recalculated the duration to be in days, and added a categories table for lookup purposes. I have then used VLOOKUP with a final argument of TRUE to find the category for each item.

    I have also added a column to find the month of each item's completed date. Given the span of your dates, I assume you might want to consider adding 'year' as a field as well, otherwise Jan 2016 is going to fall into the same month as Jan 2015.

    The COUNTIFS function, to get your data to graph, is then relatively straightforward. I have added a helper row and column to make it easier, which you can hide if need be.

    Re: Counting how many times a task takes between dates and then counting how many tim

    Hi RJL3313,

    Welcome to the Ozgrid forum.

    Any date and time function in Excel will rely on the data being in number format. In the calculations in your 'CS Cust Iss OTY' worksheet you have converted all the data to text, making it unuseable for date and time functions.

    I assume that the data in the 'CS Cust Iss OTY' worksheet is not for user analysis, only as source data for the graph, so I would suggest that you remove any text functions in that sheet. If you want to change the way that the data in that sheet is displayed, use number formatting instead.

    Re: Wrong format in FIND box

    As cytop says, nothing in the code you have posted displays the Find dialogue box; I suspect the problem is in some part of the code that you have not posted.

    However, you will find that there are all sorts of problems when using dates in UK format in VBA. Almost invariably, VBA will try to treat dates in US format. It is quite likely that, when you think it is working on occasions, the problem is still there, but because the UK day number can be interpreted as a month number (i.e. <=12), the program will work but will give results based on an incorrect date.

    Re: Basic question

    Hi belpal,

    Firstly, please edit your thread title to be more relevant to the question, for example "Find data in varying columns". The thread title is used to enable others to search for problems similar to their own; a description such as 'basic question' does not help in any way.

    Please see the attached as a possible solution to your problem.

    Re: vba combox worksheet_activate crash

    Another thought - do you have any worksheet events active on Sheet2? Try using Application.EnableEvents to prevent the SetupHomeCombobox procedure from calling other programs.

    Re: vba combox worksheet_activate crash

    Try setting a breakpoint on the first actionable command

    Application.ScreenUpdating = False

    and using F8 to step through the code one command at a time. That should tell you which line of code is causing the problem.

    Re: vba combox worksheet_activate crash

    Hi chrishd,

    Welcome to the Ozgrid forum.

    Without seeing exactly how your programs are structured, I would suspect that the problem relates to the code line


    If you are calling the SetupHomeCombobox procedure from a Worksheet_Activate event, that line of code is going to invoke the same Worksheet_Activate code again before it has finished running.

    I would suggest that you declare and set a worksheet variable in the SetupHomeCombobox procedure, and avoid using any code that uses .Select, Selection or ActiveSheet.

    I would also avoid the 'With MCC' inside the 'With ActiveSheet' code as this is also likely to cause problems.

    Hope this helps.

    Re: Concatenate Values Based on Conditions

    Re-reading my last post, I've just spotted that it won't work in that form. I shortened by original code too much.

    With Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1)

    Re: Concatenate Values Based on Conditions

    Hi RVADataMonkey,

    Welcome to the Ozgrid forum.

    Try something like this:

    Re: Linking Excel and Outlook in Citrix

    Hi Cytop,

    Many thanks for the response.

    I don't have the ability to log on to Citrix myself, it's our outsourced business partners in India who are having the problem. I will ask them to try your suggestion and see what happens.

    As far as I am aware, the error message they were seeing was the same as the one in the test, i.e. error 429 "ActiveX component can't create object". The original code, written by someone else in the company, actually used 'CreateObject' rather than 'GetObject', but the result was the same with both sets of code.

    If it's going to be a difficult technical issue to overcome, I will simply write two sets of code - the first to run in Excel and output a text file, the second to run in Outlook and read and action based on the text file data. I'm hoping that will work.

    Re: SUMIFS Multiple Criteria between Two Date Ranges

    Hi dpspet,

    Welcome to the Ozgrid forum.

    I have a problem in that I can see dates in both UK and US format. I suspect that might be down to my version of Excel, being in the UK, so I will assume that you see them all in US format.

    If that is the case, the main issue is that the dates created by your formula in column N of Sheet 2 result in a text value that looks like a date, rather than actually being a date. You need to wrap a date creation function (e.g. DATEVALUE) around what you currently have in order to turn it into a date so that it can be compared with the dates in Sheet 1.

    You could put the DATEVALUE function into your extract formula, in which case SUMPRODUCT would probably work better than SUMIFS:

    =SUMPRODUCT('Analytics All Web Site Data All'!$L$8:$L$140,'Analytics All Web Site Data All'!$M$8:$M$140=$A$9,--(DATEVALUE('Analytics All Web Site Data All'!$N$8:$N$140)>=$A11),--(DATEVALUE('Analytics All Web Site Data All'!$N$8:$N$140)<=$A11))

    That doesn't work for me in your workbook because of the different date formats, but it might work for you.

    Alternatively, if you put the DATEVALUE into the formulas in column N of Sheet 2, you wouldn't need it in the extract formula, in which case either remove it from the formula above, or your SUMIFS might work (I haven't attempted to look at the syntax of that formula).

    Perhaps a bit confusing, but whatever you do, you will certainly need to somehow convert the 'text dates' in column N of Sheet 2 into a proper number/date format.

    Hi all,

    Our partners in India are unable to run on our behalf a number of Excel-based macros, using Outlook to create e-mails, apparently due to the fact that they are working in a Citrix environment. As I don't have access to a Citrix environment, in which to try to replicate and resolve the problem, I'm looking to understand whether Citrix introduces any restrictions on linking applications in VBA.

    I have given our partners this code to run:

    This results in the display of error 429 "ActiveX component can't create object" even though Outlook is open at the point of running the macro. I can replicate the problem in Windows (Excel 2013 / Office 365) if I close Outlook, but the test works fine if I have Outlook open.

    I can potentially give our partners separate Excel and Outlook applications to run, but would prefer to combine them in one process if possible, so any insights into potential Citrix-specific issues would be appreciated.

    Re: VBA, Advanced Filter

    It's not necessarily hard, just difficult without being able to see the format of the data that you are reading, what additional data you want to create, and where, and where you want all the results to go.

    Provided that you understand what all your code is doing, it should only be a question of you deciding which bits to action, and in which order, to get to the desired result. But only you should decide what you want the result to be, not me.

    I might be able to have a look at this, but I would need to see the format of the various files your are reading, what additional data you want to create and where you want to put it, and exactly what the format of the output needs to be. Also, I have very little time over the next week or two and can't guarantee to be able to look at it for a while - it is only by chance that I logged on to the forum and saw your latest post.

    Re: VBA, Advanced Filter


    What part of the process are you having problems with?

    I assume that, as you are introducing new data into your worksheets, this will change the layout of your data and also where you want to place the results, neither of which I can define for you.

    As you have all the logic you need, I would suggest that you copy the code within the worksheet loop to where you think it best fits, and then put a breakpoint in the program and scroll through the program one line at a time (F8) and check whether it is doing everything in the correct order, and based on the right data. You can then just keep amending the column references, or other variables, and re-run the process until it does what you want.

    By the way, I would suggest that you replace the 'ActiveWorkbook' object with the 'wb' variable. It is much more reliable, and there seems to be little point in assigning the 'wb' variable only to then ignore it in favour of the less reliable 'ActiveWorkbook..

    Re: Using one formula detect duplicates


    I will answer this one, but no more in this thread. New questions should be raised in their own thread.

    There are probably several different ways to do this, but one way is this formula, filled down (assumes your data starts in cell A1):


    Re: Taking time to execute while searching and selection.

    Hi ahs_786,

    Welcome to the Ozgrid forum.

    Your problem is with the use of SpecialCells with a range equal to the entire column, especially when no cells are hidden by filtering. You are also looping through every row in the sheet, which is over 1 million loops.

    Without changing the basis of how your program works too much, try the following, although I'm not sure which columns it is intended to select: