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.

DW

No,

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

DW

Thanks Dave....Ill take that on board for the future.

Regards

DW

Thanks Dave..

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

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

Thanks

Dw

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 workbooks.open("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.

DW

Thanks Dave for your promplt reply and this fantastic board

Regards

DW

Hi All,

Would anyone know how to create a custom function that determines the week number of a date?

Thanks and Best Regards

DW

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

DW

Welcome to the board...

You could have an alternative to the above response

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

else
Application.Calculation = xlCalculationManual
endif
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

DW

Hi guys,

I do generally structure my code with sub subs included in them....

To get around the error handling thing instead of using Exit Sub I use End....now that kills everything!!!

Best Regards

DW

tmelsr
,

Place this formula in B1

=if(and(a1>=1,a1<=9),30,0))

Best Regards

DW

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

DW

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

DW

paspoard,

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").Copy
Range("A1").PasteSpecial Paste:=xlValues
End Sub

Thanks Jeff,

I can now see about a million applications to used this for.

Regards

DW

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

DW

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.

DW

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

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

statement

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

notesdb.Send

notesdb.Close
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

Thanks

DW