SPC Control Charts & Process Capability

Oct 3, 2006

I am trying to use excel to generate spc data i.e. control charts and process capability calculations. How can this be done within excel?

View 5 Replies


Quality Control Charts

Jun 13, 2014

I have a daily control material analyzed for certain chemical tests and the results values are being plotted as
"points" to show the performance of the machine and reagents.

I want these values to be represented as dots on the already prepared control chart as soon as I fill a daily table of the control values.

Attached file explains the problem : Internal Control.xls‎

View 14 Replies View Related

Nested Do Loops With Find Next Capability

Jun 20, 2007

how to nest do loops with find next capability. I have written code (that does not work, of course) that I hope will explain what I am trying to do. The code error is something like 'Object needed'.


Selection.Find( _
What:="2", _

n5 = ActiveCell.Address

View 9 Replies View Related

Converting Static Charts To Dynamic Charts

Jun 27, 2013

My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers

Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average

When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.

View 2 Replies View Related

Pivot Charts Versus Charts

Sep 5, 2012

I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.

Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1


View 2 Replies View Related

Avoid Hard Coding Control Name Inside Control Event Procedure?

Mar 4, 2014

Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?

It might be clearer to explain by a dummy code example:

[Code] ......

I'm seeking what I would need to replace Line1 with.

View 11 Replies View Related

Select Next Control / Cell On Worksheet After Enter In Control

Jan 9, 2008

Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?

View 9 Replies View Related

Drag From Treeview Control To Spreadsheet Control

Jan 12, 2007

I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.

I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.

I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).

View 4 Replies View Related

Determine Active Control On Multipage Control

Oct 4, 2007

How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])

View 3 Replies View Related

How To Process Checkboxes In VBA

Feb 11, 2014

how to handle checkboxes in VBA

I have a worksheet ("Sheet1") with an activex checkbox (CheckBox1) in cell B1

what code do I need to tell if the checkbox is ticked or not. (it probably should be some true / false type test but I keep not getting the syntax of the code correct.

what do I need to declare or set or ? to access the state of the checkbox

View 6 Replies View Related

Process Before Printing

Feb 2, 2010

I need to print a worksheet. The sheet has rows of a specific height (45) and data in one cell grows - adding comments/notes with date attached so that last one entered is displayed, the rest get shoved down.

I need to sometimes print out the sheet. However, I want to have all the notes shown so would like to (1) autofit all rows, (2) print the sheet and then (3) put all rows back to row height of 45. All this I would LIKE to have done by "trapping" the print button on the normal Print area

I have attached some code, but the sheet (1) does not print at all, (2) the code is sometimes executed twice (in debug mode followed it) and (3) sometimes does not set the row height properly either at autofit or static height.

View 2 Replies View Related

Automate Process

Feb 22, 2007

Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.

A1 @ 31/01/07 = 50 therefore cell C1 = 50
A1 @ 28/02/07 = 45 therefore cell C2 = 45
and so on for 12 months

what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.

Could this be done by date formula?

View 9 Replies View Related

Log Macro Process

Jun 30, 2006

I need to know if it's possible to log the process of a series of macros
either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)

For example:

Sub MyMacro1 ()
End Sub

Sub MyMacro2 ()
End Sub

If this process was logged, then the log file would look something like:

Date Time : Sub MyMacro1
Date Time : End Sub
Date Time : Sub MyMacro2
Date Time : End Sub

But it would be a in text just like a log file.
Is this possible with Excel ?

The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.

View 4 Replies View Related

VBA Process For Multiple Actions

Jul 17, 2009

I have a list of petroleum accumulations with three types of data: Reservoir depth, Net Pay and Gas-oil ratio. for each accumulation, the number of reservoirs within each can vary. So I may have a accumulation with only one set of those stats, or an accumulation with 20.

I need the program to go down an alphabetized list (8000 entries long) and after every unique accumulation name average the values of the stats(which are in three separate columns) then put it in one row perhaps beginning in Column M or another sheet. So that I have a list of accumulations with the average of those stats, one entry per accumulation name.

View 5 Replies View Related

UserForm Won't Process Because VBA Is Too Long

May 9, 2014

I have a UserForm where the user can enter figures for 143 different items which they select via a combobox drop down.

When I tested it, I hit the submit button but it says the procedure is too large!

Here is the code which is repeated 143 times but with a different row number and range each time (in red):

[Code] .......

So the next bit of code would be exactly the same except the range would be C5 and the row would be 18.

The range is always going to increase nice and easily: C5 up to C146.

The row number is a bit hit and miss.

Is there a way of significantly decreasing the size of this or will I have the break the code down into different UserForms?

View 14 Replies View Related

Interrupting The Save Process

Oct 24, 2008

Is there a way I can interupt the save process depending upon certain conditions. For example if a particular cell contains a specific value and the user attepts to save the worksheet I would like to interupt the process asking "Do you really want to save this sheet now?" with the options to continue or abort. I think I can manage the message box or userform but don't know how to initiate it when save is selected

View 5 Replies View Related

Process Of Executing Macro

Aug 30, 2009

I wanted to know If there a way to hide the process of executing macrob (vba)?
I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.

View 5 Replies View Related

How To Repeat Process For Rows

Feb 18, 2013

I have a list of rows with Yes and No check boxes. Columns "T" and "U" contain the output of these checkboxes (i.e. "TRUE" or "FALSE"). At the end I have a button which I'd like to run this code. Essentially, I want to code to go through row by row and do the following:

1. Check to see if both boxes are blank or if both boxes are checked - and if so set the background color to yellow
2. Otherwise set the background color to clear

I have written the following code which accomplishes this task for Row 9. Is there an easy way to repeat this process for rows 10-15 without copying all the text and changing the row numbers?

Then, any way, say "If all the rows (i.e. 9-15) have clear background colors (which would mean that they all 'passed' the test of having exactly one and only one box checked in the row), then run another macro which I have written"?

Rem Check to see if neither box is checked or if both boxes are checked and sets background color to yellow, otherwise sets background color to clear.

If (Range("T9").Text = "") And (Range("U9").Text = "") Or (Range("T9").Text = "TRUE") And (Range("U9").Text = "TRUE") Or
(Range("T9").Text = "FALSE") And (Range("U9").Text = "FALSE") Then
Range("B9", ("G9")).Interior.ColorIndex = 6
Else: Range("B9:G9").Interior.ColorIndex = 0
End If

View 3 Replies View Related

Process Without Having The Program Crash

Feb 7, 2007

what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.

The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.

Here's the formula I am using.


View 9 Replies View Related

Phantom Process With Remote VB6 Dll

Jun 21, 2008

I remoted most of my number crunching stuff to a dll.

It work fine except for a few strange things that I observed like from time to time a strange message "Error in loading DLL" even though everything works fine. This message appears only when entering the main formula in the formula, and is no problem at all in usual work.

When I investigated, I noticed in the "windows task manager" that one instance of excel remains open even after I have closed excel. This phantom excel process is maybe related to the other problem I explained above.

Instead of calling a VBA function, it creates a VB6 object from this dll and uses the methods of this object. In this way I have remoted the number crunching functionality.

The main parameter passe in this process is a reference to the original workbook. I need it because the number crunching functions need to read data from the workbook.

I made it sure to terminate the life of the object properly on both sides by ad hoc obj=Nothing statements.

Still I think that the "garbage collection" or the "terminating" is the cause of the problem.

View 9 Replies View Related

Process Append Query Through VBA

Dec 1, 2009

I currently have a great VBA option to append records to the appropriate Access table. One problem I'm running into though is getting by the unique record number. Currently a user submits a record to a DataStorage tab which is then appended to the database. However, we would like to keep some of the old records (maybe a weeks worth: approx 100 records) on that tab in case any problems arise in the append transition. But we don't want duplicate records in the table.

Is there a way to modify the below code to kick out the duplicates and only bring in new records to the table? I will assign the table the requirement to only bring in unique new records but the code errors in Excel.

Sub RunAccessQueries_ADO()
Dim cn As ADODB.Connection
Dim cm As ADODB.Command

dbPath = "C:Documents and SettingsUSERDesktop"
dbName = "MyAppendTest.mdb"

Set cn = New ADODB.Connection
Set cm = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
End With

With cm
.CommandText = "MyApp"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
End With
MsgBox ("Append Update is Complete")
End Sub

View 9 Replies View Related

Process Every Nth Row In Macro Loop

Jun 19, 2009

I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?

ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.LargeScroll ToRight:=-1
Selection.Delete Shift:=xlUp

View 4 Replies View Related

Create And Process Dynamic Form?

Feb 9, 2014

I want the user to type data via a form, but the number of the records to be added may vary.For this purpose I want to create a form, which has a "new record" button, which adds a new textbox, checkbox and radio button to the form. I do not really know, how to add these controls on such a way to the form, that the new elements will be well aligned below to the existing ones and the size of the form will also be adapted if it is necessary.

View 4 Replies View Related

Data Validation - Automating Process

Feb 12, 2014

I'm currently trying to automate the process of creating Data Validation.


I have Grades and Names List.

" GradesList " Range has values "One, Two, Three, Four "

I'm trying to map Names List to GradesList using INDIRECT().

Debug the function DataValidation() to understand the issue better.

View 7 Replies View Related

Macro Progress Bar To Show % Process Done

Oct 25, 2008

As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....

I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?

Or even tell me which sub routine it is up to? (but % would probably be better)

And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...

View 14 Replies View Related

Selection Process Finding X From Within 7 Columns

Sep 10, 2009

Hi I have a process in which I have to create a selection without bias. There are 9 categories in this selection process. The 1st and 2nd category already have code and they work differently from the rest. For the 3rd through the 8th categories I have to choose one from the 3rd category, then one from the 4th, etc. After the 8th category I start back at the 3rd and go to the 4th, ect. It ends when cell A1 reaches 30%.

Its a little difficult to explain but the process is simple enough to understand once you see it. I have attached an example of the process. I have also started the code and left explanations within the code.

View 14 Replies View Related

InputBox And Cancelling For Stop The Process

Sep 29, 2009

I have the following Input Boxes. If cancel is pressed on any box then I want the process stopped. eg if I enter an answer on sya questions one or two then click Cancel on question 3 I want the process stopped.

View 2 Replies View Related

Process To Combine Set Of Numbers That Have Range Of 1 To 48

Jul 20, 2012

I need a process to combine a set of numbers that have a range of 1 to 48. The set can vary. I need all possible combinations. Example:

out of the range 1 to 48, the set of numbers are (6,11,15,21,22,27,33,34,47). The numbers need to be combined into all possible combinations of subsets of 6 numbers. The criteria for combination is does not include sequencing. In other words the numbers only need to combined into 6 number sets that are in any sequence.

View 3 Replies View Related

Automating Copy And Pasting Process?

Oct 24, 2012

I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next.

View 9 Replies View Related

How To Process Non Numerical Data In Excel

May 18, 2014

how to process this non-numerical. I tried to use formulas but it wasn't fruitful. refer the table and.


[Code] ......

****The aggregate derived from the grade of the subjects , where each grade will be given a pointer and the average pointer for all four subjects will be considered as aggregate :

Pointers for grades
A =1

This is how the aggregate for Alex should be counted:
English A = 1
Maths C = 3
Science B = 2
History A = 1
*****aggregate = (1+3+2+1)/4 =1.75

The result column be showing Pass/Fail. If the student fail one of the four subjects he is considered as Fail.
**Grade D and E =>Fail

The Achievement Column should be indicating the numbers of As, Bs and Cs
for example: O6= A(2),B(1),C(1)

View 7 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved