• use ‘left’ in VBA (2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » use ‘left’ in VBA (2003)

    • This topic has 12 replies, 3 voices, and was last updated 17 years ago.
    Author
    Topic
    WSVegasNath
    AskWoody Lounger
    April 14, 2008 at 10:45 pm #450212

    Hi,

    If Range(“F” & lngRow) = “” Then
    Range(“Q” & lngRow) = “=”””””
    Else
    Range(“Q” & lngRow) = Range(“F” & lngRow) & ” – ” & Range(“H” & lngRow)
    End If

    How do I adjust this to say ‘= Range(“F left 14 characters” & lngRow) & ” – ” & Range(“H” & lngRow)’

    Thanks
    Nath

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      April 14, 2008 at 10:51 pm #1105610

      If you mean “the first 14 characters of the value in column F” instead of “F left 14 characters” & lngRow, try

      Range(“Q” & lngRow) = Left(Range(“F” & lngRow), 14) & ” – ” & Range(“H” & lngRow)

      Otherwise, what do you want to accomplish?

      Reply | Quote
      • WSVegasNath
        AskWoody Lounger
        April 14, 2008 at 11:16 pm #1105615

        Hans,
        Sorry, that is exactly what I meant, and it works perfectly, Thanks.

        I have adjusted the code that you assisted me with recently for a different scenario, which is working fine except I am reporting ‘match’ on blank rows. Any idea’s where my error is??

        Long piece of code moved to attachment by HansV

        Many Thanks
        Nath

        Reply | Quote
        • WSVegasNath
          AskWoody Lounger
          April 14, 2008 at 11:16 pm #1105618

          Hans,
          False alarm!!
          I had the same number of blank debit rows as blank credit rows, which is why they were reporting as match. I would’nt imagine that there is a way around this happening? But then I don’t suppose it would be a major problem if it did.

          Thanks again,
          Nath

          Reply | Quote
          • WSHansV
            AskWoody Lounger
            April 14, 2008 at 11:24 pm #1105621

            If you wish, you can add a check for blank rows.

            Reply | Quote
            • WSVegasNath
              AskWoody Lounger
              April 14, 2008 at 11:33 pm #1105623

              Hans, I’m not sure where the code would need to change to accomodate this, and to be honest, it would probably be more trouble than it is worth.

              Thanks,
              Nath

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              April 14, 2008 at 11:36 pm #1105624

              OK, we’ll leave it at that then.

              Reply | Quote
            • WSVegasNath
              AskWoody Lounger
              April 14, 2008 at 11:44 pm #1105626

              I have named cells:

              X_DR_MATCH_COUNT
              X_DR_MATCH_VALUE
              X_CR_MATCH_COUNT
              X_CR_MATCH_VALUE

              Would it be possible to report these in my ‘End MSGBOX’

              The two ‘counts’ should be equal as should the two ‘values’. Maybe a MSGBOX that reports any differences between the counts or values?

              Regards,
              Nath

              Added later:

              Though in thinking that, the blank row matches may distort the result. I may need to re-think this.

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              April 14, 2008 at 11:58 pm #1105629

              You could replace the line

              MsgBox "Matches successfully created"

              with

              Dim strMsg As String
              strMsg = "Finished matching."
              strMsg = strMsg & vbCrLf & "Match count: " & Range("X_DR_MATCH_COUNT")
              If Not Range("X_DR_MATCH_COUNT") = Range("X_CR_MATCH_COUNT") Then
              strMsg = strMsg & " (vs " & Range("X_CR_MATCH_COUNT") & ")"
              End If
              strMsg = strMsg & vbCrLf & "Match value: " & Range("X_DR_MATCH_VALUE")
              If Not Range("X_DR_MATCH_VALUE") = Range("X_CR_MATCH_VALUE") Then
              strMsg = strMsg & " (vs " & Range("X_CR_MATCH_VALUE") & ")"
              End If
              MsgBox strMsg, vbInformation

              You can modify this to suit your preferences, of course.

              Reply | Quote
            • WSVegasNath
              AskWoody Lounger
              April 15, 2008 at 12:50 am #1105646

              Hans,
              Thanks for this! I have used (which better suit’s my needs)

              Dim strMsg As String
              strMsg = “Matches Complete……..”
              strMsg = strMsg & vbCrLf
              strMsg = strMsg & vbCrLf & ” Debits Matched = ” & Range(“X_DR_MATCH_COUNT”) & ” @ ” _
              & Range(“X_DR_MATCH_VALUE”)
              strMsg = strMsg & vbCrLf
              strMsg = strMsg & vbCrLf & “Credits Matched = ” & Range(“X_CR_MATCH_COUNT”) & ” @ ” _
              & Range(“X_CR_MATCH_VALUE”)
              strMsg = strMsg & vbCrLf
              strMsg = strMsg & vbCrLf & “Matching Errors = ” & Range(“X_MATCH_COUNT_ERRORS”) & ” @ ” _
              & Range(“X_MATCH_VALUE_ERRORS”)
              strMsg = strMsg & vbCrLf
              MsgBox strMsg, vbInformation

              Which gives me:

              Matches Complete……

              Debits Matched = 2211 @ 373272.96

              Credits Matched = 2211 @ 373272.95999999999

              Matching Errors = 0 @ 0

              Can the value’s be specified to 2 decimal places preferably with a thousand seperator? The cells are defined that way, so I don’t understand why I get three different variants?

              Thanks
              Nath

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              April 15, 2008 at 1:02 am #1105653

              VBA doesn’t look at the way the cell is formatted in Excel, it only looks at the underlying value. You can use the Format function to display the numbers the way you want:

              strMsg = strMsg & vbCrLf & ” Debits Matched = ” & Range(“X_DR_MATCH_COUNT”) & ” @ ” _
              & Format(Range(“X_DR_MATCH_VALUE”), “#,##0.00”)

              and similar for the other one.

              Reply | Quote
            • WSVegasNath
              AskWoody Lounger
              April 15, 2008 at 1:13 am #1105657

              Perfect!! Another task completed!!

              Thanks Hans, & Goodnight.

              Nathan

              Added later: Thanks Steve also……

              Reply | Quote
            • WSsdckapr
              AskWoody Lounger
              April 15, 2008 at 1:04 am #1105654

              Use FORMAT: (for example):

              strMsg = strMsg & vbCrLf & ” Debits Matched = ” & Range(“X_DR_MATCH_COUNT”) & ” @ ” _
              & format(& Range(“X_DR_MATCH_VALUE”),”#,##0.00″)

              Steve

              Reply | Quote
    Viewing 0 reply threads
    Reply To: use ‘left’ in VBA (2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.