Select Case won't read variable

[muyBN]

The following strScrap variable shows what I want in Intellisense (picks up
the value from a table cell, each column being incremented on each pass of a
while statement) but the Select Case statement doesn't work with the
variable. Anyone see what I'm doing wrong?

strScrap = Left(ActiveDocument.Tables(1).Rows(1).Cells(intCnt).Range,
Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 1)
Select Case strScrap
Case "Lead_ID"
strLead_ID = mbGetCellText(strLead_ID)

 

[Helmut Weber]

Hi,

the end-of-cell mark has a length of 2,
at least when working with the cell's range.

....

Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 1)
Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 2) !

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[muyBN]

Zowie, you be the man, Helmut! Your suggestion for "-2" worked. I bet you
have a super-speedy Bavarian Motor Works vehicle.

This is interesting because with the "-1" setting Intellisense showed that
the expression was equal to what I wanted, and many posts in this forum on
this subject said I needed to back off only one. On the other hand, when I
have tested for the length of selection for the cell marker, I've noticed
that it is 2.

Is there a special character that represents this end-of-cell marker, in the
same way that "^t" or chr(9) represents the tab character?

 

[Jay Freedman]

VBA itself seems to be confused about the nature of the cell marker.
In some places it treats it as one character and in other places as
two. Try running this little macro on a document that contains a
table:

Sub demo()
Dim oRg As Range
Dim msg As String

Set oRg = ActiveDocument.Tables(1).Cell(1, 1).Range

msg = "Cell including marker" & vbCr & _
"length = " & Len(oRg.Text) & vbCr & _
"Chr(" & Asc(Mid(oRg.Text, Len(oRg.Text) - 1, 1)) & _
"), Chr(" & Asc(Right(oRg.Text, 1)) & ")"
MsgBox msg

oRg.MoveEnd Unit:=wdCharacter, Count:=-1
msg = "Cell excluding marker" & vbCr & _
"length = " & Len(oRg.Text)
MsgBox msg
End Sub

You can see in the first messagebox that the cell marker is two
characters, Chr(13) and Chr(7) in that order. Then the MoveEnd method
moves the range's end *one* character to the left, but the range is
now *two* characters shorter than before.

When you pull the contents of a cell's range into a VBA string
variable, you'll always see the two separate characters at the end.
When you deal with the range within the document itself, though, the
marker behaves as one character.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Helmut Weber]

Was my pleasure to help.
Thanks to Jay you know the whole truth now.

With 6000 or so tipps on VBA within four years I've made 50 Dollars.
How much would that be in a hundred years?

Still not enough for a BMW.

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"