Need assistance asap please-thanks.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

simplysewing@gmail.com - 31 Oct 2007 16:18 GMT

I have a schedule-type table in MS Word with 13 rows (12 months +
label row) , and 32 columns (31 days + label column). The cells for
months with <31 days are blacked out. I want to assign variables to
my starting cell, and ending cell, then count the number of cells that
are blacked out. My idea was assign a variable to the start of the
range (BegRng) and end of the range (EndRng) as the code was placing
text ("R") in cells, then count the black cells with a variable
(tmpCount) between these placement calls. Here's what I tried:

(Start Snip)
BegRng = ActiveDocument.Tables(1).Cell(IRow, ICol).Range
ActiveDocument.Tables(1).Cell(IRow, ICol).Range.Text = "R"
ActiveDocument.Tables(1).Cell(IRow,
ICol).Range.Cells.Shading.Texture = 200
ICol = ICol + 1
EndRng = ActiveDocument.Tables(1).Cell(IRow, ICol).Range
(End Snip)

Sub Count()
Dim tmpCount As Integer, SearchRng As Range
tmpCount = 0
Set SearchRng = ActiveDocument.Tables(1).Cell(BegRng, EndRng).Range
If SearchRng = "R" Then
tmpCount = tmpCount + 1
End If
End Sub

I get an "Type mismatch" error at the Set line. (I left BegRng and
EndRng variables as variant)
Any ideas? TIA for any assistance!

Reply to this Message

Helmut Weber - 31 Oct 2007 16:51 GMT

Hi,

compare these two lines

BegRng = ActiveDocument.Tables(1).Cell(IRow, ICol).Range
... = ActiveDocument.Tables(1).Cell(BegRng, EndRng).Range

BegRng and EndRng contain ranges
A range is an Object.
cell(x,y) expects data of type long.

It will work with variant instead of long,
as long as the variant can be interpreted as long.

You can't convert a range to long, IMHO.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

simplysewing@gmail.com - 02 Nov 2007 02:46 GMT

Thank you for your help! I've reworked the code (See below). I used
4 variables (RngRowX, RngColX)
which are picking up the correct cell values when Sub MyCount is
called. However, I' still getting this error:
Runtime error 451-Property let procedure not defined and property get
procedure did not return an object.
Is this due to how my variables are declared in Sub MyCount?

If IP = 1 Then
ActiveDocument.Tables(1).Cell(IRow, ICol).Range.Text = "R"
ActiveDocument.Tables(1).Cell(IRow,
ICol).Range.Cells.Shading.Texture = 200
RngRow1 = IRow
RngCol1 = ICol
ICol = ICol + 1
If ICol > 32 Then
IRow = IRow + 1
ICol = ICol - 31
End If
RngRow2 = IRow
RngCol2 = ICol
Call Count
ICol = ICol + tmpCount
If ICol > 32 Then
IRow = IRow + 1
ICol = ICol - 31
End If
tmpCount = 0
IP = IP + 1
If IRow > 13 Then
IP = 0
End If
ElseIf IP = 2 Then
(snip)

Sub MyCount()
Dim tmpCount As Integer, MyTable, MyRange As Range
If RngRow1 > 0 Then
tmpCount = 0
Set MyTable = ActiveDocument.Tables(1)
Set MyRange = ActiveDocument.Range(Start:=MyTable.Cell(RngRow1,
RngCol1) _
.Range.Start, End:=MyTable(1).Cell(RngRow2,
RngCol2).Range.End)
If MyTable.Cell(MyRange).Shading.Texture = 900 Then
tmpCount = tmpCount + 1
Else
Exit Sub
End If
End If

> Hi,
>
[quoted text clipped - 19 lines]
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Helmut Weber - 02 Nov 2007 13:05 GMT

Hi,

I don't know exactly what's going on
or waht you are doing,
but, in general, beware of ranges in tables,
at least over different rows.

First there is no column range at all.
Second, a range in a table extends linearly,
from left two right from start to end.

Example:
Create a 5-row 5-column table in an empty test-doc.
Try the following code:

Dim oTbl As Table
Dim r As Range
Set r = Selection.Range
Set oTbl = ActiveDocument.Tables(1)
With oTbl
r.start = .Cell(1, 1).Range.start
r.End = .Cell(2, 2).Range.End
End With
MsgBox r.Cells.Count

The result is 7, not 4 as might be expected.
5 cells in row(1), 2 cells in row(2).

And by the way, the range contains the end-of-row mark, too.

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Russ - 04 Nov 2007 06:43 GMT

Simplysewing,

Are you using Option Explicit?
http://www.cpearson.com/excel/DeclaringVariables.aspx
It will help capture some typos, etc.

See inline below.

> Thank you for your help! I've reworked the code (See below). I used
> 4 variables (RngRowX, RngColX)
[quoted text clipped - 18 lines]
> RngCol2 = ICol
> Call Count

Are you suppose to be calling MyCount here? Don't name a subroutine Count,
which is part of Word VBA syntax.

> ICol = ICol + tmpCount
> If ICol > 32 Then
[quoted text clipped - 11 lines]
> Sub MyCount()
> Dim tmpCount As Integer, MyTable, MyRange As Range

Dim MyTable as Word.Table 'is better

> If RngRow1 > 0 Then
> tmpCount = 0
> Set MyTable = ActiveDocument.Tables(1)
> Set MyRange = ActiveDocument.Range(Start:=MyTable.Cell(RngRow1,
> RngCol1) _
> .Range.Start, End:=MyTable(1).Cell(RngRow2,

What is MyTable(1)?

> RngCol2).Range.End)
> If MyTable.Cell(MyRange).Shading.Texture = 900 Then
[quoted text clipped - 27 lines]
>> Win XP, Office 2003
>> "red.sys" & Chr$(64) & "t-online.de"

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

Rate this thread: