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
How do you make a VBA function in removing a specific value in a cell.

Example: remove "#, " and ", #"

#, 1, 2, 3
1, 2, #, 3
1, 2, 3, #

Results:
1, 2, 3
1, 2, 3
1, 2, 3

I really dont want to use the simple find and replace since my worksheet is big.. i need your help.. thank you in advance..

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
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.
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
0
KaeBee Posts 2 Registration date Monday June 6, 2016 Status Member Last seen June 6, 2016
Jun 6, 2016 at 10:50 PM
Hi RayH.. Thanks alot for this. However, i am receiving an error when typing in ":=".. is this normal?

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
0
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jun 7, 2016 at 10:31 AM
Did the code work for you just as I posted?

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?
0