How do you make a VBA function in removing a specific value in a
Closed
KaeBee
Posts
2
Registration date
Monday June 6, 2016
Status
Member
Last seen
June 6, 2016
-
Jun 6, 2016 at 11:25 AM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Jun 7, 2016 at 10:31 AM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Jun 7, 2016 at 10:31 AM
Related:
- How do you make a VBA function in removing a specific value in a
- Number to words in excel without vba - Guide
- Vba case like - Guide
- How to remove a number from blacklist - Guide
- Enable vba in excel - Guide
- Vba check if value is in array - Guide
1 response
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
26
Jun 6, 2016 at 03:28 PM
Jun 6, 2016 at 03:28 PM
Though still effectively a Search and Replace this is done in VBA.
This is a slightly modified version to that found in the original source website.
I have removed the multiple sheets part. See the source for the original version.
The Variable arrays:
fndList contains the entries to be found and
rplcList contains the replacement values for those found
This is a slightly modified version to that found in the original source website.
I have removed the multiple sheets part. See the source for the original version.
http://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all
Sub Multi_FindReplace()
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array(", #", "#,")
rplcList = Array("", "")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
Sheet1.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next x
End Sub
The Variable arrays:
fndList contains the entries to be found and
rplcList contains the replacement values for those found
Jun 6, 2016 at 10:50 PM
When i tried running the macro.. i got a syntax error. Starting here:
Sheet1.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False,
ReplaceFormat:=False
Next x
Jun 7, 2016 at 10:31 AM
You mean you get an error when you type in like this:
fndList = Array(":=", "#,")This works. So, no its not normal.
I have to ask as I have my tin-foil hat is blocking your thoughts.
What is the error?
About that syntax error. Does the code look like you have shown or like I have shown?