Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
I get an error on the Replace part of the following code for people who are
still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
Replace is n/a in Excel 07, along with various other string manipulation
functions. To cater for all versions do it like this Dim sOld As String, sNew 'code sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew Regards, Peter T "Monk" wrote in message ... I get an error on the Replace part of the following code for people who are still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
Thanks for your assistance Peter
I am getting a variable not defined error on the "sRep". Can you advise how to fix this as well? Regards "Peter T" wrote: Replace is n/a in Excel 07, along with various other string manipulation functions. To cater for all versions do it like this Dim sOld As String, sNew 'code sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew Regards, Peter T "Monk" wrote in message ... I get an error on the Replace part of the following code for people who are still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
Sounds like your module is headed Option Explicit (good) which means any
routine that attempts to use an undeclared variable will halt. In the code I posted I forgot to declare sRep! Change Dim sOld As String, sNew to Dim sOld As String, sNew As String, sRep As String Regards, Peter T "Monk" wrote in message ... Thanks for your assistance Peter I am getting a variable not defined error on the "sRep". Can you advise how to fix this as well? Regards "Peter T" wrote: Replace is n/a in Excel 07, along with various other string manipulation functions. To cater for all versions do it like this Dim sOld As String, sNew 'code sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew Regards, Peter T "Monk" wrote in message ... I get an error on the Replace part of the following code for people who are still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
Thanks Peter. I seem to have one final problem in that for 97 users the
resultant spreadsheet does not show any data. I think it may have to do with this line on filtering; should it also have a reference to sRep? My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew I have attached the full code if that will assist. Cheers '<<<< Create a new sheet for every Unique value 'This example copy all rows with the same value in the first column of 'the range to a new worksheet. It will do this for every unique value. 'The sheets will be named after the Unique value. 'Note: this example use the function LastRow in the ModReset module Sub SeparateAdmin() 'Note: This macro use the function LastRow Dim My_Range As Range Dim FieldNum As Long Dim CalcMode As Long Dim ViewMode As Long Dim ws2 As Worksheet Dim Lrow As Long Dim cell As Range Dim CCount As Long Dim WSNew As Worksheet Dim ErrNum As Long Dim sOld As String, sNew As String, sRep As String 'Set filter range on ActiveSheet: A11 is the top left cell of your filter range 'and the header of the first column, D is the last column in the filter range. 'You can also add the sheet name to the code like this : 'Worksheets("Sheet1").Range("A11:D" & LastRow(Worksheets("Sheet1"))) 'No need that the sheet is active then when you run the macro when you use this. Set My_Range = Range("A1:k" & LastRow(ActiveSheet)) My_Range.Parent.Select If ActiveWorkbook.ProtectStructure = True Or _ My_Range.Parent.ProtectContents = True Then MsgBox "Sorry, not working when the workbook or worksheet is protected", _ vbOKOnly, "Copy to new worksheet" Exit Sub End If 'This example filters on the first column in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = 11 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False 'Change ScreenUpdating, Calculation, EnableEvents, .... With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add With ws2 'first we copy the Unique data from the filter field to ws2 My_Range.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True 'loop through the unique list in ws2 and filter/copy to a new sheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A2:A" & Lrow) 'Filter the range sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew 'Check if there are no more then 8192 areas(limit of areas) CCount = 0 On Error Resume Next CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible ) _ .Areas(1).Cells.Count On Error GoTo 0 If CCount = 0 Then MsgBox "There are more than 8192 areas for the value : " & cell.Value _ & vbNewLine & "It is not possible to copy the visible data." _ & vbNewLine & "Tip: Sort your data before you use this macro.", _ vbOKOnly, "Split in worksheets" Else 'Add a new worksheet Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count)) On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then ErrNum = ErrNum + 1 WSNew.Name = "Error_" & Format(ErrNum, "0000") Err.Clear End If On Error GoTo 0 'Copy the visible data to the new worksheet My_Range.SpecialCells(xlCellTypeVisible).Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher ' Remove this line if you use Excel 97 '.PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With End If 'Show all data in the range My_Range.AutoFilter Field:=FieldNum Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False If ErrNum 0 Then MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _ & vbNewLine & "There are characters in the name that are not allowed" _ & vbNewLine & "in a sheet name or the worksheet already exist." End If Application.DisplayAlerts = False Sheets("Combine Sheet").Select ActiveWindow.SelectedSheets.Delete Range("A1").Select Sheets("Kylie").Select 'Windows("AET Client List.xls").Activate 'ActiveWindow.LargeScroll ToRight:=-2 'ActiveWindow.Close 'ActiveWindow.LargeScroll ToRight:=-2 Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Peter T" wrote: Sounds like your module is headed Option Explicit (good) which means any routine that attempts to use an undeclared variable will halt. In the code I posted I forgot to declare sRep! Change Dim sOld As String, sNew to Dim sOld As String, sNew As String, sRep As String Regards, Peter T "Monk" wrote in message ... Thanks for your assistance Peter I am getting a variable not defined error on the "sRep". Can you advise how to fix this as well? Regards "Peter T" wrote: Replace is n/a in Excel 07, along with various other string manipulation functions. To cater for all versions do it like this Dim sOld As String, sNew 'code sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew Regards, Peter T "Monk" wrote in message ... I get an error on the Replace part of the following code for people who are still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
I must have been in a hurry when I posted that suggestion (I didn't test
it - sorry about that). Replace sRep with sNew. Dim sOld As String, sNew As String sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sNew = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew If you have Excel2000 or later you should be able to to test both Replace and Substitute methods and end up with the same result in sNew. Regards, Peter T "Monk" wrote in message ... Thanks Peter. I seem to have one final problem in that for 97 users the resultant spreadsheet does not show any data. I think it may have to do with this line on filtering; should it also have a reference to sRep? My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew I have attached the full code if that will assist. Cheers '<<<< Create a new sheet for every Unique value 'This example copy all rows with the same value in the first column of 'the range to a new worksheet. It will do this for every unique value. 'The sheets will be named after the Unique value. 'Note: this example use the function LastRow in the ModReset module Sub SeparateAdmin() 'Note: This macro use the function LastRow Dim My_Range As Range Dim FieldNum As Long Dim CalcMode As Long Dim ViewMode As Long Dim ws2 As Worksheet Dim Lrow As Long Dim cell As Range Dim CCount As Long Dim WSNew As Worksheet Dim ErrNum As Long Dim sOld As String, sNew As String, sRep As String 'Set filter range on ActiveSheet: A11 is the top left cell of your filter range 'and the header of the first column, D is the last column in the filter range. 'You can also add the sheet name to the code like this : 'Worksheets("Sheet1").Range("A11:D" & LastRow(Worksheets("Sheet1"))) 'No need that the sheet is active then when you run the macro when you use this. Set My_Range = Range("A1:k" & LastRow(ActiveSheet)) My_Range.Parent.Select If ActiveWorkbook.ProtectStructure = True Or _ My_Range.Parent.ProtectContents = True Then MsgBox "Sorry, not working when the workbook or worksheet is protected", _ vbOKOnly, "Copy to new worksheet" Exit Sub End If 'This example filters on the first column in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = 11 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False 'Change ScreenUpdating, Calculation, EnableEvents, .... With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add With ws2 'first we copy the Unique data from the filter field to ws2 My_Range.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True 'loop through the unique list in ws2 and filter/copy to a new sheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A2:A" & Lrow) 'Filter the range sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew 'Check if there are no more then 8192 areas(limit of areas) CCount = 0 On Error Resume Next CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible ) _ .Areas(1).Cells.Count On Error GoTo 0 If CCount = 0 Then MsgBox "There are more than 8192 areas for the value : " & cell.Value _ & vbNewLine & "It is not possible to copy the visible data." _ & vbNewLine & "Tip: Sort your data before you use this macro.", _ vbOKOnly, "Split in worksheets" Else 'Add a new worksheet Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count)) On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then ErrNum = ErrNum + 1 WSNew.Name = "Error_" & Format(ErrNum, "0000") Err.Clear End If On Error GoTo 0 'Copy the visible data to the new worksheet My_Range.SpecialCells(xlCellTypeVisible).Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher ' Remove this line if you use Excel 97 '.PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With End If 'Show all data in the range My_Range.AutoFilter Field:=FieldNum Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False If ErrNum 0 Then MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _ & vbNewLine & "There are characters in the name that are not allowed" _ & vbNewLine & "in a sheet name or the worksheet already exist." End If Application.DisplayAlerts = False Sheets("Combine Sheet").Select ActiveWindow.SelectedSheets.Delete Range("A1").Select Sheets("Kylie").Select 'Windows("AET Client List.xls").Activate 'ActiveWindow.LargeScroll ToRight:=-2 'ActiveWindow.Close 'ActiveWindow.LargeScroll ToRight:=-2 Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Peter T" wrote: Sounds like your module is headed Option Explicit (good) which means any routine that attempts to use an undeclared variable will halt. In the code I posted I forgot to declare sRep! Change Dim sOld As String, sNew to Dim sOld As String, sNew As String, sRep As String Regards, Peter T "Monk" wrote in message ... Thanks for your assistance Peter I am getting a variable not defined error on the "sRep". Can you advise how to fix this as well? Regards "Peter T" wrote: Replace is n/a in Excel 07, along with various other string manipulation functions. To cater for all versions do it like this Dim sOld As String, sNew 'code sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew Regards, Peter T "Monk" wrote in message ... I get an error on the Replace part of the following code for people who are still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error message for Excel 97 users
Excellent. Works perfectly. Thanks Peter for your help.
"Peter T" wrote: I must have been in a hurry when I posted that suggestion (I didn't test it - sorry about that). Replace sRep with sNew. Dim sOld As String, sNew As String sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sNew = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew If you have Excel2000 or later you should be able to to test both Replace and Substitute methods and end up with the same result in sNew. Regards, Peter T "Monk" wrote in message ... Thanks Peter. I seem to have one final problem in that for 97 users the resultant spreadsheet does not show any data. I think it may have to do with this line on filtering; should it also have a reference to sRep? My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew I have attached the full code if that will assist. Cheers '<<<< Create a new sheet for every Unique value 'This example copy all rows with the same value in the first column of 'the range to a new worksheet. It will do this for every unique value. 'The sheets will be named after the Unique value. 'Note: this example use the function LastRow in the ModReset module Sub SeparateAdmin() 'Note: This macro use the function LastRow Dim My_Range As Range Dim FieldNum As Long Dim CalcMode As Long Dim ViewMode As Long Dim ws2 As Worksheet Dim Lrow As Long Dim cell As Range Dim CCount As Long Dim WSNew As Worksheet Dim ErrNum As Long Dim sOld As String, sNew As String, sRep As String 'Set filter range on ActiveSheet: A11 is the top left cell of your filter range 'and the header of the first column, D is the last column in the filter range. 'You can also add the sheet name to the code like this : 'Worksheets("Sheet1").Range("A11:D" & LastRow(Worksheets("Sheet1"))) 'No need that the sheet is active then when you run the macro when you use this. Set My_Range = Range("A1:k" & LastRow(ActiveSheet)) My_Range.Parent.Select If ActiveWorkbook.ProtectStructure = True Or _ My_Range.Parent.ProtectContents = True Then MsgBox "Sorry, not working when the workbook or worksheet is protected", _ vbOKOnly, "Copy to new worksheet" Exit Sub End If 'This example filters on the first column in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = 11 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False 'Change ScreenUpdating, Calculation, EnableEvents, .... With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add With ws2 'first we copy the Unique data from the filter field to ws2 My_Range.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True 'loop through the unique list in ws2 and filter/copy to a new sheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A2:A" & Lrow) 'Filter the range sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew 'Check if there are no more then 8192 areas(limit of areas) CCount = 0 On Error Resume Next CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible ) _ .Areas(1).Cells.Count On Error GoTo 0 If CCount = 0 Then MsgBox "There are more than 8192 areas for the value : " & cell.Value _ & vbNewLine & "It is not possible to copy the visible data." _ & vbNewLine & "Tip: Sort your data before you use this macro.", _ vbOKOnly, "Split in worksheets" Else 'Add a new worksheet Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count)) On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then ErrNum = ErrNum + 1 WSNew.Name = "Error_" & Format(ErrNum, "0000") Err.Clear End If On Error GoTo 0 'Copy the visible data to the new worksheet My_Range.SpecialCells(xlCellTypeVisible).Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher ' Remove this line if you use Excel 97 '.PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With End If 'Show all data in the range My_Range.AutoFilter Field:=FieldNum Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False If ErrNum 0 Then MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _ & vbNewLine & "There are characters in the name that are not allowed" _ & vbNewLine & "in a sheet name or the worksheet already exist." End If Application.DisplayAlerts = False Sheets("Combine Sheet").Select ActiveWindow.SelectedSheets.Delete Range("A1").Select Sheets("Kylie").Select 'Windows("AET Client List.xls").Activate 'ActiveWindow.LargeScroll ToRight:=-2 'ActiveWindow.Close 'ActiveWindow.LargeScroll ToRight:=-2 Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Peter T" wrote: Sounds like your module is headed Option Explicit (good) which means any routine that attempts to use an undeclared variable will halt. In the code I posted I forgot to declare sRep! Change Dim sOld As String, sNew to Dim sOld As String, sNew As String, sRep As String Regards, Peter T "Monk" wrote in message ... Thanks for your assistance Peter I am getting a variable not defined error on the "sRep". Can you advise how to fix this as well? Regards "Peter T" wrote: Replace is n/a in Excel 07, along with various other string manipulation functions. To cater for all versions do it like this Dim sOld As String, sNew 'code sOld = cell.Value #If VBA6 Then ' Excel 2000 or later sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?", "~?") #Else ' Excel 97 With Application.WorksheetFunction sRep = .Substitute(.Substitute(.Substitute(sOld, _ "~", "~~"), "*", "~*"), "?", "~?") End With #End If My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew Regards, Peter T "Monk" wrote in message ... I get an error on the Replace part of the following code for people who are still using Excel 97. I have tried using substitute instead but this also does not seem to work. Any suggestions would be appreciated. My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Excel Code returning my error message twice instead of once.... | Excel Programming | |||
Code Error Message Excel 2000 - 2003 | Excel Programming | |||
Need Error Message Box In VBA Code - Excel 2000 & 2003 | Excel Programming | |||
"File Is Locked" Error Message (not by other users) | Excel Discussion (Misc queries) | |||
Help with Error Message Box code | Excel Programming |