Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacement Code for Excel 97 Users - Join function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacement Code for Excel 97 Users - Join function
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 "Monk" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacement Code for Excel 97 Users - Join function
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. -- Rick (MVP - Excel) "Monk" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code replacement | Excel Programming | |||
PPMT function replacement | Excel Worksheet Functions | |||
replacement for IF function? | Excel Discussion (Misc queries) | |||
In Excel VBA Editor, how do I hide my code from other users? | Excel Discussion (Misc queries) |