Posts by Batman

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

Glad to have been of help.

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

Hi,

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.

Sub SetupHomeCombobox() 
    Dim ws1 As Worksheet 
    Dim MCC As Object 
     
    Application.ScreenUpdating = False 
    Application.EnableEvents = False
    Set ws1 = Worksheets("Sheet1") 
     
    With ws1 
        .Shapes.Range(Array("MenuClassChoice")).Delete 
         
        Set MCC = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _ 
        DisplayAsIcon:=False, Left:=52.5, Top:=65.25, Width:=100.5, Height:=24.75) 
    End With 
     
    With MCC 
        .Object.Font.Name = "Calibri" 
        .Object.Font.Size = 14 
        .Object.Font.Bold = False 
        .Name = "MenuClassChoice" 
        With .Object 
            If Sheet2.Range("D8") = "Y" Then .AddItem Sheet2.Range("H48") 
            If Sheet2.Range("D9") = "Y" Then .AddItem Sheet2.Range("H49") 
            If Sheet2.Range("D10") = "Y" Then .AddItem Sheet2.Range("H50") 
            If Sheet2.Range("D11") = "Y" Then .AddItem Sheet2.Range("H51") 
            If Sheet2.Range("D12") = "Y" Then .AddItem Sheet2.Range("H52") 
            .ListIndex = 0 
        End With 
    End With 
    Application.EnableEvents = True
End Sub

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

Sheets(1).Select

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.

Sub SetupHomeCombobox() 
    Dim ws1 As Worksheet
    Dim MCC As Object 


    Application.ScreenUpdating = False 
    Set ws1 = Worksheets("Sheet1")
     
    With ws1
        .Shapes.Range(Array("MenuClassChoice")).Delete 
     
        Set MCC = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _ 
            DisplayAsIcon:=False, Left:=52.5, Top:=65.25, Width:=100.5, Height:=24.75) 
    End With


    With MCC 
        .Object.Font.Name = "Calibri" 
        .Object.Font.Size = 14 
        .Object.Font.Bold = False 
        .Name = "MenuClassChoice" 
        With .Object 
            If Sheet2.Range("D8") = "Y" Then .AddItem Sheet2.Range("H48") 
            If Sheet2.Range("D9") = "Y" Then .AddItem Sheet2.Range("H49") 
            If Sheet2.Range("D10") = "Y" Then .AddItem Sheet2.Range("H50") 
            If Sheet2.Range("D11") = "Y" Then .AddItem Sheet2.Range("H51") 
            If Sheet2.Range("D12") = "Y" Then .AddItem Sheet2.Range("H52") 
            .ListIndex = 0 
         End With 
    End With 
End Sub

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,

If you wanted a relatively simple VBA change, try this:

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

Re: Concatenate Values Based on Conditions

Hi RVADataMonkey,

Welcome to the Ozgrid forum.

Try something like this:

Sub test()
    Dim a, i As Long, n As Long, rOut As Range
    
    Set rOut = Worksheets("Sheet2").Range("H3")
    With Range("a1").CurrentRegion
        a = .Value
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(a, 1)
                If Not .exists(a(i, 1)) Then
                    n = n + 1
                    a(n, 1) = a(i, 1)
                    a(n, 2) = a(i, 2)
                    .Item(a(i, 1)) = n
                Else
                    a(.Item(a(i, 1)), 2) = a(.Item(a(i, 1)), 2) & ", " & a(i, 2)
                End If
            Next
        End With
        rOut.Resize(n, .Columns.Count).Value = a
    End With
End Sub

Re: SUMIFS Multiple Criteria between Two Date Ranges

You're welcome!

It was a bit late last night when I replied, and it felt as though my answer was going round in circles. I'm glad it helped.

Re: Linking Excel and Outlook in Citrix

That's extremely helpful information. Many thanks for the input.

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:

Option Explicit


Dim objOutlook As Object
Dim strErrorMsg As String


Public Sub sTestOutlookUnderCitrix()
    ' System settings, just in case...
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    ' Set the error trap
    On Error Resume Next
    
    ' Try to find the Outlook application
    Set objOutlook = GetObject(, "Outlook.Application")
    
    ' Check for results
    If Err.Number <> 0 Then
        ' If there is an error, display the error result in the worksheet
        strErrorMsg = "Error in assigning Outlook session." & vbCrLf & vbCrLf & "Error number:  " & Err.Number & vbCrLf & vbCrLf & _
            "Error description:  " & Err.Description
            
        Err.Clear
        On Error GoTo 0
        Range("B5").Value = strErrorMsg
    Else
        On Error GoTo 0
        
        ' If there is no error, display a success message in the worksheet
        Range("B5").Value = "Test successful; Outlook session recognised."
        
        ' Remove the assignment of the Outlook session
        Set objOutlook = Nothing
    End If
    
    ' Reset system settings
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

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

Hi,

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

Hi,

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

=RIGHT(A1,LEN(A1)-FIND("X",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:

Private Sub CommandButton1_Click()
    Dim lngCounter As Long, rngSelection As Range, lRow As Long


    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range(Cells(1, 1), Cells(lRow, 4))   '.SpecialCells(xlCellTypeVisible)
        For lngCounter = 1 To lRow
        If .Cells(lngCounter, 1).Value = Me.TextBox2.Text Then
                If Not rngSelection Is Nothing Then
                    Set rngSelection = Union(rngSelection, Intersect(.Rows(lngCounter), .Rows(lngCounter).Offset(, 1)))
                Else
                    Set rngSelection = Intersect(.Rows(lngCounter), .Rows(lngCounter).Offset(, 1))
                End If
            End If
        Next lngCounter
        
    End With


        If Not rngSelection Is Nothing Then
            rngSelection.Offset(0, 5).SpecialCells(xlCellTypeVisible).Select
        Else: MsgBox "not Found"
        End If


End Sub