New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Replace space with stars on selected range

Macros, VBA, Excel Automation, etc.

Replace space with stars on selected range

Postby Xokiru » Mon May 23, 2016 8:48 am

Hello everyone :)

Noobie needs help, I'm having a problem with replacing space with stars.
This code is for two functions:
1) Add stars in front and at the end of text
2) Replace space with stars

First function works, but unfortunately the second one is not. It replaces space with stars on all workbook, I want it to replace only on selected cells.

How to replace space with stars on selected range?


Code: Select all
Sub Stars()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "*" & c.Value & "*"
    Sheets(ActiveSheet.Name).Select
    Selection.Replace What:=" ", Replacement:="*", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next
End Sub


Thank you in advance for any help!
  • 0

Xokiru
Rookie
 
Posts: 2
Joined: May 23, 2016
Reputation: 0
Excel Version: 2013

Re: Replace space with stars on selected range

Postby Xokiru » Tue May 24, 2016 1:47 am

It appears, that it is sometimes working, sometimes not! :? So far its working as it should, but I still wonder what causes it to replace spaces with stars on all workbook?

If anyone has any idea, please share :)
  • 0

Xokiru
Rookie
 
Posts: 2
Joined: May 23, 2016
Reputation: 0
Excel Version: 2013


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 64 guests