Looking for a Better Way?

[Greg Maxey]

Today I wrote some code to sequential number some Avery 5267 labels. My
template table has 7 columns and 20 rows (the Avery 5267 layout) for 80
labels. I knew that if I need more than 80 labels in the sequence then I
would need additional tables. I had a real tough time figuring out how many
tables I would actually need.

E.g. 81 + labels needs two tables, 161+ needs 3, etc.

Taking the required label count and dividing by 80 would give the base
number and then I would need one more if there was a remainder. I know
about INT returning the integer portion of a calculation result. Is there
something that returns the fractional part? What I ended up doing was
subtracting the integer part from the double and if result was > 0 then I
added another table. Is there a better way?

Dim tableCount as Long
Dim x as Double
labelCount = (seqEnd - seqStart) + 1
If labelCount > 80 Then
tablesCount = Int(labelCount / 80)
x = labelCount / 80
If x - tablesCount > 0 Then tablesCount = tablesCount + 1
If tablesCount > 1 Then
For i = 2 To tablesCount
oTbl.Range.Copy
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak Type:=wdSectionBreakNextPage
ActiveDocument.Sections.Last.Range.Select
Selection.Paste
Next
End If

In a UserForm textbox I wanted to ensure that only numbers 0-9 could be
entered. I used:

Private Sub txtEndNum_Change()
Dim pStrTest
pStrTest = Right(Me.txtEndNum.Text, 1) 'the last key pressed entered.
If Len(Me.txtEndNum.Text) > 0 Then
If Not pStrTest Like "[0-9]" Then
Me.txtEndNum.Text = Left(Me.txtEndNum.Text, Len(Me.txtEndNum.Text) - 1)
End If
End If
End Sub

Is there a better way?

Thanks.

 

[Tony Jollans]

What you need is a Ceiling function, or a RoundUp function, but, AFAIK,
neither Word nor VBA has either. There's nothing wrong with what you have
done, and there are other ways to do it, but what I think you're looking for
is the Mod function, which will give the remainder of division:

79 Mod 80 = 79
80 Mod 80 = 0
81 Mod 80 = 1
82 Mod 80 = 2
etc.

So

Labels \ 80 + (Labels Mod 80 = 0) + 1

is about as brief as you can get.

 

[Greg Maxey]

Thanks Tony. I will just keep what I have.

What you need is a Ceiling function, or a RoundUp function, but,
AFAIK, neither Word nor VBA has either. There's nothing wrong with
what you have done, and there are other ways to do it, but what I
think you're looking for is the Mod function, which will give the
remainder of division:
79 Mod 80 = 79
80 Mod 80 = 0
81 Mod 80 = 1
82 Mod 80 = 2
etc.

So

Labels \ 80 + (Labels Mod 80 = 0) + 1

is about as brief as you can get.

Today I wrote some code to sequential number some Avery 5267 labels.
My template table has 7 columns and 20 rows (the Avery 5267 layout)
for 80 labels. I knew that if I need more than 80 labels in the
sequence then I would need additional tables. I had a real tough
time figuring out how many
tables I would actually need.

E.g. 81 + labels needs two tables, 161+ needs 3, etc.

Taking the required label count and dividing by 80 would give the
base number and then I would need one more if there was a remainder.
I know about INT returning the integer portion of a calculation
result. Is there something that returns the fractional part? What
I ended up doing was subtracting the integer part from the double
and if result was > 0 then I added another table. Is there a better
way? Dim tableCount as Long
Dim x as Double
labelCount = (seqEnd - seqStart) + 1
If labelCount > 80 Then
tablesCount = Int(labelCount / 80)
x = labelCount / 80
If x - tablesCount > 0 Then tablesCount = tablesCount + 1
If tablesCount > 1 Then
For i = 2 To tablesCount
oTbl.Range.Copy
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak Type:=wdSectionBreakNextPage
ActiveDocument.Sections.Last.Range.Select
Selection.Paste
Next
End If

In a UserForm textbox I wanted to ensure that only numbers 0-9 could
be entered. I used:

Private Sub txtEndNum_Change()
Dim pStrTest
pStrTest = Right(Me.txtEndNum.Text, 1) 'the last key pressed entered.
If Len(Me.txtEndNum.Text) > 0 Then
If Not pStrTest Like "[0-9]" Then
Me.txtEndNum.Text = Left(Me.txtEndNum.Text,
Len(Me.txtEndNum.Text) - 1) End If
End If
End Sub

Is there a better way?

Thanks.

 

[Doug Robbins - Word MVP]

How about

Dim i As Long, j As Long, NumLabels As Long
Dim arow As Row
NumLabels = InputBox("Enter the Number of Labels")
i = 1
With ActiveDocument.Tables(1)
While i < NumLabels + 1
If i Mod 4 = 1 Then
Set arow = .Rows.Add
Else
Set arow = .Rows(.Rows.Count)
End If
For j = 1 To 7 Step 2
arow.Cells(j).Range.Text = i
i = i + 1
Next j
Wend
.Rows(1).Delete
End With

Before running the macro, delete all but the first row of labels from the
page and set the bottom margin so that only 20 rows of lables will fit on
the page.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Greg Maxey]

Doug,

Thanks. The example I posted was just a short version of the actual code.
I don't need to add or delete rows. I'll send you the whole piece.

How about

Dim i As Long, j As Long, NumLabels As Long
Dim arow As Row
NumLabels = InputBox("Enter the Number of Labels")
i = 1
With ActiveDocument.Tables(1)
While i < NumLabels + 1
If i Mod 4 = 1 Then
Set arow = .Rows.Add
Else
Set arow = .Rows(.Rows.Count)
End If
For j = 1 To 7 Step 2
arow.Cells(j).Range.Text = i
i = i + 1
Next j
Wend
.Rows(1).Delete
End With

Before running the macro, delete all but the first row of labels from
the page and set the bottom margin so that only 20 rows of lables
will fit on the page.

Today I wrote some code to sequential number some Avery 5267 labels.
My template table has 7 columns and 20 rows (the Avery 5267 layout)
for 80 labels. I knew that if I need more than 80 labels in the
sequence then I would need additional tables. I had a real tough
time figuring out how many
tables I would actually need.

E.g. 81 + labels needs two tables, 161+ needs 3, etc.

Taking the required label count and dividing by 80 would give the
base number and then I would need one more if there was a remainder.
I know about INT returning the integer portion of a calculation
result. Is there something that returns the fractional part? What
I ended up doing was subtracting the integer part from the double
and if result was > 0 then I added another table. Is there a better
way? Dim tableCount as Long
Dim x as Double
labelCount = (seqEnd - seqStart) + 1
If labelCount > 80 Then
tablesCount = Int(labelCount / 80)
x = labelCount / 80
If x - tablesCount > 0 Then tablesCount = tablesCount + 1
If tablesCount > 1 Then
For i = 2 To tablesCount
oTbl.Range.Copy
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak Type:=wdSectionBreakNextPage
ActiveDocument.Sections.Last.Range.Select
Selection.Paste
Next
End If

In a UserForm textbox I wanted to ensure that only numbers 0-9 could
be entered. I used:

Private Sub txtEndNum_Change()
Dim pStrTest
pStrTest = Right(Me.txtEndNum.Text, 1) 'the last key pressed entered.
If Len(Me.txtEndNum.Text) > 0 Then
If Not pStrTest Like "[0-9]" Then
Me.txtEndNum.Text = Left(Me.txtEndNum.Text,
Len(Me.txtEndNum.Text) - 1) End If
End If
End Sub

Is there a better way?

Thanks.

 

[alborg]

Hi Greg:

Listen, I'll be answering your email that you sent me on 12/15/2008 tonight.
My laptop crashed soon after receiving it, then I went to a family vacation
to Panama (where I'm writing this now). Anyhow...

In the past I sent you a copy of my EMR which among other things, prints out
medications. Specifically, I can only put 4 per Word template. This seems to
be exactly the same problem you're having. The way I got around it a while
back is to use the VBA key word "TOP" in selecting the subrecord group from
your recordset. Here is a cut from my original code:

SQLStmt = "SELECT TOP 4 MEDICATIONSTEMP.* FROM MEDICATIONSTEMP WHERE
MEDICATIONSTEMP!X = -1 and MEDICATIONSTEMP!XX = -1;"
Set rs2 = db.OpenRecordset(SQLStmt, dbOpenDynaset)

So you see, I send out 4, then cut those out, then send out 4 more, etc.,
everytime selecting the top 4... in your case, you'd use TOP 80, although I
don't see how you can get 20 x 7 = (?) 80. New math? 8^)

Gotta go swim with the kids...

Cheers,
Al

Thanks Tony. I will just keep what I have.

What you need is a Ceiling function, or a RoundUp function, but,
AFAIK, neither Word nor VBA has either. There's nothing wrong with
what you have done, and there are other ways to do it, but what I
think you're looking for is the Mod function, which will give the
remainder of division:
79 Mod 80 = 79
80 Mod 80 = 0
81 Mod 80 = 1
82 Mod 80 = 2
etc.

So

Labels \ 80 + (Labels Mod 80 = 0) + 1

is about as brief as you can get.

Today I wrote some code to sequential number some Avery 5267 labels.
My template table has 7 columns and 20 rows (the Avery 5267 layout)
for 80 labels. I knew that if I need more than 80 labels in the
sequence then I would need additional tables. I had a real tough
time figuring out how many
tables I would actually need.

E.g. 81 + labels needs two tables, 161+ needs 3, etc.

Taking the required label count and dividing by 80 would give the
base number and then I would need one more if there was a remainder.
I know about INT returning the integer portion of a calculation
result. Is there something that returns the fractional part? What
I ended up doing was subtracting the integer part from the double
and if result was > 0 then I added another table. Is there a better
way? Dim tableCount as Long
Dim x as Double
labelCount = (seqEnd - seqStart) + 1
If labelCount > 80 Then
tablesCount = Int(labelCount / 80)
x = labelCount / 80
If x - tablesCount > 0 Then tablesCount = tablesCount + 1
If tablesCount > 1 Then
For i = 2 To tablesCount
oTbl.Range.Copy
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak Type:=wdSectionBreakNextPage
ActiveDocument.Sections.Last.Range.Select
Selection.Paste
Next
End If

In a UserForm textbox I wanted to ensure that only numbers 0-9 could
be entered. I used:

Private Sub txtEndNum_Change()
Dim pStrTest
pStrTest = Right(Me.txtEndNum.Text, 1) 'the last key pressed entered.
If Len(Me.txtEndNum.Text) > 0 Then
If Not pStrTest Like "[0-9]" Then
Me.txtEndNum.Text = Left(Me.txtEndNum.Text,
Len(Me.txtEndNum.Text) - 1) End If
End If
End Sub

Is there a better way?

Thanks.

 

[Greg Maxey]

Al.

3 of the 7 columns are spacer columns so only 4 x 20 = 80 labels ;-)

Hi Greg:

Listen, I'll be answering your email that you sent me on 12/15/2008
tonight. My laptop crashed soon after receiving it, then I went to a
family vacation to Panama (where I'm writing this now). Anyhow...

In the past I sent you a copy of my EMR which among other things,
prints out medications. Specifically, I can only put 4 per Word
template. This seems to be exactly the same problem you're having.
The way I got around it a while back is to use the VBA key word "TOP"
in selecting the subrecord group from your recordset. Here is a cut
from my original code:

SQLStmt = "SELECT TOP 4 MEDICATIONSTEMP.* FROM MEDICATIONSTEMP WHERE
MEDICATIONSTEMP!X = -1 and MEDICATIONSTEMP!XX = -1;"
Set rs2 = db.OpenRecordset(SQLStmt, dbOpenDynaset)

So you see, I send out 4, then cut those out, then send out 4 more,
etc., everytime selecting the top 4... in your case, you'd use TOP
80, although I don't see how you can get 20 x 7 = (?) 80. New math?
8^)

Gotta go swim with the kids...

Cheers,
Al

Thanks Tony. I will just keep what I have.

What you need is a Ceiling function, or a RoundUp function, but,
AFAIK, neither Word nor VBA has either. There's nothing wrong with
what you have done, and there are other ways to do it, but what I
think you're looking for is the Mod function, which will give the
remainder of division:
79 Mod 80 = 79
80 Mod 80 = 0
81 Mod 80 = 1
82 Mod 80 = 2
etc.

So

Labels \ 80 + (Labels Mod 80 = 0) + 1

is about as brief as you can get.


message Today I wrote some code to sequential number some Avery 5267
labels. My template table has 7 columns and 20 rows (the Avery
5267 layout) for 80 labels. I knew that if I need more than 80
labels in the sequence then I would need additional tables. I had
a real tough time figuring out how many
tables I would actually need.

E.g. 81 + labels needs two tables, 161+ needs 3, etc.

Taking the required label count and dividing by 80 would give the
base number and then I would need one more if there was a
remainder. I know about INT returning the integer portion of a
calculation result. Is there something that returns the
fractional part? What I ended up doing was subtracting the
integer part from the double and if result was > 0 then I added
another table. Is there a better way? Dim tableCount as Long
Dim x as Double
labelCount = (seqEnd - seqStart) + 1
If labelCount > 80 Then
tablesCount = Int(labelCount / 80)
x = labelCount / 80
If x - tablesCount > 0 Then tablesCount = tablesCount + 1
If tablesCount > 1 Then
For i = 2 To tablesCount
oTbl.Range.Copy
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak Type:=wdSectionBreakNextPage
ActiveDocument.Sections.Last.Range.Select
Selection.Paste
Next
End If

In a UserForm textbox I wanted to ensure that only numbers 0-9
could be entered. I used:

Private Sub txtEndNum_Change()
Dim pStrTest
pStrTest = Right(Me.txtEndNum.Text, 1) 'the last key pressed
entered. If Len(Me.txtEndNum.Text) > 0 Then
If Not pStrTest Like "[0-9]" Then
Me.txtEndNum.Text = Left(Me.txtEndNum.Text,
Len(Me.txtEndNum.Text) - 1) End If
End If
End Sub

Is there a better way?

Thanks.