Trying to improving existing code (portion of)

C

Cameron

Hi all,
Have the following code(portion of) and would like to hear of any
suggestions to making it more efficient.
TIA
Cameron
============================================================================
========
'TextBox3.Text has to have some value before getting to this point, so
include it
' in the ActiveCell.Value now.
ActiveCell.Value = TextBox3.Text
'Check if the remaining TextBoxes have any value and collate.
If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text = ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text

If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox5.Text
If TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox6.Text

If TextBox4.Text <> "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text & vbLf & _
TextBox5.Text

If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text & vbLf & _
TextBox6.Text

If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox5.Text & vbLf & _
TextBox6.Text

If TextBox4.Text <> "" And TextBox5.Text <> "" And TextBox6.Text <> ""
Then _
ActiveCell.Value = ActiveCell.Value & vbLf & _
TextBox4.Text & vbLf & _
TextBox5.Text & vbLf & _
TextBox6.Text
============================================================================
========
 
B

Bob Phillips

Hi Cameron,

You don't need all of those tests, they will append the value to the
activecell multiple times. All you need is

'TextBox3.Text has to have some value before getting to this point, so
include it
' in the ActiveCell.Value now.
With ActiveCell
.Value = TextBox3.Text
'Check if the remaining TextBoxes have any value and collate.
If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox4.Text
If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox5.Text
If TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
.Value = .Value & vbLf & TextBox6.Text
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Brad Vontur

This should be a little better

Dim strVal As Strin

strVal = TextBox3.Tex
If TextBox4.Text <> "" Then strVal = strVal & vbLf & TextBox4.Tex
If TextBox5.Text <> "" Then strVal = strVal & vbLf & TextBox5.Tex
If TextBox6.Text <> "" Then strVal = strVal & vbLf & TextBox6.Tex

ActiveCell.Value = strVa

-Brad Vontur
 
C

Cameron

Bob, many thanks for your suggestion.

Bob Phillips said:
Hi Cameron,

You don't need all of those tests, they will append the value to the
activecell multiple times. All you need is

'TextBox3.Text has to have some value before getting to this point, so
include it
' in the ActiveCell.Value now.
With ActiveCell
.Value = TextBox3.Text
'Check if the remaining TextBoxes have any value and collate.
If TextBox4.Text <> "" And TextBox5.Text = "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox4.Text
If TextBox4.Text = "" And TextBox5.Text <> "" And TextBox6.Text = ""
Then _
.Value = .Value & vbLf & TextBox5.Text
If TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text <> ""
Then _
.Value = .Value & vbLf & TextBox6.Text
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


============================================================================
============================================================================
Click to expand...
 
C

Cameron

Brad,
A little better - I should have seen that myself.
Thank-you for highlighting my own oversight.
Cheers,
Cameron
 
J

John Williams

Cameron said:
Hi all,
Have the following code(portion of) and would like to hear of any
suggestions to making it more efficient.
Click to expand...

Each textbox is appended to the active cell only if it isn't blank, so
this can be simplified by considering each textbox in turn:

Dim text As String

ActiveCell.Value = textbox3.text
text = ActiveCell.Value
If textbox4.text <> "" Then text = text & vbLf & textbox4.text
If textbox5.text <> "" Then text = text & vbLf & textbox5.text
If textbox6.text <> "" Then text = text & vbLf & textbox6.text
ActiveCell.Value = text
 
Top