Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Trying to improving existing code (portion of)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default 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
================================================== ==========================
========


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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

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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default 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


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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Really need help with existing code. Cam Excel Discussion (Misc queries) 0 August 12th 08 07:14 PM
Help with existing code ploddinggaltn Excel Discussion (Misc queries) 1 November 27th 06 09:46 PM
Help in Modification of existing code JMay Excel Programming 11 February 28th 04 08:11 PM
Improving code.....For Next Mat Excel Programming 3 October 30th 03 06:01 AM
Code for existing links Sandy[_3_] Excel Programming 3 July 15th 03 07:42 PM


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement