macro2.bas

Introduction: 1. Macro1: AddFailureModeCol is used to the test report generated from GNPO Rpt Tools

Attribute VB_Name = "Macro2"
Sub DPHU_Match()


'*************************************************************************************************
'*|   DPHU_Match Macro
'*|   2Jan 2008 by th
'*|
'*|   this macro is Capable of 1000 rows only
'*|   to change the capability, simply replace 1000 with smaller or larger number whichever you want
'*|
'*|   approximate running time will be around 1 min
'*|
'**************************************************************************************************

        
            Dim i As Integer, n As Integer
            Dim j As Integer, k As Integer
            Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer, r5 As Integer
            Dim w As Integer, stn As Integer
            Dim MyPos
            Dim x(1999) As String
            Dim T As String
            
            i = 1
            r1 = 0
            r2 = 0
            r3 = 0
            r4 = 0
            r5 = 0
           
                        NewFN = Application.GetOpenFileName(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
                        If NewFN = False Then
                        MsgBox "Stopping because you did not select a file"
                        Exit Sub
                        Else
                        Workbooks.Open Filename:=NewFN
                        End If
                        
                        ActiveWorkbook.Sheets("Summary").Copy After:=Workbooks("DPHU_Format_26Dec.xls").Sheets(2)
                    
            'find out the starting point, which is the row right after "Test Code"
            Sheets("Summary").Select
            Range("A1").Select
            x(0) = ActiveCell.Value
            
            Do Until x(i) = "Test Code"
            ActiveCell.Offset(1, 0).Activate
            i = i + 1
            x(i) = ActiveCell.Value
            Loop
                  
                  
                  
                  
                  
  '*********************************************************************************
  '*                    check with the failure mode column
  '*                    copy selected data into related cell
  '*********************************************************************************
            Range("A1").Select
            ActiveCell.Offset(i + 1, 11).Activate
            
            For k = 1 To 1000
                    x(k) = ActiveCell.Value
                    w = i + k
   
   
   
   '*******************************************
   '*                     Test                *
   '*                                         *
   '*******************************************
                        If x(k) = "Test" Then
                                         
                            ''''''insert row to DPHU worksheet
                                    Sheets("DPHU").Select
                                        Range("C36").Select
                                        Application.CutCopyMode = False
                                        Selection.EntireRow.Insert
                                        
                                        
                                        
                                        
                            ''''''copy the data from summary to dphu sheet
                                    Sheets("Summary").Select
                                    Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    '********************
                                    Range("A" & ActiveCell.Row).Select
                                    Selection.Copy
                                    Sheets("DPHU").Select
                                    Range("C36").Select
                                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                                    
                                    
                                    Sheets("Summary").Select
                                       Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("B" & ActiveCell.Row).Select
                                    Selection.Copy
                                    Sheets("DPHU").Select
                                    Range("D36").Select
                                     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                                    
                                    
                                     Sheets("Summary").Select
                                        Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("C" & ActiveCell.Row).Select
                                    Selection.Copy
                                    
                                    Sheets("DPHU").Select
                                    Range("F36").Select
                                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Selection.NumberFormat = "0"
  
                                
                                     Sheets("Summary").Select
                                        Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("F" & ActiveCell.Row).Select
                                    Selection.Copy
                                    
                                    Sheets("DPHU").Select
                                    Range("G36").Select
                                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Selection.NumberFormat = "0"
                                   
                                    Sheets("Summary").Select
                                        Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("E" & ActiveCell.Row).Select
                                    Selection.Copy
                                    Sheets("DPHU").Select
                                    Range("H36").Select
                              Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                                   
                                   
                                 '***************
                                    Sheets("Summary").Select
                                       Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("H" & ActiveCell.Row).Select
                                    Selection.Copy
                                    Sheets("DPHU").Select
                                    Range("I36").Select
                                    'ActiveSheet.Paste
                                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
                                    Sheets("Summary").Select
                                       Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("J" & ActiveCell.Row).Select
                                    Selection.Copy
                                    Sheets("DPHU").Select
                                    Range("L36").Select
                                 '  ActiveSheet.Paste
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                                '***************
                                    
                                   
                                   Sheets("Summary").Select
                                       Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate
                                    Range("I" & ActiveCell.Row).Select
                                    Selection.Copy
                                    Sheets("DPHU").Select
                                    Range("K36").Select
                                  '  ActiveSheet.Paste
                                   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                                   
 '************************************************************
 '                  find out the test station
 '************************************************************
                        Sheets("Summary").Select
                        Range("A1").Select
                        ActiveCell.Offset(w, 0).Activate
                               Selection.End(xlUp).Select
                               T = ActiveCell.Value
                               Sheets("DPHU").Select
                                    Range("E36").Select
                                    ActiveCell.FormulaR1C1 = Get_Word(T, "First")

'***********************************************************
                        r1 = r1 + 1
                                    
                                    
                                    
                                    
                                                                  
   '*******************************************
   '*                     HW                  *
   '*                                         *
   '*******************************************
                                    
                                    
                                    
                        ElseIf x(k) = "Design - HW" Then
                                    Sheets("DPHU").Select
                                        Range("C39").Select
                                        ActiveCell.Offset(r1, 0).Select
                                        Application.CutCopyMode = False
                                        Selection.EntireRow.Insert
                                        
                                        
                                    
                                    '*********************************
                                     ''''''copy the data from summary to dphu sheet
                                    Sheets("Summary").Select
                                    Range("A1").Select
                                    ActiveCell.Offset(w, 11).Activate