ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] §ä¤F¦n´X¤Ñ §ä¤£¥X¿ù»~,½Ð¨ó§U?

[µo°Ý] §ä¤F¦n´X¤Ñ §ä¤£¥X¿ù»~,½Ð¨ó§U?

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-9-19 13:14 ½s¿è

§Ú¦b"main"¤u§@ªíªºA2Àx¦s®æ(§Ú©w¸q¥¦¬°product)¿é¤J¤@­Ó²£«~¥N¸¹,«h¦b"main"¤u§@ªíªºrange("C2:C20")·|¦Û°ÊÅã¥Ü¸Ó²£«~©Ò»Ýªº­ì§÷®Æ¥N¸¹
­ì²z¬O§Ú±N"main"¤u§@ªíªºrange("C2:C20")³]¦¨°}¦C¨ç¼Æ(=Sample(product)),³o­Ó¨ç¼Æ·|¨ì"material"¤u§@ªíªºAÄæ¥h´M§ä»P"main"¤u§@ªíC2Àx¦s®æ¦r¦ê¬Û¦PªºÀx¦s®æ,·í§ä¨ì«á·|±N¹ïÀ³ªº"material"¤u§@ªíªºBÄæÀx¦s®æ¸ê®ÆÀx¦s¦¨°}¦C,³Ì«á¦A¦^¶Çµ¹Sample
§Ú°»´úªºµ²ªG,¿ù»~¬O¦bfindnext¤W,·í²Ä¤@¦¸¥Hfind§ä¨ì®É,½T¹ê¬O¦b$A$19Àx¦s®æ¤W,¦ý¬O±µ¤U¨Óset rng=.findnext(rng)ªºµ²ªG,«o©lrngÅܦ¨nothing?(¥¿±`À³¸Ó¬O$A$20¤~¹ï)



µ{¦¡½X¦p¤U:
Public Function Sample(product As Variant) As Variant
    Application.Volatile
    Dim oRange As Range
    Dim rng As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim FoundAt As String
    Dim temp(18) As Variant
    Dim i As Long
    Dim MySearch As Variant
    On Error GoTo Err
    If TypeName(product) = "String" Then
        MySearch = Range(product).Value
    ElseIf TypeName(product) = "Range" Then
        MySearch = product.Value
    End If
    With Sheets("material").Columns("A")
            Set rng = .Find(What:=MySearch)

            If Not rng Is Nothing Then
                FirstAddress = rng.Address
                Do
                    temp(i) = rng.Offset(0, 1).Text
                    Set rng = .FindNext(rng)    '¨ç¼Æ°õ¦æ¨ì³o¸Ì®É,·|¥X¿ù
                                                '¸g°»¿ù,«e­±²Ä¤@­Ófind§ä¨ìªº¦ì¸m$A$19¨S¿ù
                                                '¥»¦¸ªºfindnext°õ¦æ«á,rngÅܦ¨nothing
                                                '¦ý¥¿½Tªº¦ì¸mÀ³¸Ó¬O$A$20
                    'If rng Is Nothing Then Exit Do
                    i = i + 1
                Loop While Not rng Is Nothing And rng.Address <> FirstAddress
            End If
    End With
    Sample = temp
    Set rng = Nothing
    Exit Function
Err:
    Set rng = Nothing
    MsgBox Err.Description
    Sample = "N/A"
End Function

¹ï¤£°_ ¼g¿ù¤F ²Ä2¦Cªº"main"¤u§@ªíC2Àx¦s®æ §ó¥¿¬°"main"¤u§@ªíA2Àx¦s®æ

TOP

¦^´_ 2# ltwinery
  1. With Sheets("material").Columns("A")
  2.             Set rng = .Find(What:=MySearch, LOOKAT:=xlWhole)
  3.             If Not rng Is Nothing Then
  4.                 FirstAddress = rng.Address
  5.                 Do
  6.                     temp = rng.Offset(0, 1).Text
  7.                     Set rng = .Find(What:=MySearch, After:=rng)     '¨ç¼Æ¸Ì­n¦³ After:=rng
  8.                     i = i + 1
  9.                 Loop While Not rng Is Nothing And rng.Address <> FirstAddress
  10.             End If
  11.     End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¤w¸Ñ¨M
·P®¦,¨ØªA,ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD