Posts by Averilp

    Re: Separate Numbers From Text


    Hi there,


    Assuming that you will always want to get the last 5 characters, and assuming that your value is in A1, use this in B1:


    =RIGHT(A1,5)


    Where 5 is the number of characters you want to display.


    Cheers,
    Averil

    Re: Calculate Filter Results


    Welcome to the new working week :whip:


    The weekend was blooming hot this way - I believe it got to about 33 yesterday!


    Thanks again for your help. Using the wonderful assistance from members of this forum, most particularily yourself and your tutorials, I have managed to get copious amounts of code and lots of looping and redundant select/activate down to about two printed pages!


    Cheers,
    Averil

    Re: Calculate Filter Results


    Thanks Dave!


    I spent 2 days trying to get my head around it, and as ever, you have hit the nail on the head with seemingly little effort. I'm still on my L plates when it comes to VB :)


    Have a great night.
    Averil

    Hi there,


    I've searched and can't seem to find anything that quite fits.


    I am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out.


    I get the error "Unable to get the Sum property of the WorkSheet function class"


    This is the code I have butchered:


    Code
    Set R = ActiveSheet.Range("d1").CurrentRegion
        Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)
        For i = 1 To R.Rows.Count
            If Not R.Rows(i).EntireRow.Hidden Then
                 intCountCancelled = WorksheetFunction.Sum("Cancelled")     ' cancelled is a named range. This is the line its failing on
            End If
        Next i


    Thanks in advance.


    Regards,
    Averil

    Re: Add Controls To Right Click Menu Based On Range List


    Thanks Dave, you rock!


    Works perfectly.


    Regards,
    Averil[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Dave,


    Sorry again. I've incorporated your code and decided to do similar to temporarily remove the existing right click options that I don't want included by having a named range. It seems to fail on the options with ... (e.g. Paste Special...). Any ideas?


    Code
    With Application.CommandBars("Cell")
            For Each rCell In Range("RightClickRange") 'Dynamic Named Range
                strRightClickName = rCell
                Application.CommandBars("Cell").Controls(strRightClickName).Visible = False
            Next rCell
        End With


    Thanks again - you are amazing!


    Cheers,
    Averil

    Hi there,


    I have customised the right click menu's for this spreadsheet and currently it is all hard coded. I was wondering if it is possible to dynamically modify the right click menu?


    The code currently has right click buttons for each staff member, and when clicked other actions are performed. I have added a "Staff" sheet and was wondering if there is a way the code can reference that sheet and create the list based on those entries for example, when new staff join or other staff leave?


    I'm sure that there is a better way to go about this, perhaps a For... Next loop but I don't know enough about it.


    I appreciate anyone having a look. In the mean time, I will keep bashing away at it in hopes of a brainwave... it's a Friday before a long weekend and I think my brain has decided its holiday time.



    For your info, I will change the .OnAction to be generic and that code will pick up which button was clicked.


    Thanks in advance.


    Regards,
    Averil

    Re: Prevent (blank) in PivotTables


    I agree, pivot data shouldn't have blank entries. However, I suppose the other band-aid would be to search for those blanks in the data source, and replace with a space???


    Averil

    Re: Sum Across Unknown Number of Worksheets


    You're too quick by half Dave - was about to suggest the same myself :smile: .


    I too would suggest having a sheet with all the raw data and then making use of Excel's commands such as Sort, AutoFilter, Subtotals, Consolidate, and PivotTable. You can save yourself a lot of aggravation by properly setting up the data to begin with.


    Regards,
    Averil

    Re: Opening And Resizing A Window


    Hey there,


    I would just hyperlink to the document as per normal, but put this code in a new module of the Word document. Change sizes as you see fit :)


    Code
    Private Sub Document_Open()
    
    
        With Application
            .WindowState = wdWindowStateNormal
            .Width = 300
            .Height = 300
        End With
     
    End Sub


    Cheers,
    Averil

    Re: Identify Duplicate Rows Between 2 Worksheets


    Thanks Dave,


    I apologise for not reading the "Not" part of the message correctly - its my first day back from holiday and my brain is in overload.


    I do not have the master spreadsheet with me at home now so I will try your formula tomorrow and let you know how it goes. It works fine on the small sample I posted so I think it should be good... Hurrah!


    I had played a bit with VBA to combine each row one at a time in the Original sheet into a string and then loop through each row in the other sheet, combine into a string etc and see if x = y, but it took forever to run even one row so gave up since I knew there had to be a way to do it via formulas.



    Bill,


    I appreciate your thoughts and comments. I growled at the person who created the spreadsheet about not putting some kind of identifier for each entry, given that some can be so similar... and that we would have to cross reference them later.


    Thanks for all your help... I bow to the gods of Excel!


    Regards,
    Averil

    Re: Identify Duplicate Rows Between 2 Worksheets


    Dave,


    Sorry to be a pain, just noticed that I am still getting #VALUE!.


    Also, when I enter the formula as an array (e.g. with Ctrl + Shift + Enter) all answers are set to TRUE for the 4500+ rows in the real "Original" sheet when there are only 1500 or so rows in the "Updated" sheet.




    Bill,


    Yes, this spreadsheet wasn't set up in the best manner so there is a lot of duplicate information. I imagine that this could be causing the errors?


    Regards,
    Averil

    Re: Identify Duplicate Rows Between 2 Worksheets


    Hi Dave,


    Thank you for responding so quickly, and correcting my thread title.


    I have tried concatenating each cell on each row on both sheets as suggested then using Match and I still get #VALUE! error. Could it possibly be because the end text is longer than 255 characters?


    After testing it combining only two columns and using Match it seems to work, unfortunately I do need it to find a match using all of the cells.


    Regards,
    Averil

    Hi there,


    I have been searching this forum and tried some of the suggestions but they don't seem to apply to me.


    I have two sheets with many rows and about 8 columns. The second sheet is some of the rows copied and pasted from the first sheet. I have been asked to mark on the first sheet those rows that have been copied to the second sheet.


    I need to check that the entire row matches before somehow making the corresponding row in the "original" sheet stand out.


    Unfortunately, there is no unique identifier that I can search by. I have tried concatenating all columns into a new column on each sheet and then using MATCH but I get #VALUE! error.


    Any help would be much appreciated.


    Regards,
    Averil pretty