DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: help regarding excel macro!!

  1. #1
    Join Date
    Apr 2007

    help regarding excel macro!!

    I have made a simple SPC program for the company I work. I am not a computer expert but I manage to make a statistical process control charts. All it does that you have 26 columns where you have to put 5 values in each column. Then it calculates the mean, range and standard deviation. There are many different sheets in a workbook basically one for each different manufacturing process. Each sheet has different control limits. I have used conditional formatting so that when ever the value is outside the control limit it turns red. And if the average of 5 values is outside the limit operator has to mention the error code which are from 1 to 9 (which they are not doing it). I want to develop something like that when ever the average of the five values is outside the control limit the operator cannot enter further data unless they put an action code first. I have two ideas how to do it but canít make a macro as I am not very good in programming.

    i) First of all I thought that when ever the average value turns red (out of control) this triggers the macro and they canít do anything except for entering the fault code first. And once they have entered the fault code value they can carry on as normal. There is one problem with this that the average is going to turn red suppose the first value is out of limit. But I really want to trigger the macro once we have put all the 5 values in.

    ii) Secondly, I was thinking to put the control limits for each chart on specific sheet and the macro takes the values from those cells and assign a variable say x and y to them. And so we can use if then else loop and say that if the values are within these limits everything is fine and if the values are outside x and y, the user cannot enter any data unless he put an action code first.

    I hope I have explained my self as clearly as possible. I am really stuck and its very urgent for me. Hope to hear from some one really soon.

  2. #2
    Join Date
    Dec 2003
    San Diego
    VBA can be a little tedious, but with as much as you've done thus far, I'm sure you can pick up the basics rather quickly. This site I found through Google, the author has compiled myriad functions, macros and formulas in Excel, as well as how to use the Macro recorder and the VB editor in Excel.

    The rare times I have to write some VBA, I typically use the Macro recorder to record the basics of what I need done, then I open that macro in the VB editor and code the rest of my custom logic. This way, you will see the core of your VBA function written. The editor has Intellisense for functions and properties available to you for a given object.

    Good luck,
    Michael Sanchez
    Managing Technical Editor
    Forum Moderator

Similar Threads

  1. Replies: 1
    Last Post: 01-02-2007, 09:58 AM
  2. Excel Macro Help Request!!!
    By TatesMommy in forum VB Classic
    Replies: 1
    Last Post: 10-16-2006, 10:31 AM
  3. Replies: 1
    Last Post: 05-15-2006, 09:23 AM
  4. Using Word commands through an Excel macro
    By Pass0 in forum VB Classic
    Replies: 1
    Last Post: 10-24-2005, 01:26 PM
  5. Matching data macro in VBA Excel
    By Werner in forum VB Classic
    Replies: 2
    Last Post: 06-15-2005, 02:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center