Excel VBA Question 1

[Nullsweat]

Hola

I have production data that is manually entered, then converted via formulas to figure out Throughput by Process Path. However not everyone does the same job every day. So the Week Ending figures have several zeroes in them. I am trying to create control charts based upon that data. I have the control charts made, but need to make a macro that pulls the Week Ending figures minus the zeroes. Does anyone know an easy way to do that? Perhaps a For While Loop? Or perhaps a IF THEN Loop? Not sure...
Thanks in advance
Sean

 

[SBendBuckeye]

Not sure what you mean. Do you mean that you only want to process rows 1, 2, 4, 5 and 7 out of the following example because rows 3 and 6 are zero? Or do you mean something else?

Row 1 10
Row 2 20
Row 3 0
Row 4 15
Row 5 35
Row 6 0
Row 7 5

A little example of the data you are trying to work with is often helpful. Feel free to post again and we'll try to help.

Good Luck!

 

[dougcranston]

NullSweat,

Suggest two possible options.

Have you looked at using the FILTER option to include ONLY those > 0?

Another would be to use VBA to create a recordset from the data, and then create the Chart for you.

The later probably would be alot more work and maybe overkill.

I am in a Access mindset, and deal better with data from and ADO recordset than filtering.

For a simple tutorial on filtering look at:
How to Filter an Excel Database

http://www.docnmail.com/ExcelFilter/filter1.htm

Hope this helps.

DougCranston

 

[Nullsweat]

Sandy 11.5 2342.0 203.65
Saundra 0.0 0.0
Sharon 0.0 0.0
Sheila 5.5 1375.0 250.00
Toni 9.0 1897.0 210.78
Traci 8.2 1150.0 140.24
Tracy 9.0 3051.0 339.00
Valerie 10.0 1782.0 178.20


Here is some of the data, I am trying (as you surmised) to only list the names with Data for that week. I am trying to set control charts and histograms based on their weekly averages as a percentage of the Overall average. I used to use a program called SQC but it requires(as far as I have used it) a single number per associate. I am trying to duplicate this in Excel since most Managers do not have access nor the knowledge to run SQC. I have over 120 Associates that change by 1 or 2 per month.

 

[Nullsweat]

Sandy 11.5 2342.0 203.65
Saundra 0.0 0.0
Sharon 0.0 0.0
Sheila 5.5 1375.0 250.00
Toni 9.0 1897.0 210.78
Traci 8.2 1150.0 140.24
Tracy 9.0 3051.0 339.00
Valerie 10.0 1782.0 178.20


Here is some of the data, I am trying (as you surmised) to only list the names with Data for that week. I am trying to set control charts and histograms based on their weekly averages as a percentage of the Overall average. I used to use a program called SQC but it requires(as far as I have used it) a single number per associate. I am trying to duplicate this in Excel since most Managers do not have access nor the knowledge to run SQC. I have over 120 Associates that change by 1 or 2 per month.
Thanks
Sean

 

[dougcranston]

NullSweat,

Using your example one way would be to do the following:

Need column names for each column.

Example:
col1=name
col2=hours
col3=units
col4=rate

Next replicate the following formula in col4 under rate.
=ROUND(IF(($B9)=0,0,($C9/$B9)),2)

You can adjust this to your purposes but it ensures that you will not get a divide by 0, and rounds your output.

Next somewhere to the right of your data in a safe area, copy the "rate" header into a cell. In the cell right below it, type in >0 That becomes your filter criteria.

Finally, you have a choice to have the filter either create a separate copy of the filtered data for graphing or remain in place.

Click on DATA / FILTER / ADVANCED FILTER

In the LIST RANGE FIELD click on the icon, highlight the headers and your four columns of data, and then click on the popup icon that kind of looks like a guage.

Do the same for the CRITERIA RANGE, click on the icon, highlight the "rate" header and your formula to set the criteria, and then click on the popup icon that kind of looks like a guage.

Then click on OK.

Filters out the "0" values.

Not sure how the graphics will react to this. If it will only graph the visible records. If not, then you may have to use the COPY TO ANOTHER LOCATION.

Hope this helps.

DougCranston