How do you make a VBA function in removing a specific value in a [Closed]

Report
Posts
2
Registration date
Monday June 6, 2016
Status
Member
Last seen
June 6, 2016
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
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 reply

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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
Posts
2
Registration date
Monday June 6, 2016
Status
Member
Last seen
June 6, 2016

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
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!