Posts by the_sleeper

    Sorry Guys for not priovidig an example..

    I have one attached if you wouldn.t mind looking at it...

    I hope it's enough to look at..

    I have used the custom function above to count the uniue values

    Hi All,

    i was wondering if there is a function in Excel that counts all the unique values from a list that meet a given criteria?

    ie Count the unique items in column D that have and x in the offsetted cell in column C?

    Is there anything better than the below (extremely slow) custom function that is by no means perfect

    Public Function CountSingle(InputRange As Range, Criteria1 As String, _
    InputOffset1 As Integer, Criteria2 As String, InputOffset2)
    Dim c As Range, counter As Double
    For Each c In InputRange
    If c.Offset(0, InputOffset1).Value = Criteria1 Then
    If c.Offset(0, InputOffset).Value = Criteria2 Then
    If c.Value <> c.Offset(1, 0).Value Then
    counter = counter + 1

    End If
    End If
    End If
    Next c

    CountSingle = counter
    End Function

    Any assistance would be highl appreciated.



    It doesn't contain any links actually. What I am doing is importing part(s) of a master file into the template (into a 'calculation' sheet) and then using SUMPRODUCT, SUM etc to retrieve the calculations that way. I find this is the quickest way bu unfortunatel it has to open 3 files for each month in the last two years and hence it takes about 1.5 - 2 mins. Are any other methods valid??

    Thanks in aadvance


    Thanks Dave..

    So from this it probably wouldnt be worth converting existing code ( to GetObject.

    By the way I love the screenupdating=false method-no more damn screen flashes!!!



    Hi All,

    I have a spreadsheet that has to impot data from other workbooks so at the moment it is set up so I use the"C\FolderName\workbook.xls") method. Iwas wondering is the GetObject method application.GetObject(,"C\FolderName\workbook.xls") is better in teams of being ableto open workboosk quicker and take up less memory. The only reason I ask is that people want this workbook to import the required information quicker (it takes about 1.5 mins at the moment) and I was hoping that the GetObject method may be a better and efficient alternative?

    Many thanks and regards.


    Hello and Welcome to the Board...

    With regards to your query, I find the easiest way to do this is to do the following few steps:
    1. Insert a new column next to the one where the values are
    2. Create a formula in the first cell of the new column =oldcell+(oldcell*15%)
    (oldcell being a reference to the original cell) - See below

    Original Column New Column
    (Eg COlumn A) (Eg Column B)
    15 =A1+(A1*15%)

    Now select cell B1 (the one with the formula in it) and you will notice a small balck box (it's called a handle) on the bottom left crner of the cell. Double-click the handle. (This will copy the formula down the entrie column (if you have data in the column to the left)

    That should do it, but I like to expand on this to 'stamp' the new values.

    To do this select the entrie contents of the new column and copy them, then go to the Edit Menu and Select Paste Specia, and select Values.
    (This will paste the values of the cells over the top of the formulas)

    If you want you can then delete the old column

    Happy Excelling!!!


    Welcome to the board...

    You could have an alternative to the above response

    Sub Auto_Open()
    if = "YourFilename" then
    Application.Calculation = xlCalculationAutomatic

    Application.Calculation = xlCalculationManual
    End Sub

    and then......

    Sub Auto_Close()
    Application.Calculation = xlCalculationAutomatic
    End Sub

    This could work as you stated that after the file get updated it is saved as a new filename.

    Hope this makes sense


    Thanks guys it look like I'll be working hard today to change my existing code to a much simpler arrangement.

    Dave-Thanks for the hint on incorporating the choose function in a loop, I'd never thought of that before, very logical and smart

    Thanks again


    Have any of you guys had this damn message!!!

    I have come across it recently when working with a large file.

    According to Micorsoft this is the cache memory of Excel being exceeded (64MB)

    Is there any smart away around this?

    To explain how I am trying to get around it at the moment:

    I have one file that links to multiple file across the network, each file contains about 5000 entries. I have set up a link in the first range of cells and then written a macro that autofills the data down 5000 rows, however I recieve the 'Not enought Memory' message. So I tried to break up the autofill macros to copy down 250 rows at a time and still I recieve the message.

    Is there any other way to achieve what I want (to have the file link to everything)?

    Any assistance would be greatly appreciated



    You could write a macro that pastes the value and overwirtes the formula when the workbook is closed??

    So say if the formula =NOW() is in A1

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("A1").PasteSpecial Paste:=xlValues
    End Sub

    My apologies......

    I just put a On Error Resume Next statement in and it works...

    One last question though..

    How do you get the activeworkbook as an attachment???

    Thanks once again for the help


    Here is the error and the description

    If anyone can help it would be GREAT!!

    Automation error (Error 440)

    When you access Automation objects, specific types of errors can occur. This error has the following cause and solution:

    An error occurred while executing a method or getting or setting a property of an object variable. The error was reported by the application that created the object.
    Check the properties of the Err object to determine the source and nature of the error. Also try using the On Error Resume Next statement immediately before the accessing statement, and then check for errors immediately following the accessing statement.


    Thanks for you prompt reply Jeff, but unfortunately I recieve an error at the

    Set notesdb = objNotesWS.COMPOSEDOCUMENT(, , "memo")


    Here is how I have it set out in a module

    Public Function SendMail(strAddressee As String, strSubject As String, strBody As String)

    Dim objNotesWS As Object
    Dim notesdb As Object

    Set objNotesWS = CreateObject("Notes.NotesUIWorkspace")

    Set notesdb = objNotesWS.COMPOSEDOCUMENT(, , "memo")

    notesdb.FIELDSETTEXT "EnterSendTo", strAddressee
    notesdb.FIELDSETTEXT "Subject", strSubject
    notesdb.FIELDSETTEXT "Body", strBody


    Set notesdb = Nothing
    Set objNotesWS = Nothing

    End Function

    Public Sub testmail()
    Dim imail
    Set imail = SendMail("[email protected]", "Test", "Text goes here")
    End Sub

    Hi All,

    One of the biggest issues I am having is producing VBA code to email the current workbook as an attachment in LOTUS NOTES. I know you can reference the object, but I don't know how to actually get it started. I can't use the application.sendmail method because it uses the useless Microsoft Exchange app (I need to send it via Lotus Notes)

    Any assistance would be appreciated