use ‘left’ in VBA (2003)

Topic

New Reply

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

Replies

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

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

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

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

Reply | Quote

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

OK, we’ll leave it at that then.

Reply | Quote

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

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

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

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

Perfect!! Another task completed!!

Thanks Hans, & Goodnight.

Nathan

Added later: Thanks Steve also……

Reply | Quote

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