Removing spaces

[austenr]

I have a cell that text is entered into that also is used as the name of the workbook at the time the file closes. Is there a way to let it print say "this is the file name" and when the code needs it to be the file name strip out the spaces and make it "thisisthefilename"?

Thanks in advance


BTW I have really learned a lot from this board and want to pass along my thanks to everyone that has contributed...

[Zack Barresse]

Hi Austen,

Long time no hear.

So when is this supposed to fire? Is this a change event? Is this just a standar macro? Is this a Function?

[TonyJollans]

In your code, wherever it is, use ..

Replace(Range("sheet1!a1"), " ", "")

(obviously with your own cell)

[austenr]

Hey.. That won't compile. Says it is missing a =.

[Zack Barresse]

The long way ...

Sub giveNoSpaces()
    Dim i As Long, fileName As String, newName As String
    fileName = Range("A1").Text
    For i = Len(fileName) To 1 Step -1
        Select Case Mid(fileName, i, 1)
        Case Chr$(160), Chr$(32)
            If newName = "" Then
                newName = Left(fileName, i - 1) & Right(fileName, _
                    Len(fileName) - i)
            Else
                newName = Left(newName, i - 1) & Right(newName, _
                    Len(newName) - i)
            End If
        Case Else
        End Select
    Next i
    MsgBox newName
End Sub[/vba]
The short way:
[vba]Sub NoSpaces()
    MsgBox Replace(Range("A1").Value, " ", "")
End Sub

[austenr]

This will not compile for me. Still get the "Expecting =" error

If OptionButton1 = True Then
    Replace (Range("sheet1!b5"), " ", "")
    ThisWorkbook.SaveAs Range("B5").Value & ".xls"
    Unload Me
End If
 
If OptionButton2 = True Then
    Replace(Range("B5").Value, " ", "")
    ThisWorkbook.SaveAs Range("B5").Value & ".xls"
    Sheets.PrintOut
    Unload Me
End If

[Zack Barresse]

Are you trying this from a UserForm? Maybe something like this ...

If OptionButton1 = True Then
ThisWorkbook.SaveAs Replace(Range("sheet1!b5"), " ", "") & ".xls"
Unload Me
End If

If OptionButton2 = True Then
ThisWorkbook.SaveAs Replace(Range("B5").Value, " ", "") & ".xls"
Sheets.PrintOut
Unload Me
End If

[johnske]

Hi Austen,

The short way was giving me compile errors also, try this

Option Explicit

Sub ReplaceText()
Range("Sheet1!A1").Replace What:=" ", Replacement:=""
End Sub

[Zack Barresse]

Hi John,

I wasn't aware that you could refer to a sheet object along with the actual range like that in VBA w/o doing so with the Sheets Object (or implied); you learn something new everyday!

[austenr]

Hey Johnske,

while your code complies a run time error occurs:

Run Time Error '1004'

Method 'Range' of Object' _ Global' failed

[johnske]

Sorry Austen,

Forgot you're using '97, try this then

ActiveSheet.Range("A1").Replace What:=" ", Replacement:=""

@ Zack, yeah, only just found out 2000 accepts it

[johnske]

Hi John,

I wasn't aware that you could refer to a sheet object along with the actual range like that in VBA w/o doing so with the Sheets Object (or implied); you learn something new everyday!

Hmmmm,

Just checked, it accepts it without any errors, but doesn't 'activate' Sheet1 if it's not the active sheet - oh well - better stick with the old way...
EDIT: Ooops! Sorry, yes it DOES change sheet1 when run from Sheet2, I was trying it with the wrong macro

[TonyJollans]

Just skimmed the thread.

Replace came in with VBA 6 (Office 2K)

If you're using Excel 97, use ..

WorksheetFunction.Substitute(Range("A1")," ","")

.. instead

[mdmackillop]

Hi Austen
I came across the same error recently (sorry I lost my examples at home)
MD

Help suggests: - Replace("mystring", "ys", "xx")

I think you need

Sub tries()
test = Replace("mystring", "ys", "xx")
MsgBox test
End Sub

[Zack Barresse]

Hey Johnske,

while your code complies a run time error occurs:

Run Time Error '1004'

Method 'Range' of Object' _ Global' failed

What version did you run with? Ran good on my 2002. Curious.

[johnske]

Hi John,

I wasn't aware that you could refer to a sheet object along with the actual range like that in VBA w/o doing so with the Sheets Object (or implied); you learn something new everyday!

Hi Zack,

Outta curiosity (playing around with this reference style), I tried the code below, the sub "TryIt" wrote the sheet name (with a space) in A1 on every sheet, the sub "RemoveSpaces" then removed the spaces - On office 2k - dunno if it'd work in '97

Option Explicit
Sub TryIt()
Dim N%
For N = 1 To 3
Range("Sheet" & N & "!A1") = "Sheet " & N
Next N
End Sub
 
Sub RemoveSpaces()
Dim N%
For N = 1 To 3
Range("Sheet" & N & "!A1").Replace What:=" ", Replacement:=""
Next N
End Sub

[johnske]

PS

Zack, this also works. (i.e. runs the code in another workbook if that workbook is open) - it's the same referencing as is used on a worksheet when pasting a link

Option Explicit

Sub TryIt()
Dim N%
For N = 1 To 3
Range("[AnotherWorkbook.xls]Sheet" & N & "!A1") = "Sheet " & N
Next N
End Sub
 
Sub RemoveSpaces()
Dim N%
For N = 1 To 3
Range("[AnotherWorkbook.xls]Sheet" & N & "!A1").Replace What:=" ", Replacement:=""
Next N
End Sub

(the use of .xls appears to be optional)