Hi Mike,

I did a blank sheet and did the Insert Module as you suggested. Put in the simple test code and it works. Now, my real problem is a workbook of multiple sheets that I have been working on for quite some time. I have some subroutine modules on the This Workbook section in VBA that do work. However, functions either on that section or on a worksheet section do not work, They are not even recognized as functions. I will probably start a new workbook and reconstruct what I have to see if something I have tried in the past has messed things up. Thanks for the help getting to here.

Terry
[email protected]

Hi Terry,

Sounds good. Something that may help you as well. In the workbooks that are working, you can right click the module, and choose export. Then in the workbook where you want the functions, you can the import the exported file.

You can also just copy and paste from the Sheet/Workbook sections into a module.

Talk to you later,

Mike

Mike
EchoVue.com

Thasnks for your help. I still am not getting my function to work as I want. Basically I am trying to search a 4 column array, matching in the first 3 columns and returning the resulting 4th col;umn. Here is my function:

Function PRMRate(RateTable As Range, ByVal Customer As Variant, Pickup As Variant, Drop As Variant)
    Dim a As Integer, TestCust As Variant, TestPickup As Variant, TestDrop As Variant
    'Get size of dataset
    lastrow = RateTable.Cells(1, 1).Value
    'Initialize return argument
    PRMRate = 0
    'Loop thru data for complete match of 3 fields
    For a = 1 To lastrow
        TestCust = RateTable.Cells(a, 1).Value
' Is This the Right Customer?
        If (Customer = TestCust) Then
' Is This the Right Pickup point for This Customer?
            TestPickup = RateTable.Cells(a, 2).Value
            If (Pickup = TestPickup) Then
' Is This the Right Drop Point for This Combination?
                TestDrop = RateTable.Cells(a, 3).Value
                    If (Drop = TestDrop) Then
' Yes - Set the Rate for This Combination
                    PRMRate = RateTable.Cells(a, 4).Value
                End If
            End If
        End If
    Next a
End Function

The range I am searching looks like this:

20
CustomerName PickupPoint DropPoint Rate
A C G 1
A C H 2
A C I 3
A D J 4
A D K 5
A D L 6
B E M 7
B E N 8
B E O 9
B F P 10
B F Q 11
B F R 12

It seems pretty simple, but then, too, I'm self-taught, so I'm pretty simple, too, and a novice in VBA. I start the search in the first column If I match in the first column, then check the second column of the same row for a match. If that is true, then I check the third column of the same row for a match. If that is true, then I return the value in the fourth column of the same row as the function value. Wahere am I wrong?

Thanks for any help anyone can give.

Hello!

It's not a formula problem!
I use this easy formula =WEEKNUM(A10-1) and save the sheet on a memory stick.
It works always fine on my DELL Laptop (Excel 2003 SP2), no problems.
Today I saw that my colleague (using this memory stick also) on his DELL Laptop get ### and as explanation: "formula contains unrecognized text"
The sheet doesn't work on his laptop/excel. On my laptop it works.
But still I don't know the reason...:(

Ciao,
Ronald

Check on his laptop (in Excel):
Tools - Add in -Analysis ToolPak and Analysis ToolPak VBA.
They shuld be checked.

That was the first what I checked yesterday. Although I think it has no relation to normal formulas. I activated it. On my laptop I activated it earlier already for a function where it was needed.
The error message was a little bit different then. But it doesn't work.
And what I found today: on a other sheet in the same workbook the formula makes no problems: I can change the date and I get the right weeknumber. Is there maybe a special setting for one sheet in the workbook

Ciao,
Ronald