Compile error - Sub Function not defined

[JMay]

Above occurs on Line 4 below Activecell.value = .......

My intent is to clean up the extra characters (return type) being entered
into my cells. Perhaps there is a better way. But lost here...
TIA

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value =
Sheets("Sheet1").TextBox1.Text
ActiveCell.Value = Application.WorksheetFunction.Substitute(ActiveCell,
char(13), "")
With TextBox1
.Text = ""
.Activate
End With
End Sub

 

[Mike H]

Try

Chr(13) instead of Char(13)

Mike

 

[FSt1]

hi
change char(13) to chr(13)
and i think you have something wrong with your first line with the offset.

regards
FSt1

 

[Bob Phillips]

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = .TextBox1.Text
End With

With ActiveCell

.Value = Application.WorksheetFunction.Substitute(.Value, Chr(13),
"")
End With

With Sheets("Sheet1").TextBox1
.Text = ""
.Activate
End With
End Sub

 

[FSt1]

hi
in fact that whole first line don't look right.
try some thing like this instead.
Sheets("Sheet1").Cells(5000,1)..End(xlUp).Offset(1).Value = _
Sheets("Sheet1").TextBox1.Text

untested. i may be wrong.

regards
FSt1

 

[JMay]

Testing the current LEN() of all my entries I'm getting 2 more than desired.

I enter into textbox1 good (prssing the return key)

With
EnterKeyBehaviour = True
Multiline = True
Word Wrap = True

=Len() against cell containg good = 6, not 4

 

[Dave Peterson]

I'm betting that you have both the carriage return (chr(13) and linefeed
(chr(10)) in your string.

With ActiveCell
'xl2k+
'.value = replace(.value,vbcrlf,"")

'xl97
.Value = Application.Substitute(.Value, chr(13) & Chr(10), "")

'or
'.Value = Application.Substitute(.Value, vbcrlf,"")

End With

vbcrlf is the same as chr(13) & chr(10)
vblf is the same as chr(10)
vbcr is the same as chr(13)
vbnewline is OS dependent. On Wintel, it's vbcrlf. On Mac, it's vblf (IIRC).

So if your code may run on a mac, you may want to use vbnewline. (But I
wouldn't trust me without testing!)

 

[JMay]

Thanks Dave;

I made the changes as you suggested. For a while it still was not working
until I realized that the *&^^ screwy results were due to the "activecell"
location.

Final Code (with correction of this) is:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With Sheets("Sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = .TextBox1.Text
.Cells(Rows.Count, "A").End(xlUp).Select ' necessary to lock down
activecell
End With

With ActiveCell
.Value = Replace(.Value, vbCrLf, "")
End With

With Sheets("Sheet1").TextBox1
.Text = ""
.Activate
End With
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

Or you could just adjust the string when you plop it into the cell:

..Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Value _
= Replace(.TextBox1.Text, vbCrLf, "")

Then you don't have to select the cell or work on the activecell.

 

[Jim May]

Thanks Dave;
I think I'm beginning to sorta understand vba, after all these years AND
Especially for your input over that time.
Much appreciated,
Jim