- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-12-22 16:51 ½s¿è
¦^´_ 2# happycoccolin
¸Õ¸Õ¬Ý- Option Explicit
- Sub Ex()
- Dim AR, Sh As Worksheet
- Dim i As Long, Msg As Variant, W As Single, M As Single, s As String, filein As String, fileout As String
- filein = Application.GetOpenFilename(FileFilter:="Excel ¬¡¶Ã¯ (*.xlsx),*.xlsx", Title:="½Ð¿ï¾Ün¤ñ¹ïªºÀÉ®×")
- If Not TypeName(filein) = "String" Then Exit Sub '¨ú®ø«hµ²§ô
- With Workbooks.Open(filein)
- Set Sh = .Sheets(1)
- AR = Sh.UsedRange.Columns("S")
- End With
- AR(1, 1) = "PASS/FAIL"
- For i = 2 To UBound(AR)
- Msg = ""
- Select Case Cells(i, "O")
-
- Case ""
- '**********************************************************************
- '2.Y¬OOÄæ(Implementation)¬°ªÅ,½Ð¦bSÄæ¦ì(µ²ªGÄæ¦ì)Åã¥Ü"µL¤u§@¹qÀ£/¹q¬y"
- Msg = "µL¤u§@¹qÀ£/¹q¬y"
- Case "C"
- 'IF(O1="C",IF(¡yÂ^¨úM1Äæ"/"«á¦r¤¸¡z*0.6>Q1,PASS,FAIL))
- Msg = Val(Split(Cells(i, "M"), "/")(1)) * 0.6 > Cells(i, "Q")
- Case "R"
- Msg = Split(Cells(i, "P"), "_") 'Msg = PÄ椤¥Î "_" ¦r¦ê¤À³Î¶Ç¦^ªº°}¦C
- If UBound(Msg) = 0 Then '°}¦C¤¸¯À¥u¦³¤@Ó,PÄ椤¨S¦³"_"ªº¦r¤¸
- Msg = Split(Cells(i, "P"), "_")(0)
- ElseIf UBound(Msg) > 0 Then 'PÄ椤¦³"_"ªº¦r¤¸
- Msg = Split(Cells(i, "P"), "_")(1)
- If Mid(UCase(Msg), 1, 1) = "H" Then
- '"r0603_hxx"(xx¬OÅܼÆ) : r+«á¥|½X¦r¦ê_hxx
- Msg = Split(Cells(i, "P"), "_")(0)
- End If
- End If
- W = 0
- Select Case Right(Trim(Msg), 4) ' PÄæ«á4½X¦r¦ê
- Case "0402" '¹s¥ó¤j¤p
- W = 0.0625 '¥\²v(W)
- Case "0603"
- W = 0.1
- Case "0805"
- W = 0.125
- Case "1206"
- W = 0.25
- Case "1210"
- W = 0.3333
- Case "1812"
- W = 0.5
- Case "2010"
- W = 0.75
- Case "2512"
- W = 1
- End Select
- '**********************************************************************
- '4.¥H¤U³o¬q,Y¬OKohm & Mohm¤¤¶¡±a¤@ӪŮæ(K ohm & M ohm),¤@¼Ë¥i¥H§PÂ_¥X¨Ó¶Ü?
- '**************************************************************************
- Msg = UCase(Right(Trim(Cells(i, "M")), 5))
-
- If Msg = "K OHM" Or Msg = "M OHM" Then 'Ū¨ú5Ó¦r¤¸,¨ú±o¼Ú©i³æ¦ì
- 'M1»Ý§PÂ_È:¥H¼Ú©iÈpºâ.Y¬°Kohm¡G¨äÈ¡Ñ1000¡BY¬°Mohm¡G¨äÈ¡Ñ1000000)
- '¨Ò¦p:M1=2.64Kohm=2.64*1000=2640
- M = Val(Cells(i, "M")) * 10000 'Kohm
- If Msg = "M OHM" Then M = Val(Cells(i, "M")) * 1000000
- 'IF(O1="R",IF(OR((M1<>"0ohm"),(M1<>"0 ohm")),IF(Q1¥¤è/2640-2640*N1<0.0625*0.6,PASS,FAIL)))
- Msg = Cells(i, "Q") ^ 2 / M - M * Cells(i, "N") < W * 0.6
- End If
-
- If Not IsNumeric(Msg) Then '5Ó¦r¤¸,«D¼Ú©i³æ¦ì
- Msg = UCase(Right(Trim(Cells(i, "M")), 4)) 'Ū¨ú4Ó¦r¤¸,¨ú±o¼Ú©i³æ¦ì
- If Msg = "0OHM" Or Msg = " OHM" Then '¡y«D0 ohm¡z¡GohmÃþªº¦r¤¸«e±¥i¯à±aªÅ®æ,³¡¤À¥¼±aªÅ®æ
- 'IF(O1="R",IF(OR((M1="0ohm"),(M1="0 ohm")),IF(Q1¥¤è*N1< W°Ñ¾\¶µ¥Ø2*0.6,PASS,FAIL)))
- M = Val(Cells(i, "M"))
- Msg = Cells(i, "Q") ^ 2 * Cells(i, "N") < W * 0.6
- ElseIf Msg = "KOHM" Or Msg = "MOHM" Then
- 'M1»Ý§PÂ_È:¥H¼Ú©iÈpºâ.Y¬°Kohm¡G¨äÈ¡Ñ1000¡BY¬°Mohm¡G¨äÈ¡Ñ1000000)
- '¨Ò¦p:M1=2.64Kohm=2.64*1000=2640
- M = Val(Cells(i, "M")) * 10000 'Kohm
- If Msg = "MOHM" Then M = Val(Cells(i, "M")) * 1000000
- 'IF(O1="R",IF(OR((M1<>"0ohm"),(M1<>"0 ohm")),IF(Q1¥¤è/2640-2640*N1<0.0625*0.6,PASS,FAIL)))
- Msg = Cells(i, "Q") ^ 2 / M - M * Cells(i, "N") < W * 0.6
- End If
- End If
- If Not IsNumeric(Msg) Then '4Ó¦r¤¸,«D¼Ú©i³æ¦ì
- Msg = UCase(Right(Trim(Cells(i, "M")), 3)) '³Ñ¤U3Ó¦r¤¸,³Ì¤pªº¼Ú©i³æ¦ì
- M = Val(Cells(i, "M"))
- Msg = Cells(i, "Q") ^ 2 * Cells(i, "N") < W * 0.6
-
- End If
- Case "BEAD"
- 'IF(O1="Bead",IF(Q1¥¤è<¡y§PÂ_F1Ä桨/¡¨«á¦r¤¸,Y¥X²{mA¦r¤¸,¨äÈn¡Ò1000¤Ï¤§«h§_¡z¥¤è*0.6,PASS,FAIL)))
- '¨Ò¦p:F1È=FERRITE BEAD(0402)600OHM/300mA,«h°õ¦æ¡¨/¡¨«á¦r¤¸=300mA=300/1000
- 'IF(O1="Bead",IF(Q1¥¤è<0.3¥¤è*0.6,PASS,FAIL)))
- If InStr(Cells(i, "f"), "/") Then '§PÂ_F1Äæ ¦³¡¨/¡¨¦¹¦r¤¸
- M = Val(Split(Cells(i, "f"), "/")(1))
- Msg = InStr(UCase(Split(Cells(i, "f"), "/")(1)), "MA")
- If Msg Then M = Val(Split(Cells(i, "f"), "/")(1)) / 1000
- Msg = Cells(i, "Q") ^ 2 > M
- End If
- End Select
- If Msg <> "" Then
- If Msg = "µL¤u§@¹qÀ£/¹q¬y" Then
- AR(i, 1) = Msg
- Else
- AR(i, 1) = IIf(Msg, "PASS", "FAIL")
- End If
- End If
- Next
-
- With Sh.UsedRange.Columns("S")
- .Cells = AR
- Msg = Array("PASS", "FAIL")
- For i = 0 To UBound(Msg)
- .Replace Msg(i), "=EX", xlWhole
- With .SpecialCells(xlCellTypeFormulas, xlErrors)
- .Cells = Msg(i)
- 'PASSÅã¥Üºñ©³¶Â¦r FAILÅã¥Ü¬õ©³¥Õ¦r
- .Font.Color = IIf(i = 0, vbBlack, vbWhite)
- .Interior.Color = IIf(i = 0, vbGreen, vbRed)
- End With
-
- Next
- .SpecialCells(xlCellTypeConstants).EntireRow.Copy
- End With
- '**********************************************************
- '1.§Ú¥Ø«e¬O·Q°µ¦¨§Ú¥i¥H°õ¦æ¦¹µ{¦¡«á¸õ¥X¤@Óµøµ¡->Åý§Ú¬D¿ïn¸ü¤JªºÀÉ®×->¸ü¤J«á°õ¦æ¹Bºâ->¸õ¥Xµøµ¡Åý§Ú¥i¥t¦s·sÀÉ~
- If MsgBox("½Ð°Ý¬O§_nÀx¦sÀÉ®×?", vbYesNo) = vbYes Then
- fileout = Application.GetSaveAsFilename(FileFilter:="Excel ¬¡¶Ã¯ (*.xlsx),*.xlsx", Title:="¥t¦s¬°·sÀÉ")
- If Not TypeName(fileout) = "String" Then Exit Sub '¨ú®ø«hµ²§ô
- With Workbooks.Add(1)
- .Sheets(1).Paste
- .SaveAs fileout ', FileFormat:=xlWorkbookDefault
- .Close True
- End With
- Else
- Application.CutCopyMode = False
-
- End If
- '******************************
- '1¤£n±Nµ²ªGÅã¥Ü¦bìÀÉ®×
- Sh.Parent.Close False
-
- End Sub
½Æ»s¥N½X |
|