Strange character on table range text

[Donquick]

Hi all,

New here - so thanks in advance for your help.

I have been automating a word document with some semi sophisticated bits of code and have now got stuck on the easiest thing i am trying to do. I am tring to examine the content of a table cell and change the text according to the current value. The rule is as follows: if it says "Low" change it to "Low to Moderate"; says "Low to moderate" change to "Moderate"; etc

I am using a field which is acting as a macro call on the neighbouring cell - which is working fine.

Anyway to get ot the point. I am examining the content of the cell by setting a range on that cell and then examining the .text property of the range and comparing it within a case statement to determine the behaviour.

The coding is fine BUT the .Text property for the range is coming up with a strange dot following the actual text. i.e. if the table cell says "Low", then the .Text property (examined during debug) is showing as "Low-", but with the hyphen i have shown as a bold dot.

I have tried trimming this text and saving to a string variable, but the dot is still there. I have tried testing for the word "Low" plus a chr(8) in case it is just a space and no luck and the word plus a chr(9) in case a tab and still no luck.

How can i clean my text from this mysterious wart, or if not possible include it in my condition when looking for a text match.

Here is the code just for the heck of it:

[VBA]Dim OnRg2 As Range

With ActiveDocument
Set OnRg2 = .Tables(1).Rows(1).Cells(2).Range
End With

Select Case OnRg2.Text
Case "Low"
OnRg2.Text = "Low to Moderate"
Case "Low to Moderate "
OnRg2.Text = "Moderate"
Case "Moderate "
OnRg2.Text = "Moderate to High"
Case "Moderate to High "
OnRg2.Text = "High"
Case "High "
OnRg2.Text = "Low"
End Select
[/VBA]

Thanks for any help.

[Donquick]

here is a cludge solution which works, but isn't terribly pretty. Would still be interested to understand more about the mystery dot if anybody knows.

[VBA] Dim OnRg2 As Range
Dim MyWord As String

With ActiveDocument
Set OnRg2 = .Tables(2).Rows(1).Cells(2).Range
End With

Select Case OnRg2.Words.Count
Case 2
MyWord = OnRg2.Words(1)
Case 3
MyWord = OnRg2.Words(1) & OnRg2.Words(2)
Case 4
MyWord = OnRg2.Words(1) & OnRg2.Words(2) & OnRg2.Words(3)
End Select

Select Case MyWord
Case "Low"
'First is on change to second
OnRg2.Text = "Low to Moderate"
Case "Low to Moderate"
'Second so third
OnRg2.Text = "Moderate"
Case "Moderate"
'Third is on change to third
OnRg2.Text = "Moderate to High"
Case "Moderate to High"
'Third is on change to third
OnRg2.Text = "High"
Case "High"
'Third is on change to third
OnRg2.Text = "Low"
End Select
[/VBA]

[Dave]

Trial chr13 it's a square thingee the name of which eludes me right now. I think it represents the table cell end but is included in the range of text (on the end). Check fot chr13 or just shorten the string text of the range by 1 character ie Left(Text,Len(text)-1) HTH. Dave

[Donquick]

Thanks Dave, I tried the CHR(13), but that is not it - this is just represented by a spece within a string - CR perhaps. Nonetheless, the second soultion with the left substring has done the trick.

For some reason my case statement is not matching on the string even tho it looks identical once the dot has been amputated, but i guess i just need to track this down and i will be good to go.

Thanks for your help - I will use the left function again now i know about it.

Best regards,

Don

[Paul_Hossler]

The 13/7 pair is used to mark end of cells in Word

[VBA]
Option Explicit
Sub drv()
Dim sLast As String, sNexttoLast As String

Dim OnRg2 As Range

With ActiveDocument
Set OnRg2 = .Tables(1).Rows(1).Cells(2).Range
End With

sLast = Right(OnRg2.Text, 1)
sNexttoLast = Mid(OnRg2.Text, Len(OnRg2.Text) - 1, 1)

'13 is ASCII Carriage Return and 7 is ASCII Bell
'the 13/7 pair is used to mark end of cell in Word
MsgBox Asc(sNexttoLast)
MsgBox Asc(sLast)


Select Case Left(OnRg2.Text, Len(OnRg2.Text) - 2)
Case "Low"
OnRg2.Text = "Low to Moderate"
Case "Low to Moderate "
OnRg2.Text = "Moderate"
Case "Moderate "
OnRg2.Text = "Moderate to High"
Case "Moderate to High "
OnRg2.Text = "High"
Case "High "
OnRg2.Text = "Low"
End Select

End Sub
[/VBA]

Paul

[Donquick]

Ah ha - Thanks Paul - So i need to get rid of the end two characters to get a match on my text string.

As they say 'every day is a school day'!

Thanks

Don

[gmaxey]

Donquick,

There are several ways to skin the cat. Using a separate function to return cell text is one:

[VBA]Sub Test()
Dim OnRg2 As Range
With ActiveDocument
Set OnRg2 = .Tables(1).Rows(1).Cells(1).Range
End With
Select Case CellText(OnRg2)
Case "Low"
OnRg2.Text = "Low to Moderate"
Case "Low to Moderate "
OnRg2.Text = "Moderate"
Case "Moderate "
OnRg2.Text = "Moderate to High"
Case "Moderate to High "
OnRg2.Text = "High"
Case "High "
OnRg2.Text = "Low"
End Select
End Sub

Function CellText(ByRef oRng As Word.Range) As String
CellText = Left(oRng, Len(oRng) - 2)
End Function
[/VBA]

[macropod]

Personally, I'd keep it simple:
[vba]Sub Test()
Dim OnRg2 As Range
Set OnRg2 = ActiveDocument.Tables(1).Rows(1).Cells(1).Range
With OnRg2
.End = .End - 1
Select Case Trim(.Text)
Case "Low"
.Text = "Low to Moderate"
Case "Low to Moderate"
.Text = "Moderate"
Case "Moderate"
.Text = "Moderate to High"
Case "Moderate to High"
.Text = "High"
Case "High"
.Text = "Low"
End Select
End With
End Sub[/vba]
Don: Note the alternate way of getting the cell's contents without the end-of-cell marker (.End = .End - 1). Also, the Trim function's there 'cause I can't see why you'd be concerned with whether a space before/after the test strings. Maybe there is a good reason - but it's not apparent.

[Paul_Hossler]

Elegant

[VBA]
End = .End - 1
[/VBA]

Paul

[gmaxey]

Paul/Paul,

Yep elegant and probably the simpliest in the list. I dug up my old ref file and thought I would post all five of the skinned cats that I am aware of:

[VBA]Sub ScratchMacro()
Dim oCell As Cell
For Each oCell In Selection.Tables(1).Range.Cells
MsgBox fcnCellText1(oCell.Range.Text)
MsgBox fcnCellText2(oCell.Range)
MsgBox fcnCellText3(oCell.Range)
MsgBox fcnCellText4(oCell.Range.Text)
MsgBox fcnCellText5(oCell.Range)
Next
End Sub
Function fcnCellText1(ByRef pStr As String)
fcnCellText1 = Left(pStr, Len(pStr) - 2)
End Function
Function fcnCellText2(ByRef oRng As Range)
oRng.MoveEnd wdCharacter, -1
fcnCellText2 = oRng.Text
End Function
Function fcnCellText3(ByRef oRng As Range)
oRng.End = oRng.End - 1
fcnCellText3 = oRng.Text
End Function
Function fcnCellText4(ByRef pStr As String)
fcnCellText4 = Replace(pStr, ChrW(13) & ChrW(7), "")
End Function
Function fcnCellText5(ByRef oRng As Range)
'Slightly different as this also strips trailing empty paragraphs.
oRng.MoveEndWhile Cset:=Chr(13) & Chr(7), Count:=wdBackward
fcnCellText5 = oRng.Text
End Function[/VBA]