Converting Word Macro to be used in Excel...

[Greg]

Hi,

Below please find a "Cut Stack Order" subroutine I wrote for Microsoft
Word. This script generates numbers in Cut Stack Order sequence and
saves the numbers into a TXT file. I import this TXT File into an
Excel row next to my data and use that as my Sort field:

Sub CutStackOrder()
Dim fn$, Start, Finish, range, k, j, l$
fn$ = "Numbers.csv"
Open fn$ For Output As 1
Print #1, "num1"
Start = 0
Finish = 30
range = 9
For k = 1 To range
For j = (Start + k) To Finish Step range
l = Format(j, "0000")
Print #1, l
Next j
Next k
Close 1
WordBasic.MsgBox "File: " + fn$ + " has been created!"
End Sub

I wish to use this code in Excel, placing the numbers directly into
the Excel spreadsheet instead of importing them from the TXT file. Can
this be accomplished? Thanks in advance for your assistance.

Regards,
Greg

 

[Dave Peterson]

Option Explicit
Sub CutStackOrder2()
Dim myCell As range
Dim StartNumber As Long
Dim FinishNumber As Long
Dim myStep As Long
Dim k As Long
Dim j As Long

StartNumber = 0
FinishNumber = 30
myStep = 9

Set myCell = ActiveSheet.range("a1")

myCell.Value = "num1" 'do you still want this?

For k = 1 To myStep
For j = (StartNumber + k) To FinishNumber Step myStep
Set myCell = myCell.Offset(1, 0)
With myCell
.NumberFormat = "0000"
.Value = j
End With
Next j
Next k
End Sub

 

[Greg]

Thank you Dave, this code works great!

One more question, can you tell me how to make this macro available
whenever I open an Excel spreadsheet?

-Greg

 

[Barb Reinhardt]

If you put the code in your Personal.xls file in the XLSTART folder, you'll
have access to it each time you open Excel.

 

[Dave Peterson]

Another option is to just keep the code in a different workbook. And open that
workbook when you need to use the code.

This kind of thing doesn't look really universal (to me!). I'd just open the
workbook when I needed it.

 

[Greg]

Thanks Barb and Dave for your prompt replies. I appreciate knowing
where I can store macros in the future that will load each time I
start up Excel.

In an effort to automate this macro, I could use your help once again.
Can you tell me how to calculate the last record in an Excel Worksheet
containing data? I want to define this value as my FinishNumber.

Additionally, I would like to add a dialog box which calls this
CutStackOrder2 routine. I have created a very basic userform that
contains the StartNumber, FinishNumber and Mystep labels. However, I
haven't had much success with passing this information along to the
variables defined in the CutStackOrder2 subroutine. Can you help
explain how this can be accomplished?

Many thanks in advance.

-Greg

 

[Dave Peterson]

You can use something like this to find the last used cell in any column.

Dim wks as worksheet
Dim LastRow as long
set wks = worksheets("somename") 'or ActiveSheet
with wks
lastrow = .cells(.rows.count,"X").end(xlup).row
end with

I used column X, but you'd use the column that always contains data if the row
is used.

I'd start by reading these:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp


=========
But you could embed the code into the Ok button's click event:

I'm betting you used textboxes (not labels) in your userform (and a userform,
not a dialog)???

Option explict
Sub CommandButton1_Click()

Dim myCell As range
Dim StartNumber As Long
Dim FinishNumber As Long
Dim myStep As Long
Dim k As Long
Dim j As Long

if isnumeric(me.textbox1.value) = false _
or isnumeric(me.textbox2.value) = false _
or isnumeric(Me.textbox3.value) = false then
msgbox "Please fix the input!
exit sub
end if

StartNumber = clng(me.textbox1.value)
FinishNumber = clng(me.textbox2.value)
myStep = clng(me.textbox3.value)

....

End Sub

(Untested, uncompiled. Watch for typos.)

And you'd probably want to add some other basic checks to limit those values
(not too big, not too small kind of stuff).

 

[Greg]

Thanks Dave, I'll give this a try and read through the additional
resources you suggested.

-Greg

 

[Greg]

Hi Dave,

I'm having some success with the code you supplied. Based on your
example, I have created two check boxes on my userform and would like
to create a IF condition to confirm which check box is select. If both
checkboxes are select I wish to stop the routine. Below, I have
extracted the If/Else condition used:

If IsNumeric(Me.SortOrder.Value) = True _
& IsNumeric(Me.Tickets.Value) = True Then
MsgBox "You must select SortOrder or Ticket!!!"
Exit Sub
ElseIf Me.SortOrder.Value = True Then
myStep = CLng(Me.TextBox3.Value)
ElseIf Me.Tickets.Value = True Then
myStep = FinishNumber / CLng(Me.TextBox3.Value)
End If

It appears when this part of the code is executed, the Msgbox If
Condition is ignored. Can you tell me what I am doing incorrectly or
whether there is an easier way to accomplish this task?

Thanks,
Greg

 

[Dave Peterson]

You can check the value of a checkbox:

if me.sortorder.value = true then
'it's checked
else
'it's not checked
end if

If you want to check a couple of checkboxes:

if me.sortorder.value = true _
and me.tickets.value = true then
'both checked
else
'one or 0 checked
end if

You use "And", not "&" for and.

==========
Just curious: Are these checkboxes mutually exclusive?

Maybe you only need one and then just check to see if it's checked or not????

 

[Greg]

Thanks. Yes, they are mutually exclusive. How would I only check for
one?

-Greg

 

[Dave Peterson]

If you have to choose one or the other, you could just "ask" the question
once--if the checkbox is checked, then option A is chosen. If that checkbox is
not checked, then Option B is checked.

Kind of like the Tools|Options|View tab. Either an option is checked or it's
not.

if me.checkbox1.value = true then
'do the first way
else
'do the second way
end if

 

[Greg]

Ok, but how does this condition prevent from choosing both boxes or no
boxes?

-Greg

 

[Dave Peterson]

It doesn't.

If you have to choose exactly one of the checkboxes, it's the same as a yes/no
checkbox.

If you can choose either or neither, but not both, then this wouldn't apply.

 

[Greg]

Hi,

I would like to use the Ceiling function to create a new variable
called Total_Record_Count. Below is the code I use:

Total_Record_Count = Ceiling(FinishNumber / myStep, 1) * myStep

When it is executed, I receive the following error message:

Compile error sub or function not defined.

Can you tell me what is the problem?

Thanks,
Greg

 

[JE McGimpsey]

Ceiling is not a VBA function. Try

Total_Record_Count = Application.WorksheetFunction.Ceiling( _
FinishNumber / myStep, 1) * myStep

 

[Dave Peterson]

I sometimes just add (mystep-1) to the starting number, so that dividing by
mystep and taking the integer portion gives me what I want:

Total_Record_Count = Int((FinishNumber + (myStep - 1)) / myStep) * myStep

 

[Greg]

Hi JE McGimpsey,

Thanks, the Application.WorksheetFunction works like a gem!

-Greg

 

[Greg]

Thanks Dave, this way works great too!

-Greg

I sometimes just add (mystep-1) to the starting number, so that dividing by
mystep and taking the integer portion gives me what I want:

Total_Record_Count = Int((FinishNumber + (myStep - 1)) / myStep) * myStep