Replacement Code for Excel 97 Users - Join function

[Monk]

The code below works fine for users on excel 2003 and later but we have a
couple of people still on 97 which doesn't seem to handle the Join function.
Any thoughts on a replacement code that will work across all versions would
be appreciated. Thanks

Sub DataError()
Dim Contents As String
Contents = Join(WorksheetFunction.Transpose(Range("L2:L1250")), Chr(1))
If InStr(1, Contents, Chr(1) & "Not on System" & Chr(1), vbTextCompare) Then
MsgBox "Please review and update the database if required."
End If
Application.ScreenUpdating = True
End Sub
Application.ScreenUpdating = True
End Sub

 

[Tom Hutchins]

It looks like you are just seeing if any of the cells in L2:L1250 contain
"Not in System". You could do that with the Find command:

Sub DataError()
Dim c As Range
With ActiveSheet.Range("L2:L1250")
Set c = .Find("Not on System", LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "Please review and update the database if required."
End If
End With
End Sub

I don't have access to Excel 97 any more, but hopefully it supports Find.

Hope this helps,

Hutch

 

[Rick Rothstein]

The Join function (which takes a one-dimensional array of string values and
makes them into a single string of text, separating each array element in
that string using a specified delimiter) was added to the VBA language
starting in VB6. For versions of Excel using an earlier version of VB, you
will need to loop through the array, one element at a time, and concatenate
the string of text as you go. I don't have access to XL97, so I can't test
this, but I expect it would work. Replace this line...

Contents = Join(WorksheetFunction.Transpose(Range("L2:L1250")), Chr(1))

with these lines...

Dim Elements() As Variant

Elements = WorksheetFunction.Transpose(Range("L2:L1250"))
For X = LBound(Elements) To UBound(Elements)
If X < UBound(Elements) Then Contents = Contents & Chr(1)
Contents = Contents & Elements(X)
Next

putting the Dim statement up with your other Dim statements.