Halt Macro Execution

[Guest]

I need to halt a macro ( a series of sub routines) to post instructions and
allow user input. I have been trying a User Form to display the instructions
so that closing the form would continue the macro. The problem is that once
the form is shown it does not allow editing of the worksheet.
Any help would be appreciated.
Thanks

 

[Guest]

To allow physical/manual direct editing of the worksheet, you pretty much
need the macro to end. Obviously the workaround it to build some type of
interface where the user provides input to the interface and your code
changes the sheet.

 

[Don Guillett]

look in the vba help index for
messagebox
or
inputbox

 

[Guest]

Tom,
Halting the macro was a 'get out' because I could not come up with a
solution that did not require user intervention.
I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below
PartNumber Qty
23456-444 1
24477-079-02 4
24477-079-03 2
24536-900 1
26655-234-05 3
26655-234-07 1
26756-654 2
etc

What I need to do is where the PartNumber has a suffix (02, 03 etc) I need
to increase the Qty by multiplying it by the suffix (4 x 2=8) and then
removing the suffix (-02). I then need to total all like part numbers with
the total qty being in the first occurance of that number. Then delete all
other rows of that number.
The sheet would then look like this:
PartNumber Qty
23456-444 1
24477-079-02 14
24536-900 1
26655-234-05 22
26756-654 2

You can see why I went for the 'easier' solution!

 

[Guest]

Tom,
Sorry an error, the 'after' should look like this;
PartNumber Qty
23456-444 1
24477-079 14
24536-900 1
26655-234 22
26756-654 2

 

[Don Guillett]

Try this for the data presented where part number in column B. Assumes
sorted and all the same length.

Sub FixPartNums()
For i = 8 To 2 Step -1
If InStr(7, Cells(i, "B"), "-") > 1 Then
Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3)
Cells(i, 2) = Left(Cells(i, 2), 9)
If Cells(i, 2) = Cells(i + 1, 2) Then
Cells(i, 3) = Cells(i + 1, 3) + Cells(i, 3)
Rows(i + 1).Delete
End If
End If
Next i
End Sub

 

[Guest]

Don,
I got this to run by changing "B" to "A" (part numbers are in column A). It
works from row 8 upwards, this is a problem as there could be 100+ rows. It
stopped with a 'type mismatch' on the line "Cells(i, 3) = Mid(Cells(i, 2),
11, 2) * Cells(i, 3)".

 

[Don Guillett]

You will need to change all references to use for col A. ie:
cells(i,2) to cells(i,1) etc
and change the for 8 to 2 step -1 to 100 to 2 step -1

 

[Guest]

Thanks Don, it works well.

You will need to change all references to use for col A. ie:
cells(i,2) to cells(i,1) etc
and change the for 8 to 2 step -1 to 100 to 2 step -1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Easier than doing each by hand. Glad to help.