Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    use 'left' in VBA (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use 'left' in VBA (2003)

    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?

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use 'left' in VBA (2003)

    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

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use 'left' in VBA (2003)

    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
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use 'left' in VBA (2003)

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

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use 'left' in VBA (2003)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use 'left' in VBA (2003)

    OK, we'll leave it at that then.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use 'left' in VBA (2003)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use 'left' in VBA (2003)

    You could replace the line
    <code>
    MsgBox "Matches successfully created"
    </code>
    with
    <code>
    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
    </code>
    You can modify this to suit your preferences, of course.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use 'left' in VBA (2003)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use 'left' in VBA (2003)

    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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: use 'left' in VBA (2003)

    Use FORMAT: (for example):

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

    Steve

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use 'left' in VBA (2003)

    Perfect!! Another task completed!!

    Thanks Hans, & Goodnight.

    Nathan

    Added later: Thanks Steve also......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •