Select Case won't read variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

muyBN - 03 Jun 2007 05:16 GMT

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)

Signature

Bryan

Reply to this Message

Helmut Weber - 03 Jun 2007 10:23 GMT

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) !

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

muyBN - 04 Jun 2007 02:53 GMT

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?

Signature

Bryan

> Hi,
>
[quoted text clipped - 4 lines]
> >Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 1)
> >Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 2) !

Reply to this Message

Jay Freedman - 04 Jun 2007 03:24 GMT

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 FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

>Zowie, you be the man, Helmut! Your suggestion for "-2" worked. I bet you
>have a super-speedy Bavarian Motor Works vehicle.
[quoted text clipped - 16 lines]
>> >Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 1)
>> >Len(.Tables(1).Rows(1).Cells(intCnt).Range) - 2) !

Reply to this Message

Helmut Weber - 04 Jun 2007 14:51 GMT

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.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message