I have a question related to a previous post about TRIM()

[TonyU]

thread707-281842

Is it possible to code the macro to prompt the user to select the column that needs to be trimmed?? and how. Please help. Thanks

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[SkipVought]

Hi,

Sub TrimColumn()
    Col = InputBox("What Column?", vbOKCancel)
    If Col = vbCancel Then Exit Sub
    'now trim column Col
    Set inst = Intersect(ActiveSheet.UsedRange, Columns(Col))
    
    For Each c In inst
        c.Value = Trim(c.Value)
    Next
End Sub

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[TonyU]

Should I type "G" or "7" when asked "What column" ?

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[acron]

You would type "G", as if you type a number an error would result.

A suggestion, rather than have your user input which column, why not have them select a cell in the required column just before they elect to execute the macro, that is work with the active column.The following is an adaption of Skip's code to do just that.

Sub TrimColumn()
    Dim inst as range
    Set inst = Intersect(ActiveSheet.UsedRange, _
        Columns(ActiveCell.Column))
    For Each c In inst
        c.Value = Trim(c.Value)
    Next
End Sub

A.C.

 

[TonyU]

thanks for your response acron.

I copied your code and typed three random names in column f
and ran the macro but nothing happened

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[SkipVought]

Either a number OR letter works for Column choice.

;-) Skip,
SkipAndMary1017@mindspring.com

 

[TonyU]

Both a number and letter did not work and I get a type mismatch

When I typed "G" I get this
Run time error '13'
in orange below

Sub TrimColumn()
Col = InputBox("What Column?", vbOKCancel)
If Col = vbCancel Then Exit Sub
'now trim column Col
Set inst = Intersect(ActiveSheet.UsedRange, Columns(Col))

For Each C In inst
C.Value = Trim(C.Value)
Next
End Sub



When I type number "7" I get this
runt-time error '1004'
Aplication-defined or object-defined error
in teal below

Sub TrimColumn()
Col = InputBox("What Column?", vbOKCancel)
If Col = vbCancel Then Exit Sub
'now trim column Col
Set inst = Intersect(ActiveSheet.UsedRange, Columns(Col))

For Each C In inst
C.Value = Trim(C.Value)
Next
End Sub

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[acron]

TonyU :
are you sure nothing happens ?

Place some laeding spaces on some text in a cell and see if it works. TRIM() only clears leading or trailing spaces.

Skip :
The ordinary InputBox returns a String. I know if you use the Application.InputBox in excel, you have more choice in what is returned.

A.C.

 

[TonyU]

Acron, here's the deal



I found this code from a previous post and I tested it and it works perfect. I just want to be able to prompt my users for a field name and or number using the same code.


[tt]HERE'S THE CODE

Sub SampleCode()
Dim intCol As Integer 'Column Number
Dim intRow As Integer 'Row Number
Dim strWS As String 'Worksheet Name
Dim strName As String 'full name field
Dim strSearchChar As String
Dim intPos As Integer 'Pos of Blank in strName
Dim strTemp As String 'rebuild strName

strSearchChar = " "
intCol = 1 'Column A
strWS = "Sheet1"

For intRow = 1 To 18922 'Row Numbers to loop through
strTemp = ""
strName = Sheets(strWS).Cells(intRow, intCol).Value
Do
'Find position of 1st blank in strName
intPos = InStr(1, strName, strSearchChar, 1)
If intPos <> 0 Then
strTemp = strTemp & &quot; &quot; & Left(strName, intPos - 1)
strName = Trim(Right(strName, Len(strName) - intPos))
Else
strTemp = strTemp & &quot; &quot; & Trim(strName)
End If
Loop Until intPos = 0
'MsgBox strTemp
Sheets(strWS).Cells(intRow, intCol).Value = strTemp
Next intRow
End Sub

[tt]&quot;A Successful man is one who can build a firm foundation with the bricks that others throw at him&quot;[/tt]

​

 

[uberpudge]

Hello Tony,

I noticed a few problems in the example you are having trouble with. Try this and let us know how it works:

Sub TrimColumn()
Dim varCol As Variant
Dim rngColumnToTrim As Range
Dim rngCell As Range

'Get input from the user as the column to trim.
varCol = Interaction.InputBox(&quot;What column would you like to be trimmed?&quot;, &quot;Input required&quot;, vbOKCancel)

'InputBox returns an empty string (&quot;&quot if &quot;Cancel&quot; is clicked, so
'if an empty string is returned, exit the sub.
If varCol = &quot;&quot; Then Exit Sub

'Test to see if the input is a string representation of a number.
If Information.IsNumeric(varCol) Then
'If so, convert the string to a number. I use a Long data type out of habit.
'Technically, any numeric data type should work because there can only be
'256 columns.
varCol = Conversion.CLng(varCol)
End If

'Now set the range to remove leading and trailing spaces from.
Set rngColumnToTrim = Intersect(ActiveSheet.UsedRange, Columns(varCol))

'Test to make sure a valid column (one with data) was selected.
If Not rngColumnToTrim Is Nothing Then
'For each cell in the Range...
For Each rngCell In rngColumnToTrim
'Remove leading and trailing spaces.
rngCell.Value = Trim(rngCell.Value)
Next
Else
'Let the user know a mistake was made on their end.
Interaction.MsgBox &quot;The column you selected does not contain valid data.&quot; + Constants.vbCrLf + &quot;Please select a different column.&quot;
End If
End Sub

Hope this helps,
Pete

 

[kevinclark]

Hi Tony


The code from the earlier post was intended to remove excess blanks between text in addition to any leading or trailing blanks. It would be bit of an over kill if all you need to do is remove leading and trailing spaces, in which case using the Trim function as suggested / posted by Skip and uberpudge would be your best bet. If your still intent on using the code from that earlier posting you should refer back to that thread to view / understand a code correction (it was mistakingly inserting a leading space). Also as suggested by Skip and uberpudge adjust the code to use Long for the Row variable (A habit I should get in to).


In regards to having the user enter a column number or letter, I am wondering if you could simply have the user select a column (click on the column) and then capture and send the column number into the the trimming routine (Which ever one you use).


example:

Sub Button1_Click()

Dim lngCol As Long
Dim lngPrompt As Long
lngCol = ActiveCell.Column
lngPrompt = MsgBox(&quot;This will trim all text under column &quot; _
& lngCol & &quot;. Continue ?&quot;, vbYesNo + vbQuestion, &quot;Trim ?&quot

If lngPrompt = vbYes Then
SampleCode lngCol
End If

End Sub

Sub SampleCode(lngCol As Long)

Dim lngRow As Integer 'Row Number
Dim strWS As String 'Worksheet Name
Dim strName As String 'full name field
Dim strSearchChar As String
Dim intPos As Integer 'Pos of Blank in strName
Dim strTemp As String 'rebuild strName

strSearchChar = &quot; &quot;
intCol = 1 'Column A
strWS = &quot;Sheet1&quot;

For lngRow = 1 To 18922 'Row Numbers to loop through
strTemp = &quot;&quot;
strName = Trim(Sheets(strWS).Cells(lngRow, intCol).Value)
Do
'Find position of 1st blank in strName
intPos = InStr(1, strName, strSearchChar, 1)
If intPos <> 0 Then
'strTemp = strTemp & &quot; &quot; & Left(strName, intPos - 1)
strTemp = strTemp & Left(strName, intPos)
strName = Trim(Right(strName, Len(strName) - intPos))
Else
strTemp = strTemp & Trim(strName)
End If
Loop Until intPos = 0
'MsgBox strTemp
Sheets(strWS).Cells(lngRow, intCol).Value = strTemp
Next lngRow
End Sub

 

[kevinclark]

Sub Button1_Click()

Dim lngCol As Long
Dim lngPrompt As Long
lngCol = ActiveCell.Column
lngPrompt = MsgBox(&quot;This will trim all text under column &quot; _
& lngCol & &quot;. Continue ?&quot;, vbYesNo + vbQuestion, &quot;Trim ?&quot

If lngPrompt = vbYes Then
SampleCode lngCol
End If

End Sub



Sub SampleCode(lngCol As Long)

Dim lngRow As Integer 'Row Number
Dim strWS As String 'Worksheet Name
Dim strName As String 'full name field
Dim strSearchChar As String
Dim intPos As Integer 'Pos of Blank in strName
Dim strTemp As String 'rebuild strName

strSearchChar = &quot; &quot;

strWS = &quot;Sheet1&quot;

For lngRow = 1 To 18922 'Row Numbers to loop through
strTemp = &quot;&quot;
strName = Trim(Sheets(strWS).Cells(lngRow, lngCol).Value)
Do
'Find position of 1st blank in strName
intPos = InStr(1, strName, strSearchChar, 1)
If intPos <> 0 Then
'strTemp = strTemp & &quot; &quot; & Left(strName, intPos - 1)
strTemp = strTemp & Left(strName, intPos)
strName = Trim(Right(strName, Len(strName) - intPos))
Else
strTemp = strTemp & Trim(strName)
End If
Loop Until intPos = 0
'MsgBox strTemp
Sheets(strWS).Cells(lngRow, lngCol).Value = strTemp
Next lngRow
End Sub

 

[jasonneuman]

How do you Read a file in and Write a file out in a Comma Delimited format. I realize this is probably basic stuff but I am really new to VBA.

Example I have a (file named F30001x30.txt) and I want to produced the comma delimited format into a (file named F30001q30.txt)

Any help would be greatly appreciated.

Jason Neuman