ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Trying to improving existing code (portion of) (https://www.excelbanter.com/excel-programming/294050-trying-improving-existing-code-portion.html)

Cameron[_6_]
Trying to improving existing code (portion of)
 
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
================================================== ==========================
========

Bob Phillips[_6_]
Trying to improving existing code (portion of)
 
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)

"Cameron" wrote in message
...
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

================================================== ==========================
========

Brad Vontur[_2_]
Trying to improving existing code (portion of)
 
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

Cameron[_6_]
Trying to improving existing code (portion of)
 
Bob, many thanks for your suggestion.

"Bob Phillips" wrote in message
...
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)

"Cameron" wrote in message
...
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


================================================== ==========================
========

Cameron[_6_]
Trying to improving existing code (portion of)
 
Brad,
A little better - I should have seen that myself.
Thank-you for highlighting my own oversight.
Cheers,
Cameron

"Brad Vontur" wrote in message
...
This should be a little better:

Dim strVal As String

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

ActiveCell.Value = strVal

-Brad Vontur

John Williams[_4_]
Trying to improving existing code (portion of)
 
"Cameron" wrote in message ...
Hi all,
Have the following code(portion of) and would like to hear of any
suggestions to making it more efficient.

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


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com