Cell contents are not updating using a UDF

[haljam]

First off I would like to say that I've spent the better part of today looking for a solution to this problem. Everything that I've come across says to add "Application.Volatile" to the beginning of the macro. Adding "Application.Volatile" to the macro doesn't seem to do anything. Hopefully someone can figure out what's going on.

FYI my VBA skills aren't very strong.

I found the following UDF posted online that works beautifully. The UDF concatenates a range of cells and includes an option to include a delimiter.

Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
         MultiCat = MultiCat & sDelim & rCell.Text
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
     
  End Function

The data I need to be concatenated is produced using a combination of FIND and INDEX/MATCH. This portion also works beautifully.

When I update cells within the array used in INDEX my FIND, INDEX/MATCH formula continues to work as expected. However this is not the case with my MultiCat UDF.

For example. If I have the following contatenation result:

Spring 2000, Spring 2001, Summer 2001

and I update a value (Fall 2000) in my INDEX array I expect the following result:

Spring 2000, Fall 2000, Spring 2001, Summer 2001

Unfortunately after entering the data nothing changes. If I update a different cell in the array (e.g. Fall 2001), I end up getting the result I expected in the previous step but not the new change. The UDF continues to be one step behind after each calculation.

Please note that as I stated earlier, adding "Application.Volatile" has no affect on the result of the UDF.

Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Application.Volatile
     Dim rCell As Range
     For Each rCell In rRng
         MultiCat = MultiCat & sDelim & rCell.Text
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
     
  End Function

I think that's everything. Let me know if I'm missing any key information.

Thanks,
Hal

Windows XP
Excel 2007

 

[MrKowz]

Welcome to the forums!

Here is a UDF I wrote some time ago that I believe does what you need:

Public Function ConcatRange(rng As Range, Optional delimiter As String = "")
   Dim rRng As Range
 
   For Each rRng In rng
      If rRng.Value <> "" Then
         ConcatRange = ConcatRange & rRng.Value & delimiter
      End If
   Next rRng
 
   If Len(ConcatRange) > 0 Then
      ConcatRange = Left(ConcatRange, Len(ConcatRange) - Len(delimiter))
   Else
      ConcatRange = ""
   End If
 
End Function

 

[haljam]

It works like a charm. I have no idea what it is in your code that makes the difference though.

Many thanks MrKowz!

 

[highway32south]

Hoo Yaa! I've been jerking around with my spreadsheet for two days trying to figure out why multicat would update on one page but none of the other pages I used it on. You da man!!!!!