# Posts by Averilp

• ## Separate Numbers From Text

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

• ## Calculate Filtered Results

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

• ## Calculate Filtered Results

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

• ## Calculate Filtered Results

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

Regards,
Averil

• ## Run Macro When Workbook Closes

Re: Beforeclose Event

Hey there,

Put the code in the ThisWorkbook section, and not the code for an individual sheet.

Cheers,
Averil

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

• ## Add Controls To Right Click Menu Based On Range List

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.

Regards,
Averil

• ## Prevent (blank) in PivotTables

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

• ## Inserting Tabs In Mailing Labels

Re: Inserting Tabs In Mailing Labels

Are you still struggling with this? Can you upload a sample of what the merge doc looks like at the moment?

Cheers,
Averil

• ## Sum Across Unknown Number of Worksheets

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

• ## Open Word Via Hyperlink and Resize Window

Re: Opening And Resizing A Window

http://support.microsoft.com/kb/213566

Cheers,
Averil

• ## Open Word Via Hyperlink and Resize Window

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

• ## Identify Duplicate Rows Between 2 Worksheets

Re: Identify Duplicate Rows Between 2 Worksheets

Dave,

Works like a dream. Thanks again!

I love this forum - I was getting withdrawl symptoms when on holiday :yourock:

Cheers,
Averil

• ## Identify Duplicate Rows Between 2 Worksheets

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

• ## Identify Duplicate Rows Between 2 Worksheets

Re: Identify Duplicate Rows Between 2 Worksheets

Hmmm that's the formula I did have in there after your earlier advice (I tried as array because of the note above the formula you posted :-))

I have attached the file with an example of when it is failing.

Cheers,
AP

• ## Identify Duplicate Rows Between 2 Worksheets

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

• ## Identify Duplicate Rows Between 2 Worksheets

Re: Identify Duplicate Rows Between 2 Worksheets

Thanks Dave,

Seems to work perfectly. You are a legend!

Thank to Bill also for your contribution

Regards,
Averil

• ## Identify Duplicate Rows Between 2 Worksheets

Re: Identify Duplicate Rows Between 2 Worksheets

Hi Dave,

Please find an extract of the data for you.

Thanks again for having a look!

Regards,
Averil

• ## Identify Duplicate Rows Between 2 Worksheets

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

• ## Identify Duplicate Rows Between 2 Worksheets

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