Custom VBA Function

[kland1]

I created a custom function in vba but it does not update automatically like sum or other functions. The function gets numbers from another worksheet in the same workbook but when the numbers on the other worksheet change the cell with the function does not update automatically. Is there any special code that I need to have it update when the number changes on the other worksheet?

 

[Andrew Poulsom]

Welcome to the Board.

You could try:

Application.Volatile

at the beginning of your function.

 

[VoG]

Hello and welcome to MrExcel.

Try adding this near the top of your function

Application.Volatile

 

[kland1]

Thank you.

Works perfectly now. Thanks a lot.

 

[kland1]

Hey guys,

That line of code works good but its doing something different now. The cells update automatically now but since i just the same function in multiple workbooks, it seems that only one workbook will show the values and the other ones will say #VALUE!. I can only see the values of the other workbook when i click on a cell and hit enter. This is when the other workbook will change to #VALUE!. Is there a way I can fix this?

 

[Andrew Poulsom]

Please post your function.

 

[kland1]

'*********************************************************************************************
' Retrieves the number of copper ports
' checkRange - the range to check for "Copper"
' numberCol - the number of columns right of checkRange to retrieve the data
Function CountCopper(checkRange As range, numberCol) As Integer
Application.Volatile
total = 0
Col = checkRange.Column + numberCol

For Each cell In checkRange
If (LCase(cell.Value) = "copper") Then
Row = cell.Row
total = total + Sheets(checkRange.Worksheet.Name).Cells(Row, Col).Value
End If
Next cell

CountCopper = total
End Function

 

[Andrew Poulsom]

Instead of:

total = total + Sheets(checkRange.Worksheet.Name).Cells(Row, Col).Value

try:

total = total + checkRange.Parent.Cells(Row, Col).Value

 

[kland1]

Thank you again. Not getting the #VALUE! anymore.