9 Replies - 795 Views - Last Post: 05 October 2013 - 10:08 PM Rate Topic: -----

#1 vbrookie2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-September 13

Complex String Expressions

Posted 02 October 2013 - 11:25 AM

I created the following subroutine but I'm trying to condense the for x loop into 1 line of code.

Public Sub CreateEmailAddress()
 Dim intNumRows As Integer
 Dim x As Integer
 Dim strEmail As String
 
 intNumRows = Sheet1.UsedRange.Rows.Count
 
 For x = 2 To intNumRows
    strEmail = Left$(Sheet1.Cells(x, "C"), 1) & Sheet1.Cells(x, "b")
    strEmail = strEmail & Right$(Sheet1.Cells(x, "A"), 2)
    strEmail = strEmail & "@bigcompany.com"
    strEmail = LCase$(strEmail)
    Sheet1.Cells(x, "D") = strEmail
 Next x

End Sub



So far I've gotten this far before the results start getting wacky:
 For x = 2 To intNumRows
    strEmail = Left$(Sheet1.Cells(x, "C"), 1) & Sheet1.Cells(x, "b") & Right$(Sheet1.Cells(x, "A"), 2)  & "@bigcompany.com"
    strEmail = LCase$(strEmail)
    Sheet1.Cells(x, "D") = strEmail
 Next x



I'm not sure how to enter the LCase$ and the result location in the same line as the one defining the string. If anyone can please point me in the right direction, I'd greatly appreciate it!

Is This A Good Question/Topic? 0
  • +

Replies To: Complex String Expressions

#2 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,057
  • Joined: 02-July 08

Re: Complex String Expressions

Posted 02 October 2013 - 11:34 AM

Is this VBA?
Was This Post Helpful? 0
  • +
  • -

#3 vbrookie2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-September 13

Re: Complex String Expressions

Posted 02 October 2013 - 11:36 AM

Yes, am I in the wrong forum?
Was This Post Helpful? 0
  • +
  • -

#4 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,057
  • Joined: 02-July 08

Re: Complex String Expressions

Posted 02 October 2013 - 11:55 AM

Yes, I'll have a Mod move it for you.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,677
  • Joined: 12-December 12

Re: Complex String Expressions

Posted 02 October 2013 - 12:31 PM

Moved to VB6 which is much closer to VBA.

You would just wrap the whole of your first assignment in LCase$:

strEmail = LCase$(..)

But I question why you want to do this?

  • It is much easier to read (to maintain, and to debug) if split across a few lines.
  • It will make no difference at all to performance.

Was This Post Helpful? 0
  • +
  • -

#6 vbrookie2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-September 13

Re: Complex String Expressions

Posted 03 October 2013 - 08:28 AM

I'm only doing this for homework purposes, outside of the classroom I agree with you 100%. For some reason my subscription was removed once this post was moved to the correct forum so I wasn't aware it had been moved yet. Shortly after I posted this question, I came to this point
For x = 2 To intNumRows
    strEmail = LCase$(Left$(Sheet1.Cells(x, "C"), 1) & Sheet1.Cells(x, "b") & Right$(Sheet1.Cells(x, "A"), 2) & "@bigcompany.com")
    Sheet1.Cells(x, "D") = strEmail
 Next x



Is it possible to combine these into 1 line of code? I tried defining strEmail after Sheet1.Cells(x, "D") but it started returning FALSE values.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,677
  • Joined: 12-December 12

Re: Complex String Expressions

Posted 03 October 2013 - 08:37 AM

You can skip the variable strEmail altogether:

Sheet1.Cells(x, "D") = LCase$(...)

Was This Post Helpful? 0
  • +
  • -

#8 vbrookie2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-September 13

Re: Complex String Expressions

Posted 03 October 2013 - 09:23 AM

Wow, I racked my brain for a good 8 hours yesterday, trying everything I could think of. Sometimes the answer is right in front of you, you just need another pair of eyes.

Thanks so much and I look forward to continuing to learn more about VBA.
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3190
  • View blog
  • Posts: 10,677
  • Joined: 12-December 12

Re: Complex String Expressions

Posted 03 October 2013 - 10:23 AM

Glad I could help.

You might find this tutorial useful:

Working With The Excel Object Model
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: Complex String Expressions

Posted 05 October 2013 - 10:08 PM

So tell the nice man that you appreciate his help by clicking the green plus under his answer. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1