Code: Select all
ActiveDocument.Bookmarks("myString").Range.Text = xlWsh.Range("A1").Text
Ken
Code: Select all
ActiveDocument.Bookmarks("myString").Range.Text = xlWsh.Range("A1").Text
Code: Select all
Dim strText As String
Dim i As Long
strText = xlWsh.Range("A1").Text
i = 1
Do While IsNumeric(Right(strText, i)) And i <= Len(strText)
i = i + 1
Loop
ActiveDocument.Bookmarks("myString").Range.Text = Right(strText, i - 1)
Code: Select all
With ActiveDocument.Bookmarks("myString").Range
If IsNumeric(Right(strText, 5)) Then
.Text = Right(strText, 5)
Else
.Text = Right(strText, 4)
End If
End With
I used to worry about such efficiencies until I tested Excel VBA and found my i3 dual core PC's and laptops were executing tens of millions of lines of VBA code (and gads more machine-level instructions!!) per second!Well, if you start with i = 4, IsNumeric(Right(strText, i)) will start off with the last four characters in the string, rather than having to iteratively and unnecessarily test the last 3 before getting to it.
Well, you are right of course, Macropod but unless that assumption (of 4 or 5) is enshrined as a Business Rule and thus sacrosanct to change, I would go with Hans' code because you never know when it might suddenly change. Actually, I might even start at right(1) and keep working left until it runs out of numerics, depending what the client says when I ask him.macropod wrote:Well, if you start with i = 4, IsNumeric(Right(strText, i)) will start off with the last four characters in the string, rather than having to iteratively and unnecessarily test the last 3 before getting to it. Indeed, you could forego the loop entirely, with:
Code: Select all
With ActiveDocument.Bookmarks("myString").Range
If IsNumeric(Right(strText, 5)) Then
.Text = Right(strText, 5)
ElseIf IsNumeric(Right(strText, 4)) Then
.Text = Right(strText, 4)
Else
.Text = ""
End If
End With
I don't think he stated it was a rule but merely what he wanted to do.macropod wrote:The 'rule' is stated in the first sentence of the first post. As such, it is not an assumption. My response concerns that rule, nothing else. As for your last statement, surely that's violating the rule. So what if there's a dozen digits, the specs call for no more than the last 5.