VBA/Macro to count number of times the same record appears in different sheets within WBK

**Hackboss007** · 02-18-2014, 10:12 AM

Hi everyone!

I'd like to create a macro to find the number of times a bunch of records within tables appear in subsequent sheets of the workbook and then be able to divide it by the total number of sheets. The reason for doing so is to be able to calculate the percentage that these records appear within the workbook. However, I'd like to eliminate the first 3 sheets as these are templates for the remaining sheets. So from the 4th sheet onwards I'd like to be abe to count to the last sheet. The records are within 3 different tables on a single sheet. From the 4th sheet onward, all sheets have the same layout/template (if it makes it easier to code in anyway). Any help would be very much appreciated! Thanks!

**Olly** · 02-18-2014, 10:38 AM

Attach a sample workbook, showing your data structure, examples of the data you wish to count, and what your required outcome looks like.

**Hackboss007** · 02-18-2014, 11:09 AM

Hi Olly XLS!

The sample workbook has been attached. This is the 3rd sheet of the workbook and the records I've mentioned about are on the 3rd page onward. The first table starts from "Left/Right wing structure". This same layout will be used for subsequent sheets. However, not all of the items mentioned in the table will be used for every aircraft. Some might require all and others might need only some of the repairs but all of the values entered will be from this master table. I want the macro to be able to count the number of times each of the records occurs in the subsequent sheets and then divide it by the total number of sheets (minus the first 3) and then paste it in a cell designated by me on one of the first 3 sheets. Hope this helps with undestanding the problem better. If you need any additional clarification, please let me know. Thanks

**Olly** · 02-18-2014, 11:29 AM

Try this UDF:

Function PartsPerSheet(PartNum As Variant)
Dim i As Integer, lPartCountWS As Long, lPartCountWB As Long, iSheetCount As Integer
On Error GoTo ErrCatch
Application.Volatile
For i = 4 To Worksheets.Count
    lPartCountWS = WorksheetFunction.CountIf(Worksheets(i).UsedRange, PartNum)
    If lPartCountWS > 0 Then
       lPartCountWB = lPartCountWB + lPartCountWS
       iSheetCount = iSheetCount + 1
    End If
Next i
If iSheetCount = 0 Then
    PartsPerSheet = 0
Else
    PartsPerSheet = lPartCountWB / iSheetCount
End If
Exit Function
ErrCatch:
    PartsPerSheet = CVErr(xlErrRef)
End Function

Use as:

Formula:

=PartsPerSheet('Survey Template'!A101)

Note that it will only work on the first three sheets - use on sheet 4 onwards will result in a circular reference.

**Hackboss007** · 02-18-2014, 12:15 PM

Hi Olly XLS!

Thank you for the response. I have not tried it yet but will be trying it soon. Would this macro work for the cells designated by me as I would like to paste the count divided by the number of sheets value in different cells for each part. That way I'll be able to create a graph citing that 25% of the fleet has the "5122001-17" part added to it and then 75% of the fleet has "5122011-21" part added to it, etc. Thanks for any help you can provide. I will let you know if I encounter any problems.

Thanks!

**Olly** · 02-18-2014, 12:26 PM

It's a function which accepts anything as an argument, so you can use it anywhere in Sheets 1-3, with any part number.

Can we be clear on the definition though - at the moment, it counts the number of occurrences of a part number, and divides that by the number of sheets in which that part number was found.

That's not % of fleet, that's parts per aircraft which used that part.

For % of fleet, try the following:

Function FleetPercent(PartNum As Variant)
Dim i As Integer, lPartCountWS As Long, iSheetCount As Integer
On Error GoTo ErrCatch
Application.Volatile
For i = 4 To Worksheets.Count
    lPartCountWS = WorksheetFunction.CountIf(Worksheets(i).UsedRange, PartNum)
    If lPartCountWS > 0 Then
       iSheetCount = iSheetCount + 1
    End If
Next i
FleetPercent = iSheetCount / (Worksheets.Count - 3)
Exit Function
ErrCatch:
    FleetPercent = CVErr(xlErrRef)
End Function

This counts the number of sheets on which the part WAS used, and divides it by the total number of sheets which exist (-3, to ignore your template sheets). I guess I'm assuming that you have one worksheet per aircraft.

Use as

Formula:

=FleetPercent('Survey Template'!A101)

**Hackboss007** · 02-18-2014, 12:45 PM

Hi Olly XLS!

Your undestanding is correct. I will be using one sheet for each aircraft and yes it is to find the occurrences of a part number and divides it by the number of sheets it was found. Thanks! I will let you know if it works.

**Hackboss007** · 02-18-2014, 01:34 PM

Hi Olly XLS!

I tried the PartsPerSheet function and created 4 sheets. I then entered a part number "5122008-17". I used the PartsPerSheet UDF to reference the cell where I enterd the part number and it came up with the answer 1. However, the middle 2 sheets didn't contain the record. So I assumed it was only searching the first sheet. Even after deleting the record from the first sheet and including it on the second sheet instead. It still shows 1 instead of 0.5, since 2 of the sheets contain the record out of the 4. Any help you can provide is very much appreciated. Thank you for the time and dedication to respond to my queries!

Regards

**Olly** · 02-18-2014, 02:00 PM

Okay, we're not clear on definition, are we?

Let's define the following:

PartCount = Total Number of matching Part Numbers found on sheet 4 onwards
SheetCount = Number of Sheets on which the matching Part Number was found, at least once
AllSheets = Number of Worksheets, excluding templates (1:3), regardless of whether the matching Part Number is on that sheet or not.

Using those three definitions, what do you actually want the measure to be?

As above:
The PartsPerSheet function returns PartCount / SheetCount
The FleetPercent function returns SheetCount / AllSheets

Is either of those your requirement, or did you want something else?

**Hackboss007** · 02-18-2014, 02:26 PM

Hi Olly XLS!

The FleetPercent function works perfectly! That's what I needed. Since a particular part number will only occur once on a sheet, the PartsPerSheet Function will always return 1. However, the FleetPercent function does the trick. Thanks so much!

Regards

**Olly** · 02-18-2014, 02:28 PM

Excellent, glad it does what you needed. Thanks for the feedback. Apologies for the confusion over definitions!

VBA/Macro to count number of times the same record appears in different sheets within WBK

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA/Macro to count number of times the same record appears in different sheets within WBK

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Re: VBA/Macro to count number of times the same record appears in different sheets within

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count the number of times a value appears across multiple sheets displaying a total

[SOLVED] Count the number of times an A appears 5 or more times consecutively

How to count the number of times something appears?

[SOLVED] Count Number of Times Something appears

[SOLVED] Search text & record number of times it appears

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions