PDA

View Full Version : [SOLVED] Removing spaces



austenr
11-15-2004, 05:08 PM
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
11-15-2004, 05:30 PM
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
11-15-2004, 05:36 PM
In your code, wherever it is, use ..


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

(obviously with your own cell)

austenr
11-16-2004, 08:49 AM
Hey.. That won't compile. Says it is missing a =.

Zack Barresse
11-16-2004, 09:48 AM
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
11-16-2004, 12:15 PM
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
11-16-2004, 12:57 PM
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
11-16-2004, 01:08 PM
Hi Austen,

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


Option Explicit

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

Zack Barresse
11-16-2004, 01:18 PM
Hi John, :006:

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! :yes

austenr
11-16-2004, 01:26 PM
Hey Johnske,

while your code complies a run time error occurs:

Run Time Error '1004'

Method 'Range' of Object' _ Global' failed

johnske
11-16-2004, 01:41 PM
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 :bink:

johnske
11-16-2004, 01:59 PM
Hi John, :006:

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! :yesHmmmm,

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... :bink:
EDIT: Ooops! Sorry, yes it DOES change sheet1 when run from Sheet2, I was trying it with the wrong macro :blush

TonyJollans
11-16-2004, 02:19 PM
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
11-16-2004, 04:09 PM
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
11-16-2004, 04:52 PM
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
11-16-2004, 05:07 PM
Hi John, :006:

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! :yesHi 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 :bink:

johnske
11-16-2004, 06:24 PM
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 :bink:

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)