Free Microsoft Excel 2013 Quick Reference

Making show numbers only Results

Hi All,

I am trying to make this formula works so that it performs on th e previous tworksheet a similar check to the one it is currently performing on the same worksheet.

I currently use this code.

Lr = Range("A" & Rows.Count).End(xlUp).Row 
If ActiveCell.Value = "" Then 
    Selection.FormulaArray = _ 
    "=IF(RC[-9]=""DEBIT"",IF(MAX(IF(R2C3:R" & Lr & "C3 =RC[-14],IF(LEFT(R2C8:R" & Lr & "C8,3)=""INT"",IF(R2C13:R" & Lr &
"C13=RC[-4], IF(R2C8:R" & Lr & "C8"""",ROW(R2C13:R" & Lr & "C13),0),0),0),0))=0,"""",""COR""),"""")" 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code works, it performs the check on the same table.
Now I am trying to make it work so that it checks for the array in the previous table.
I tried:

Lr = Range("A" & Rows.Count).End(xlUp).Row 
Set ws1 = Worksheets(Worksheets.Count) 
Worksheets(Worksheets.Count - 2).Activate 
ws2 = ActiveSheet.Name 
Lr2 = Range("A" & Rows.Count).End(xlUp).Row 
If ActiveCell.Value = "" Then 
    Selection.FormulaArray = _ 
    "=IF(RC[-9]=""DEBIT"",IF(MAX(IF('" & ws2 & "'!R2C3:R" & Lr2 & "C3 =RC[-14],IF(LEFT('" & ws2 & "'!R2C8:R" & Lr2 &
"C8,8)=""INT"",IF('" & ws2 & "'!R2C13:R" & Lr2 & "C13=RC[-4], IF('" & ws2 & "'!R2C8:R" & Lr & "C8"""",ROW('" & ws2 &
"'!R2C13:R" & Lr & "C13),0),0),0),0))=0,"""",""COR""),"""")" 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But it doesnt work.
It shows the following msg "Unable to set the fomula array property..."

I have also tried to do on excel the formula, but once i select the range in the previous sheet, the formula dont work.

Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =IF(H14="DEBIT",IF(MAX(IF('Transactions 30-04'!C:C=C14,IF(LEFT('Transactions 30-04'!$H$2:$H$154,8)="INT",IF('Transactions 30-04'!$M$2:$M$154=M14,IF('Transactions 30-04'!$H$2:$H$154"",ROW('Transactions 30-04'!$M$2:$M$154),0),0),0),0))=0,"","COR"),"") 

What am I doing wrong here?
Thanks for the help!


I come again to this excellent forum to ask a question, this time on pivot tables.

How can I make a calculated field that can refer to a previous value in the table? That is to say, if you have values grouped by e.g. weeks of the year as columns, then in the column for week 2, I want to be able to include in a calculated field a derivation of the value from week 1.

Perhaps the attached file will make more sense!

I have several branch locations - N, S, E and W. They all run events every week (col b) that have an "existing" amount of business and a "new" amount.

I want to pivot to track this over time as per the attached. You'll see I have a successful basic pivot showing New and Existing per week.

What I need to do is add a column next to the New and Existing - presumably a calculated field - where the sum for week "n" is

=(week N existing - week N new)/(week n - 1 existing)

I saw an article at which gave me an idea that

"It turns out you can use a special syntax to refer to "next" and "previous" items in a field.

In this example, the PivotTable author had actual financial results for FY02 FY04, and wanted to create a forecast for the next fiscal year based on the actual for those three years. You can see in the screenshot above that they used the following formula: =IF(Year[-1]=0,Year[-2],2*Year[-1]-Year[-2])."

But it looks like from experimenting that that syntax only works with calculated *items*, not calculated fields that I need.

I tried various formula including the [-1] syntax, and playing with using a dummy field set as a custom calculation type "difference from" but got nowhere. On the latter, it seems even though I can get it to display the differences, trying to calculate on that includes the underlying whole number, not the displayed difference. Lookup functions like GETPIVOTDATA are not apparently allowed within a pivot table itself.

The dataset is going to be pretty huge and changing regularly, so wanted to avoid array formulae and manual paste-values etc. would be a pain. I know how to do it via array formulae, but it is hugely slow and inflexible.

Does anyone know if it's possible? Huge gratitude if so! Am using Excel 2003.

Thanks, do let me know if the question isn't clear!


This is very odd. I cannot find Excel files that contain specific strings.
For example, if I create a new file, and in the first cell type in "123456".
Then save the file to the folder c:Temp
Then in Windows Explorer, right-click on C:Temp and select "Search . . ."
Then under "A word or phrase in the file", type in: 123456
It will NOT find the file ! ! It will find a txt file or a doc file, but not xls files.

I thought maybe it fails because that is a number.
But I have hundreds of Excel files with Integer numbers stored in "General" format cells and I desperately need to be able to search them to see which files contain certain numbers.

It all started with some files that were sent to me - they work PERFECTLY for searching cells for numbers. So strange - yet if I open the file then save it . . . no changes or edits at all, just Open and Save . . . then the search fails for all numbers in that file.

This is so perplexing that I have placed 2 zip Sample files on my website for you to Download

Unzip the two files to any folder.
One is named "Sample-Works.xls" - Then do a Windows Search for any number in the file, here is one to try that is in the two files: 174026
And it will list "Sample-Works.xls" but it will NOT list "Sample-Fails.xls", even though BOTH FILES ARE IDENTICAL !! I copied the same file twice and then named them. The only difference is that I opened the file Sample-Fails.xls and then immediately Saved it, which as I stated earlier, makes it fail all Searches from that point forward.

Also please try this - using Windows Explorer, copy the good file and then redo the Search. You will see both the good files show up as having "174026". Then open the copy you made and save it - now that file will fail to show up in the Search ! !

I did try this on my other PC, by the way, and the same Problem exists.
I also tried numerous Search Utilities (Agent Ransack, File Boss, PowerDesk File Finder, Turbo Search, etc) and they all have the SAME problem. There is simply no way to Search an Excel file for a number unless it is one of those files that were emailed to me a while back.

Any ideas? Anyone know how I can search Excel files and find the files that contain specific Integers ? TIA


Custom Formats in Excel Format>Cells>Number-Custom are perhaps one of Excels most underutilized features. This is normally due to not understanding the construct of a Cells Format. There was also the introduction of Conditional Formatting in Excel 97.

A cells format is made up from 4 different part known as "Sections" They are, from left to right

Format for positive numbersFormat for negative numbersFormat for zero valuesFormat for text
The semi-colon ; is what is used to seperate these Sections. For example a Custom Format of:


Would stop any text that is entered in that cell from being displayed. We could replace General with any format, e.g 0.00etc

In this case it is the use of the lastSemi-Colon (in the Text Section) that is preventing text from being seen.

If we used the format:


Nothing would show Except error values like #VALUE!, #N/A etc. We need to know about these

Let's suppose we have a cell that we only want to show when/if the number is positive, all negative numbers should not be seen. The positive numbers should also only show 2 decimal places. We could use


If it were zeros we want hidden we could use:


One very good way to understand how Excels formats work is to format any cell as Curreny with negative being red and preceded with a negative sign and 2 decimal places (optional). Now select Custom and you should see


As you can see, Excel is being told to make all negatives red ([Red]) and preceded with the negative sign. We can easly change this, so that, it stays red but uses parenthesis rather than the negative sign, i.e


As you can see, that while Custom formats can be confusing, they become far less confusing once we have grasped the concept of Sections

i have a database of monthly sales of a regional distributor and im using a pivot table to manipulate my data.

i want to show only what account makes a positive sales for a specific product and i want to get the total number of accounts who have the positive sales. there are multiple entries in my database, the condition is if an account have positive sales of a specific product regardless on how many times the account bought the result should be 1 (meaning one buying account)

i've tried to use a calculated field in my pivot table, i get the correct result per account but when it comes to grand total its not getting the sum of the total number of accounts

here is the sample of what i need to get..

you can see that there are multiple entries but it shows only 1 per account when it has at least 1 positive sales of a specific product and 0 if it hasn't any sales/negative... and for the total it adds the number of accounts to get the total number of buying accounts..

Hi everyone....I have some code that I have written (well actually most of it was done by people on this forum who have helped out!) but I am stuck...
If you see the attached sheet, the first worksheet shows data in its original form. When the user presses the button (which calls the code below) a new table is generated on Sheet 2 which should present the data in a different table.. It is supposed to put all classroom bookings in my chart. However because some of the dates are the same (even though for different classroms) it is overwriting and therefore only some of the bookings appear.....does that make sense????
Here is the code:

    Dim c As Range, Srng As Range 
    Dim i As Integer, startcol As Integer, endcol As Integer 
    Dim startdate As Date, enddate As Date 
    Dim rowno As Long 
    Dim ws As Worksheet 
    Dim legend As Range, f As Variant 
    Set legend = Range("Legend") 
    startdate = Application.Min(Range("B3", Range("B" & Rows.Count).End(xlUp))) ' this searches for the smallest start date
    enddate = Application.Max(Range("C3", Range("C" & Rows.Count).End(xlUp))) ' this searches for the largest end date date
    Set ws = Worksheets("AvailabilityChart") 'choose Sheet you want the table
    Set Srng = ws.Range("A3") 'choose where you want the table here
    Srng.Offset(, 1).Value = Format(startdate, "D-MMM") 'this writes the smallest start date
     'then it goes through the following   loop to write all dates up to the largest end date
        i = i + 1 
        Srng.Offset(, i + 1).Value = Format(startdate + i, "DD-MMM") 
    Loop Until Srng.Offset(, i + 1).Value = enddate 
     'writes the room numbers down the side for existing bookings
    Range("D3", Range("D" & Rows.Count).End(xlUp)).AdvancedFilter _ 
    Action:=xlFilterCopy, CopyToRange:=Srng.Offset(1), Unique:=True 
     'this loop goes through and enters all bookings in the   chart
    For Each c In Range("E3", Range("E" & Rows.Count).End(xlUp)) 
        rowno = Application.WorksheetFunction.Match(c.Offset(, -1).Value, ws.Range(Srng.Offset(1) _ 
        , ws.Cells(Rows.Count, Srng.Column).End(xlUp))) + Srng.Row 
        startcol = (c.Offset(, -3).Value - startdate) + Srng.Column + 1 
        endcol = (c.Offset(, -2).Value - startdate) + Srng.Column '+ 1
         'Copy name and then color as appropriate
        With ws.Cells(rowno, startcol).Resize(1, (endcol - startcol) + 1) 
            .Value = c.Value 
            f = Application.Match(c.Offset(, 3), legend, 0) 
            If IsNumeric(f) Then 
                .Interior.ColorIndex = legend.Cells(f).Interior.ColorIndex 
            End If 
        End With 
    Next c 
     'puts borders all around the chart and in between the rows
    With Srng.CurrentRegion 
        .Borders(xlEdgeTop).LineStyle = xlContinuous 
        .Borders(xlEdgeTop).Weight = xlMedium 
        .Borders(xlEdgeLeft).LineStyle = xlContinuous 
        .Borders(xlEdgeLeft).Weight = xlMedium 
        .Borders(xlEdgeBottom).LineStyle = xlContinuous 
        .Borders(xlEdgeBottom).Weight = xlMedium 
        .Borders(xlEdgeRight).LineStyle = xlContinuous 
        .Borders(xlEdgeRight).Weight = xlMedium 
        .Borders(xlInsideVertical).LineStyle = xlContinuous 
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous 
    End With 
     'formats of the table
    Set Srng = ws.Range("A2:IV2") 
    Srng.Font.Bold = True 
    Set Srng = ws.Range("A1:A100") 
    Srng.Font.Bold = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Does anyone know what the problem is? I am a complete novice to VBA!!!!

I am sure that there is a way to do this but I don't know enough VBA to make this work. I would REALLY appreciate it if some kind soul out there would help me.

I work at a hotel, and every day I have to hand-write coupons for breakfast for each of our guests who arrive for certain local accounts that we have, and I need a separate coupon for each day they are staying. As you can imagine, this is very time consuming.

I had a similar situation a few months ago with guests who arrived and were part of our frequent stay program, and someone was kind enough to help me by writing a VBA code to run a macro to solve that problem. Unfortunately that macro will not work in this situation, as the report that I run from my system for this is not in the same format.

Since the people who will be using this spreadsheet and macro are my employees and may not know excel as well as I do, I need to do this in as few steps as possible. They are already familiar with importing data from another excel file and using Ctrl Q to run the macro. My coupon workbook is saved as password protected, with a prompt to open as read-only so that it cant be messed up by someone by accident. (Password for attached file is password, no caps)

Currently we run a report from our reservation system, export it to excel, and save it to the desktop. Then we open the workbook that has the breakfast coupons in it. On the first tab they select import data and then select the saved excel file to import from. Then they go to the second tab in the coupons workbook and run the macro by using Ctrl Q. This creates the right number of coupons for each guest based on the number of days that they are staying with us.

In this first instance the report that I run has already sorted the guests, so it only includes the guests who I need coupons for. For this new set of coupons I need to not only account for the number of days a guest is staying, but for the coupons to be run only for certain guests based on the code that their reservation was booked under, as the report I can run from my system for this will not sort them first.

The report that I run from my system shows all arrivals for a given day or set of days. Each arrival will have a special code which shows what account they booked their reservation with.

I need a macro that will create coupons only for people fitting a certain set of codes. Also, I will need to be able to modify the list of included codes later in case we add any new codes or lose any current ones.

I have attached a copy of the files and reports that I am currently using.

The first file is the coupon workbook that I use. (Password is password, no caps) - Sorry I could not include this. Try as I might, I could not get it to fit the file size, not sure why. I will be happy to email this to someone who is interested!

The second file shows the report that we use for our frequent stay guests and import into the coupon workbook to make the coupons for them. (I show this just so you can see what already works) - Sorry, There is a maximum of 2 attachments per post. Again, I will be happy to email!

The third file is the report which I have which shows arrivals based on their booking code. This is listed in the far right column (the C-, L-, or S- before the 3 letter code can be ignored).

The fourth file is a list which shows what special rate codes include breakfast (all those which say BF). Guests who are listed in the third file, which shows arrivals based on their booking code, would need to be sorted using this information, to have coupons made for them.

Please note: All names have been changed and company names removed from these files for privacy purposes.

I know this is rather complicated, but I have attached all of the current files I have in the hopes that it will make things easier if someone chooses to help me.

Thank you in advance to anyone who has the chance to assist me with this!

I am running a program in excel and I can get the macros to perform all the formulas I have except I can't get it to do a vital part. I have a column where if the criteria from the previous formula isn't met then it will show a zero, if it is met then it will show the code of numbers. Now is it possible for excel to choose from that column the cell that has the code of numbers and then list all of them in a different column? I want to put these coded numbers in a different table but not a large one table.

I'll try to be a bit more specific, I have 350 rows of barecodes. Col A counts the barecodes Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 countif($B1:B$1,B1)  . Col B is the barcode ex: 06950290B0524123840022747. Col C shows me the code Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 if(B1="",0,MID(B1,5,5)*1)  ex: 50290. Col D gives me the date Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 if(B1="",0,MID(B1,12,5)*1)  ex: 52412. Col E gives me code and date together Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 (C1&D1)*1  ex: 50290052412. Col F counts all of the different codes/dates Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  countif(E:E,E1)  ex:116 (in this case). Col G counts each individual barecode incase there are duplicates Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  countif(B:B,B1)  . Col H gives me the item # Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 if(B1="",0,RIGHT(B1,5)*1)  ex: 22747. Col I shows if an item is missing Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  if(H1=H2-1,"OK","ERROR")  . Col J shows the entire date Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 MID(B2,11,6)  ex: 052412. Col K, Col L and Col M are for checking between different codes/dates aka batches Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 if(J1=J2,0,1)  for Col K, and for Col L Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  if(C1=C2,0,1)  . Col M Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 SUM(K1:L1)

Col N is the column I am referring to, it sorts out the different batches by code/date based on all the barecodes that match the criteria Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 if(M1 0,MID(B1,5,12),0)  . Col O would show the amount of each batch Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 if(M1 0,F1,"")  so 116. All formulas are extended 350 rows.

Here is where my problem lies, I am trying to find a way to read only the batches from Col N and the amounts from Col O and put them in a table without having to make the table 350 rows big. My table has to be small and only show the number of batches that from Col N and O that meet the criteria. As of now out of 350 rows, there are batches and amounts in Col N and O in rows 116, 195, 210,225,226,254,270,334,349 and 350, the rest have zeros based on the formula, however these rows will not always be the ones that meet the criteria So I want a table to show those 10 batches and 10 amounts in 10 rows with a column for batches and another column for amounts one after another; So the first batch will show up in row 1 on the table then the second batch will show up in row 2 and so on, is that possible, are there some formulas that will allow me to do this? The table doesn't have to be on the same page but I already know how to reference another sheet, I just don't want the table to have to be 350 rows to show the batches where the criteria is met.

Thank you

Thanks for the prompt response - turns out it was to do with my printer - a
Minolta QMS 3300. when I selected an HP printer as the default printer the
problem was resolved. Strange but true!!!


"keepITcool" > wrote in message
> I've seen this before.. I think the problem is connected to
> the Scaling and hidden columns..
> Try to circumvent by changing the column width of B and G
> from hidden (=0) to 0.1
> --
> keepITcool
> | | keepITcool chello nl | amsterdam
>> Hope you folks can help me out with a strange one.
>> I have several worksheets formatted in exactly the same way as
>> follows:
>> Col A - width 4
>> Col B - hidden
>> Col C - width 4
>> Col D - Width 108
>> Col E - Width 3
>> Col F - Width 11
>> Col G - Hidden
>> Col H - Width 11 & Empty
>> My print range should be Cols A:G (I have used page setup to set the
>> scaling to fit 1 page wide by [blank] pages tall, thus each sheet will
>> print as many pages as required depending on number of rows]
>> When I have the print range set to A:G only columns A:E show on the
>> print preview (and also on the actual print out) and when I make print
>> area A:F only A:C show on the print preview.
>> To get A:G printed I have to set the print area to A:H.
>> In all 3 of the examples above when I look at the print preview there
>> is a blank area on the right hand side of the page where the missing
>> columns SHOULD be but aren't... ie the size of the page seems to be
>> formatted correctly to include all the columns i want but they just
>> don't print unless 1 extra column is selected in the print area.
>> Has anyone out there experienced this problem before?
>> Thanks in advance.
>> Stuart

Ok, I admit it, designing a Collection of all the XL applications currently running in one's machine and use it nearly in the same way as other native collections in the XL object model is no doubt of little practical use for most XL users but I 've long wanted to do this if only for the challenge of it as well as for the sake of learning.

This subject was discussed here but was abandoned.

Also, let me just say that the method I've attempted here to do this is by no means an elegant robust solution.It's actually just a hackworkaround which also requires quite a bit of setting up and can also be prone to errors if not handled carefully.

For a robust solution, the only way I know of is by registering and using a third party type lib : OLELIB.TLB written in C with which one can get a pointer to every object in the ROT but then again, what we want here is a VBA-Only solution !!

Set Up required - Follow these 2 STEPS :

STEP1- Create a workbook , place the following code in the 'ThisWorkBook' Module , save it as an AddIn and name it : AppsCollection.xla

AddIn Code

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private bSkipCloseEvent As Boolean

Private Sub Workbook_Open()

    Dim oWB As Workbook
    Dim lHwnd As Long
    Dim vAddInLocations As Variant
    Dim sMaxInstances As String
    'check if there is a max allowed # of xl instances
    'in the registry
    sMaxInstances = GetSetting("XLInstances", "MaxAllowed", "Max=")
    'if there is,check how many xl instances are already open
    If Val(sMaxInstances)  0 Then
        vAddInLocations = GetAllSettings("MyRunningApps", "AddInLocations")
        'if this xl instance is going to exceed the allowed #
        'then set a flag,inform the user and close it
        If UBound(vAddInLocations, 1) >= Val(sMaxInstances) - 1 Then
            GoTo exitHere
        End If
    End If
    'however,if it's ok to open this instance then see if it is the first one.
    'if it is not,save this addin temporarly under a unique name
    'this will permit to retrieve a pointer to the parent application !
    lHwnd = FindWindow("XLMAIN", Application.Caption)
    If Me.ReadOnly Then
        Me.SaveAs Environ("temp") & Application.PathSeparator & _
        CStr(lHwnd) & "_" & Me.Name
    End If
    'always make an entry in the registry so the addin path
    'can be later retrieved by the 'Applications' class
    SaveSetting "MyRunningApps", "AddInLocations", CStr(Me.Name), CStr(Me.FullName)
    Exit Sub
    'we got here means there are more than the allowed # of instances
    'quit and get out now skipping the Before_Close event handler
    bSkipCloseEvent = True
    MsgBox "You have exceed the maximum number of allowed instances", vbExclamation

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'if user is closing this xl instance delete its corresponding
    'registry entry to indicate it's no longer running
    If bSkipCloseEvent Then Exit Sub
        DeleteSetting "MyRunningApps", "AddInLocations", CStr(Me.Name)
        'if it's not the first instance delete the associated temp addin
        If Me.Name  "AppsCollection.xla" Then
        KillMyself Me
    End If

End Sub

Private Sub KillMyself(oWB As Workbook)

    Dim sPathName As String
    sPathName = oWB.FullName
    oWB.ChangeFileAccess xlReadOnly
    Kill sPathName
    oWB.Saved = True

End Sub

Now, install this addin before you can use the Class shown in the next step.

STEP2- Create a workbook , add a Class Module to it's VBProject and name the Class : clsAppsCollection.
Place the following code in this Class Module :

Option Explicit

Private Declare Function ShowWindow Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Private Declare Function GetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Const MAX_COMPUTERNAME_LENGTH As Long = 31

Private oColApps As Collection
Private ap As Application
Private byMaxInstances As Byte
Private WithEvents wbCloseEvent As Workbook

Public Property Get Count() As Long

    Call UpdateAppsCollection
    Count = oColApps.Count

End Property

Public Property Let MaximumInstancesAllowed(ByVal vNewValue As Byte)
    'store this value in the registry so it can be accessed by all
    'other running xl applications
    SaveSetting "XLInstances", "MaxAllowed", "Max=", CStr(vNewValue)
    byMaxInstances = vNewValue

End Property

Public Property Get MaximumInstancesAllowed() As Byte

    MaximumInstancesAllowed = byMaxInstances

End Property

Public Function OpenNew _
(Optional visible As Boolean = True, Optional WBPathName As Variant) As Excel.Application

    Dim sErrMsg As String

    If Not WeAreExceedingMaxXLInstances Then
        'if there is 'WBPathName' this xl instance will open it
        If Not IsMissing(WBPathName) Then
            If Len(Dir(WBPathName))  0 Then

                Set OpenNew = OpenNewXLApp(visible, WBPathName)
            'wrong pathname entered ?, warn the user and get out
                sErrMsg = sErrMsg & "Check the spelling of the path name."
                MsgBox "Path name : ' " & WBPathName & " '  not found." & vbCrLf _
                & vbCrLf & sErrMsg, vbExclamation
            End If
            Set OpenNew = OpenNewXLApp(visible)
        End If
    End If
End Function

Public Function Item(NameOrIndex As Variant) As Excel.Application

'this routine allows to reference the applications by their index #
'or the name of any of its children workbooks for easier use of this proprety

    Dim i As Byte
    Dim oWBk As Workbook
    Call UpdateAppsCollection
    If Not IsNumeric(NameOrIndex) Then
        For i = 1 To oColApps.Count
            For Each oWBk In oColApps(i).Workbooks
                If UCase(oWBk.Name) = UCase(NameOrIndex) Then
                    Set Item = oColApps(i)
                    Exit Function
                End If
            Next oWBk
        Next i
    End If
    Set Item = oColApps(NameOrIndex)

End Function

Public Sub ActivateApp(App As Excel.Application)
    'this is just a nice convinience
    'when you want to quickly activate one
    'particular xl instance

    Call UpdateAppsCollection
    If App.visible Then

        App.WindowState = xlMaximized
        AppActivate App.Caption
    End If

End Sub

Public Property Get HostComputer() As String
    'property similar to the standard Parent prop
    Dim lLen As Long
    Dim sBuffer As String
    'Create a buffer
    sBuffer = Space(lLen)
    'Get the computer name
    GetComputerName sBuffer, lLen
    'get only the actual data
    sBuffer = Left(sBuffer, lLen)
    'Show the computer name
    HostComputer = sBuffer

End Property

Private Sub Class_Initialize()
    'set up the 'Before_Close' event sink
    Set wbCloseEvent = ThisWorkbook

End Sub

Private Sub wbCloseEvent_BeforeClose(Cancel As Boolean)
    'make sure we delete the registry entry that
    'stores the # of xl instances once the class workbook is closed
    On Error Resume Next
    DeleteSetting "XLInstances"

End Sub

Private Sub UpdateAppsCollection()

    'this routine updates the current # of insatnces
    'and assigned their respective pointers to the module
    'level collection.
    'this routine is run every time a member of this class
    'is executed. this is vital to keep upto date with
    'the changing number of open xl instances.
        Dim i As Byte
    Dim vAddInLocations As Variant

    Set oColApps = New Collection
    vAddInLocations = GetAllSettings("MyRunningApps", "AddInLocations")
    For i = LBound(vAddInLocations, 1) To UBound(vAddInLocations, 1)
        oColApps.Add GetObject((vAddInLocations(i, 1))).Parent
    Next i

End Sub

Private Function WeAreExceedingMaxXLInstances() As Boolean

    Dim vAddInLocations As Variant

    vAddInLocations = GetAllSettings("MyRunningApps", "AddInLocations")
    If byMaxInstances  0 Then
        If UBound(vAddInLocations, 1) >= byMaxInstances - 1 Then
            WeAreExceedingMaxXLInstances = True
        End If
    End If

End Function

Private Function OpenNewXLApp _
(Optional ByVal bVisible As Boolean = True, Optional ByVal sWBPathName As Variant) As Excel.Application

    Dim oNewApp As Application
    Dim sAddInPathName As String
    Set oNewApp = CreateObject("EXCEL.APPLICATION")
    oNewApp.UserControl = True
    sAddInPathName = AddIns("AppsCollection").FullName
    oNewApp.Workbooks.Open sAddInPathName
    Call UpdateAppsCollection
    oNewApp.visible = bVisible
    If Not IsMissing(sWBPathName) Then
        oNewApp.Workbooks.Open sWBPathName
    End If
    Set OpenNewXLApp = oNewApp

End Function
That's it.

Here are two examples you can try once the AddIn has been insatlled & loaded and the Class has been added to your project.

Start a few instances of Excel and run the test routine below :

Example 1

'In a Standard Module

Option Explicit

Private Applications As clsAppsCollection

Sub Test()

    'open a few xl applications instances and
    'run this code to create an 'Applications' collection
    'and display info about each xl instance
    Dim App As Excel.Application
    Dim i As Byte, j As Byte
    Dim sMsg As String
    Set Applications = New clsAppsCollection
    With Applications
        For i = 1 To .Count
            sMsg = sMsg & " Application:  " & i & vbCrLf
            sMsg = sMsg & "--------------" & vbCrLf
            sMsg = sMsg & " WorkBooks Count:  " & _
            Applications.Item(i).Workbooks.Count & vbCrLf & vbCrLf
            For j = 1 To .Item(i).Workbooks.Count
                With .Item(i).Workbooks(j)
                    sMsg = sMsg & " WorkBook:  " & j & "  Sheets Count : " _
                    & .Sheets.Count & vbCrLf
                End With
            Next j
            sMsg = sMsg & vbCrLf & vbCrLf
        Next i
    End With
    MsgBox sMsg

End Sub

Example 2:

'In a Standard Module

Option Explicit

Private Applications As clsAppsCollection

Sub Test2()
    'allow no more than 3 xl instances @ a time
    'and use the OpenNew Method to open one
    Set Applications = New clsAppsCollection
    With Applications
        .MaximumInstancesAllowed = 3
    End With

End Sub

A word of caution for anyone trying this code! The code in this project makes use of the Registry to store temporary entries inside the 'VB and VBA program Settings' Key during the AddIn auto_Open event. I believe this is safe but if you happen to edit the code, subtle errors can arise and can be tricky to locate. You may have to manually delete the above temp reg entries by running the RegEdit.exe Applet.

The MaximumInstancesAllowed Property can be particularly troublesome if edited !

Tested in XL 2002 Office XP.

Any feedback would be much appreciated.


I am trying to put some conditional formatting on an excel sheet. I am working with data that is pulled in from an outside data base. I have multiple columns and need them all formatted in the same way, which isn't a problem, with three different conditions:
1. If B10=100 blue formatting needs to appear, simple enough, in conditional format 1 I did: =IF($B10=100,1)
2. If D10=100 green formatting needs to appear, again, simple enough, in conditional format 2 I did: =IF($D10=100,1)
3. If O10 is not equal to R10 orange formatting needs to appear. This seemed simple enough, I did =IF($O10$R10,1) which worked in theory, except the problem is some of the numbers are only a couple of cents off. I don't care if that is the case, they are basically equal so I need to make the formatting show if 010 is not within $1 of R10. I can't figure out how to make a range, I tried =IF($R10-1>$O10>$R10+1,1) but this didn't work. If anyone has any ideas it would be wonderful. Thank you so much for your help!

hi there, i really need help, im working in a interesting project about data aquisition, making calculations, report the results and then make a graphic...ALL IN REALL TIME, de data aquisition is based on a dx system , this sistem get the data from sensors installed on a variety of industrial machinery , well... the problem is, in the past the number of data was acumulative, so we have a number of data aquired at the order of 200 or 300 data samples, now whita that using formulas there is no problem making calculations etc etc etc, when the graphic was done there is no problem too, because we have more than 1 data available to obtain max min prom etc....well now all the data aquisition is in reall time! so when y make the graphic it only shows one point (this based on a x-y graphic) , i only make de aquisition of two kinds of data (hz and potency in Mw) but now i have just one variable changing constantly at the reason of one second, my data number downs from 300 samples of data to 1!

SO!, what im trying to do is:

1.- i have 2 data entryes
2.- #1: in Hz and #2 in Mw
3.- each data changes every one second
4.- each data from each cell needs to be puted in a formula to obtain a sumatory or promedy or maxs or mins etc (by example =MIN (A1))



that is the =MIN value based on a minimal change in the obtained data, based in a change of one second.

once i obtain that data ill treat that cell like if was 2 data sources, or two diferent values, because in the main formula, a number of variables is needed, if i put number of variables =1 the result is not succesfull, BESIDES my system values changes every one second

so, there is a form to put that data in a diferent cell to obtain a new column of data?????

d __________________________e

the 1st sec data___________the 2th second data

whit that i can promediate the data and make my calculations!!!



OK, what I'm doing is basically tallying up a series of tournaments in order to show overall winners. I have a formula to do that- that is no problem.

What I need to do however is for every one of these individual tournaments is to get a score. The first tournament there are 64 entrants and I want to gte a score out of 128.
The first person #1 should have a score of 127 (just to bring it on par with the other tournaments of 128 I am comparing it with.
The second: score 125
The second last: score 3
Last score 1 (just to make it one better that if the tournament was not entered at all.

To do this i have used the formula =129-1*(2*C5) (where C5 was their place in the tournament).

The problem I have is that I am using a bakers dozen numbers of tournaments and in each one slightly different people contested. Some people were in every tournaments, some were in a few and others may have only been in one.

The trouble I have is that when a person did not enter a tournament he gets a placing of "0", which means that point wise he gets 129, which is better than first place.
In actuality I want him to get 0. How can i put some kind of limit so that the calculation is only done if the placing is greater than 0 (ie 1 and up)? But if the placing is zero, then the points will come to be zero)?

Sorry if that was hard to follow, you probably didn't need to know the background of what I needed, but there it is.
Please help me someone.

I want to import some, but not all, columns from a *.csv file. I am trying to do this with VBA because I then have to do a bunch of other things with the data (replace values, rearrange the columns, etc.) and I want to do it all with one button.

My code works just fine if the file is named *.txt. But if I try this on the same file named *.csv ALL the columns import. Also, I need to import some "number" columns as text. Again, works fine on the *.txt, but not the *.csv.

Can anyone offer some insight on how to get this to work. (a solution other than renaming the file first )

Thank you
file = Application.GetOpenFilename()

Workbooks.OpenText Filename:=file, Origin:=437, StartRow:=1 _
        , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 9), _
        Array(3, 9), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 9), Array(9, 1), _
        Array(10, 1)), TrailingMinusNumbers:=True
ps. the file I importing is 48 columns, I only show few above to make it easier to read.

Hello everyone!

I am trying to create a database to be used for SQL and I am using excel to make this happen. One of the requirements that I must do is convert the .xls file into .csv file. So, I have all the data with the calculations and I already know that if you saved the file as csv with the calculations, it will show as text only.... So, I copied the entire worksheet and used paste special and clicked on "values and number formats". Doing it this way eliminates the formulas and just display the values in the sheet to be saved as .csv format. After saving the file as .csv and then opened it a few days later, most of the data changed dramatically and I can't figure out why. The Averages I used are displayed as 0 (zero) in several columns. The formulas that I used to calculate in the original excel sheet are now displaying as 0 when there should be a number of a finished product.

Does anyone else know why it's doing that? If I am doing it wrong, please let me know the correct steps so I can correct it as soon as I can.

Thanks! I'm using Excel 2003 in XP environment.

I hope the code shows up correctly. If not I will have to reply to my own post with new code.

I am trying to find records that appear more than once in my sheet and then copy just one of the record copies to another location. Using Col O which is the tax parcel number if there are 2 or more parcel numbers such as O6,O7,and O8 I need to grab or highlite just the first entire row O6 or have all the 1st record copied to another location. I do need the entire row though because I am making mailing labels from the record.

These are people who are deliquent on their property taxes. Each record that they have is one year of taxes owed. I only want people who have at least 2 years of late taxes. But I only need to have one of their records to make a mailing label. Any help will be greatly appreciated.


*removed by Admin

Hey there again...

I'm using excel 2007 currently and am trying to write a list.

I've importerd a selection of data from a database we use. Column A shows the part number, Column B shows the quantity and column c shows cost. The Parts number we use are codes set out like 12345-12345-12. And in total we have over 200 varieties of this number, each variant relates to a product we make here on site.

If possible, (and i'm not sure this is) I would like a formula that would list each code found in the imported data, once. So effectivly the same results you see when you have set a filter up. You can click on the filter arrow and see each different variation used.

So if the first five lines in column a were:-


Then in column D i would like to see:


If any of you have any ideas at all (bar the only one i've found so far, manually type them in) then i'd love to hear it and would appreciate your help.

I hope I've made that clear enough, If not then please, please, please, ask any questions you need to.

Thank you very much in advance for any help you, much venerated, people can offer me. And if you can't help me then thanks foir reading this far at aleast :D


Hi All,

Please see attached picture of what I am trying to achieve. Please bear with me while i try and explain as best i can the rationale behind this, and then I'll ask the question.

I have a s/sheet in which there are 2 user inputs:

1. the user enters numerical values in column "E"
2. the user selects a value from a pulldown menu in cell "H10" to match the input in column "E".

For example (as shown in the picture), if "3" is selected in "H10", then the user would insert values in column "E" (green boxes) corresponding to multiples of "3", ie. days 0,3,6,9,12,15.... as shown in column "D" (grey boxes).

As another example, if "5" were selected in "H10", then entries would be made in days 0,5,10,15,20....

In other words, for any value in "H10", the corresponding MULTIPLES of that number are referred to in column "D" in order to complete the relevant entries in column "E"

Once this is done, a series of (corrected) calculations are made corresponding to ALL days included in column "D" and the results presented in column "F".

One requirement of the s/sheet is to reproduce the grey (column "D") and pink (column "F") boxes into a NEW table (columns "I" & "J") such that it is easier to see the relationship between the days (in this case, multiples of 3, ie. 3,6,9,12..) and the corrected values corresponding to those days (ie, pink boxes in column "J").

For this, i have used the formula that is highlighted in the formula box at the top of the screen. Thus, for any user input into "H10", this table automatically shows the relevant multiples and the corresponding corrected numbers.

OK, so far so good.

The problem is this: i want to recognise cell "E47" as the last cell in column "E" containing a numerical value (via user input), and then report ONLY its partnered values in cells "D47" and "F47" up until that point, ie. the grey and pink cells shown in columns "I" and "J". You will notice that the current formula will provide values for ALL multiples of "3" and not just up to "27".

I cannot just simply copy/reproduce what is shown in columns D/E/F into a new table to achieve this because the user input in column "E" has the potential to always vary, BOTH in time (ie, total number of days) AND in multiplicity (eg, 0,2,4,6... or 0,4,8,12... or 0,7,14,21,28.....).

So what i need is to identify the last cell in column "E" that contains a numerical value, and then report its partnered values only up to that point.
Can this be achieved with the current OFFSET formula i have?

i've tried incorporating several things but to no avail. Many apologies for the long-winded explanation, but i am trying to make it easier on whomever has to try and decipher this!

Many thanks in advance.

I know that pivot charts lose formatting when the table is refreshed. I read everywhere that the work around is to apply a macro. But I have never seen code for it to get some ideas. I did this for my first ever macro so I though I might share some of my lessons learned for other beginners. Mine was specifically a line chart but I am sure it can be altered.

I welcome others to add their thoughts or experiences on making it even easier. I am sure there is enough knowledge on this board to allow others a great start to building this type of macro.

Interesting Food for Thought
Recording the macro is fairly straight forward. But when you want to re-use the macro for similar graphs you have a few issues. I was able to build the macro with some "re-usability" with only a few small changes to make it work on similar graphs.
1) The active chart name "Chart 1" etc, needs to be changed (Is there a way to have this dynamic?).
2) If the number of series in the chart can change from chart to chart. It is a good idea to build in a loop to apply formatting to the number of series.
3) My chart applied trend lines. Therefore it added to the legend. I didnt want to show the trend in the legend so I had to develop a way to delete them. I used a similar loop getting the count of series.
4) When changing line weights etc. When you go above 6 or 7 pt I think, the tick marker automatically switches to a solid block (I couldnt get it to change back). I originally had a loop to change the marker style but the issue caused me to make it all the same. is the code...

Sub Reapplygraphformat()
' Reapplygraphformat Macro
' Reapply graph format to closed defect duration graph.
' Keyboard Shortcut: Ctrl+r
Dim LegendCounter As Integer 'a counting variable for number in legend
Dim SeriesCounter As Integer 'a counting variable for number in series
Dim ColorHolder As Integer 'a counting variable for number of Color

ActiveSheet.ChartObjects("Chart 2").Activate
SeriesCounter = 1
Do Until SeriesCounter > ActiveChart.SeriesCollection.Count
If SeriesCounter = 1 Then '
ColorHolder = 5
If SeriesCounter = 2 Then
ColorHolder = 7
If SeriesCounter = 3 Then
ColorHolder = 6
If SeriesCounter = 4 Then '
ColorHolder = 8
If SeriesCounter = 5 Then '
ColorHolder = 9
End If
End If
End If
End If
End If

With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlX
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(SeriesCounter).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
With Selection.Border
.ColorIndex = ColorHolder
.Weight = xlThin
.LineStyle = xlContinuous
End With

SeriesCounter = SeriesCounter + 1

LegendCounter = ActiveChart.SeriesCollection.Count * 2
Do Until LegendCounter = ActiveChart.SeriesCollection.Count
LegendCounter = LegendCounter - 1

End Sub

Thanks for any additions!!

I have a spreadsheet which has an output of order numbers and order details from different Suppliers. The problem with the spreadsheet in its current format is that I am unable to use data filter and choose all orders by a particular supplier.
Column A is blank.
B1 has the supplier name
Row 2 is blank
Row 3 is blank
B4:H10 has the order data in it
H11 has the total of that suppliers orders. nothing else in Row 11
B12 has a new supplier name
Rows 13 & 14 are blank
B15:H19 has the order data
H20 has the total of orders, nothing else in that row.
This format just repeats itself, some suppliers have more orders than others and sometimes it's not 2 rows of blank data after the supplier name, its only 1 blank row. Not sure why this happens but cant change it.

I think that a way to be able to do this is to somehow get the Supplier name to move from column B to column A but to also place it beside all of the order numbers for that particular supplier. Then data filter could be used to show all orders by one Supplier.
Im guessing there will be some way to do this using VBA, which is a bit like black magic to me. I can understand it once it is written but I am totally lost at writing it from scratch.
The other problem with this sheet is that for some of the suppliers you get the Supplier name in column B, 2 blank rows and then the data. For other suppliers you only get 1 blank row then the data, not sure why this is.
At the same time in column H at the end of each data set there is a cell with a total for each supplier, I would like to take this out as if everything is going into a table format which can be data filtered then this is no longer required.
Please see the attached spreadsheet which hopefully makes my explanation seem slightly clearer! Im using excel 2007 but have saved the example in 2003 format.