Charles,

Thanks for the reponse. Will the ugly fix (application.volitile) work as
well as passing the ranges as arguments? I am just after a quick fix for now,
and the change to the argument will take some time to sort out, due to having
also to rewrite the formulas in the sheet to supply the ranges. I will
cretainly try it, as a learning opertunity for myself, but if the
application.volitile fix works, at least I can get the second workbook done
(passing all the ranges as arguments) and running before going back and
recoding the current one.

Regards
Richard

"Charles Williams" wrote:

I agree that one of the problems with your functions is that the functions
dont know which workbook/sheet they are referring to.

Another major problem is that you are referring to ranges that are not in
the argument list of the functions.

This does not work properly because Excel does not know when to recalculate
the functions.

An ugly fix is to add Application.Volatile to each function.

A better solution is to make sure that all the ranges being used inside the
function are passed as arguments.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Aviashn" wrote in message
...
Just glancing through your code I noticed a good best practice.
Define your workbooks and worksheets.

Dim wb As Workbook
Dim wsCurr As Worksheet

Set wb = Application.Workbooks("MyWorkbook.xls")
Set wsCurr = wb.Worksheets("Sheet1")

So that later you can be sure you are pulling from the correct
source. A modification to your code:

Startrow = wb.wsCurr.Range(LotNoCell.Address).Row
StartlotColumn = wb.wsCurr.Range(LotNoCell.Address).Column
StartDataColumn = wb.wsCurr.Range(DataCell.Address).Column

Not sure that this is your problem, but it will help a lot if you have
multiple workbooks open.

Hope this helps.

On Feb 21, 10:25 am, Richard Wood
wrote:
Here is all the code. As I said this is not my strong point, I learnt a
bit
of baisc over 20 years ago and have not done much with it since.

Function GetAllDataForLot(LotNoCell, DataCell)
MaskLen = 8
RowCount = 0
StartLotNoStr = ""
MylotNoStr = ""
sumof = 0

Startrow = Range(LotNoCell.Address).Row
StartlotColumn = Range(LotNoCell.Address).Column
StartDataColumn = Range(DataCell.Address).Column

n = 1

Do While (Left(Worksheets("BLCK Classified").Cells((Startrow + n - 1),
StartlotColumn).Value, MaskLen)) = (Left(Worksheets("BLCK
Classified").Cells(Startrow, StartlotColumn).Value, MaskLen))
n = n + 1
Loop

RowCount = n - 1

For c = 1 To RowCount
myrow = Startrow + c - 1
mycolumn = StartDataColumn

sumof = sumof + Worksheets("BLCK Classified").Cells(myrow, mycolumn).Value

Next c

GetAllDataForLot = sumof

End Function

Function RowOffset(LotNoCell)

MaskLen = 8
RowCount = 0

Startrow = Range(LotNoCell.Address).Row
StartlotColumn = Range(LotNoCell.Address).Column

n = 1

Do While (Left(Worksheets("BLCK Classified").Cells((Startrow + n - 1),
StartlotColumn).Value, MaskLen)) = (Left(Worksheets("BLCK
Classified").Cells(Startrow, StartlotColumn).Value, MaskLen))
n = n + 1
Loop

RowCount = n - 1

RowOffset = RowCount - 1

End Function
Function SmallestBK(Start1, Start2)
Column1 = Range(Start1.Address).Column
Column2 = Range(Start2.Address).Column
Startrow = Range(Start1.Address).Row

If Worksheets("BLCK Overview").Cells(Startrow, Column1).Value = "" Then
lowest = Worksheets("BLCK Overview").Cells(Startrow, Column2).Value
Else
lowest = Worksheets("BLCK Overview").Cells(Startrow, Column1).Value
End If

For n = 1 To 118
If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value) 5 Then
If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value < lowest Then
lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value
End If
End If
Next n

For n = 1 To 118
If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value) 5 Then
If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value < lowest Then
lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value
End If
End If
Next n

For c = 1 To 118
If Right(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 1) = "A" Then
If Left(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 8) = lowest Then
lowest = lowest + "A"
End If
End If
Next c

SmallestBK = lowest

End Function

Function NextSmallestBK(Start1, Start2, LastSmallest)
Column1 = Range(Start1.Address).Column
Column2 = Range(Start2.Address).Column
Startrow = Range(Start1.Address).Row
LastSRow = Range(LastSmallest.Address).Row
LastSColumn = Range(LastSmallest.Address).Column

lowest = "zzzzzzzz"

For n = 1 To 118
If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value) 5 Then
If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value Worksheets("BLCK Overview").Cells(LastSRow,
LastSColumn).Value Then
If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value < lowest Then
lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value
End If
End If
End If
Next n

For n = 1 To 118
If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value) 5 Then
If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value Worksheets("BLCK Overview").Cells(LastSRow,
LastSColumn).Value Then
If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value < lowest Then
lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value

End If
End If
End If
Next n

For c = 1 To 118
If Right(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 1) = "A" Then
If Left(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 8) = lowest Then
lowest = lowest + "A"
End If
End If
Next c

If Worksheets("BLCK Overview").Cells(LastSRow, LastSColumn).Value = ""
Then
NextSmallestBK = ""
Else
If lowest < "ZZzzzzzz" Then
NextSmallestBK = lowest
Else
NextSmallestBK = ""
End If
End If

End Function

Function BKCLExist(MasterLotNo, CLLotNo)

MasterColumn = Range(MasterLotNo.Address).Column
MasterRow = Range(MasterLotNo.Address).Row
CLRow = Range(CLLotNo.Address).Row
CLColumn = Range(CLLotNo.Address).Column
BKCLExist = ""

For n = 1 To 118
If Worksheets("BLCK Overview").Cells(CLRow + n - 1, CLColumn).Value
= Worksheets("BLCK Overview").Cells(MasterRow, MasterColumn).Value Then
BKCLExist = Worksheets("BLCK Overview").Cells(MasterRow,
MasterColumn).Value
End If

Next n

End Function

Function BKKNExist(MasterLotNo, KNLotNo)

MasterColumn = Range(MasterLotNo.Address).Column
MasterRow = Range(MasterLotNo.Address).Row
KNLotRow = Range(KNLotNo.Address).Row
KNLotColumn = Range(KNLotNo.Address).Column

BKKNExist = False

For n = 1 To 118
If Worksheets("BLCK Overview").Cells(KNLotRow + n - 1,
KNLotColumn).Value = Left(Worksheets("BLCK Overview").Cells(MasterRow,
MasterColumn).Value, 8) Then
BKKNExist = True
End If

Next n

End Function



"Charles Williams" wrote:
I suggest you post the code for a typical function.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Richard Wood" wrote in message
...
I have written a number of custom functions for the manipulation and
sorting
of a large amount of data. These work exactly as I intended and I can
not
see
any problem with the way I have coded them, however I am not very
experienced
with VBA or any other programming language for that matter. These
functions
use a range of data from the sheet, and are used on 116 rows of the
sheet.

I am not sure if I have placed the code for them in the correct place
(in
Module1) them or defined them correctly (Function [function
name](parameter)), as the cause some strange results when other
workbooks
are
open at the same time.

When certain other workbooks are open (and active) and a recalculation
is
performed, when I return to my original workbook/sheet the custom
functions
have thrown up a "Value!" error (or it might be a REF! error I can't
remember
which). This is not so much of a problem as a simple recalc (F9)
corrects
this.

The big problem occurs when I have two versions of my workbook open.
(The
workbook is used to hold manufacturing data and a new one is created
every
month from a mast file for that months production). When 2 copies of
the
workbook are open any change of data in a single cell results in a