[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

VB Excel replace wildcard

Is there a way to write and IF Then or Select Case constructs for an activecell that contains a certain character?
For example:

AAAAA/BBBBB*CCCCC


If Selection.Find(What:="/", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= xlPart)_
            = */* Then _
ActiveCell.Replace What:="/", Replacement:=",", LookAt:=xlPart
Else
End If


AAAAA,BBBBB*CCCCC


Is this possible without a find/replace?  Can, When, and How can I use the  * wildcard in the IF THEN construct?
0
beechjl
Asked:
beechjl
  • 2
2 Solutions
 
beechjlAuthor Commented:
The simplest answer is the best.  Thank you for your time
0
 
beechjlAuthor Commented:
I determined that I can you use a Like operatpr to change the / , +,  and - characters. Im having trouble still with replacing the other wildcards such as "<#[" and "*~**".
Example:
AAAAA*BBBBB<6[CCCCC
to
AAAAA,BBBBB,CCCCC

I have attached my code
Sub Replace()
'
' Replace Macro
' Macro recorded 12/23/2009 by mellerni
    If ActiveCell Like "*/*" Then
       ActiveCell.Replace What:="/", Replacement:=",", LookAt:=xlPart
    Else
    End If
    
    If ActiveCell Like "*+*" Then
        ActiveCell.Replace What:="+", Replacement:=",", LookAt:=xlPart
    Else
    End If
    
    If ActiveCell Like "*-*" Then
        ActiveCell.Replace What:="-", Replacement:=",", LookAt:=xlPart
    Else
    End If
    
    If ActiveCell Like "*<#[*" Then
        ActiveCell.Replace What:="<#[", Replacement:=",", LookAt:=xlPart
    Else
    End If
    
    If ActiveCell Like "*~**" Then
        ActiveCell.Replace What:="*", Replacement:=",", LookAt:=xlPart
    Else
    End If
    
End Sub

Open in new window

0
 
cd_morrisCommented:
IF does not take wildcards. COUNTIF, MATCH and VLOOKUP do take wildcards
0
 
patrickabCommented:
beechjl,

Try the macro below - it's in the attached file. Press the button to run the macro.

Patrick
Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim i As Long
Dim str1 As String
With Sheets("Sheet1")
    Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each celle In rng
    For i = 1 To Len(celle)
        Select Case Mid(celle, i, 1)
            Case Is = "/"
                str1 = str1 & ","
            Case Else
                str1 = str1 & Mid(celle, i, 1)
        End Select
    Next i
    celle.Offset(0, 1) = str1
    str1 = ""
Next celle

End Sub

Open in new window

replacer-02.xls
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now