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

¨D§U VBA Vlook ­n°õ¦æ¦n¤[

¨D§U VBA Vlook ­n°õ¦æ¦n¤[

Dear ¤j¤j­Ì
¤U¦C¬O§Ú¤Uªº«ü¥O¡A¥i¬O­n°õ¦æ¦n¤[~~
½Ð°Ý
1.­n¦p¦ó­×§ï¤~¤£·|°õ¦æ¨º¨Ó¤[
2.­n¦p¦ó¥[¤J·íVµL¸ê®Æ®É¡A¥HªÅ­ÈÅã¥Ü
ÁÂÁÂ~ ·P¿E¤£ºÉ



Dim i As Integer
    Range("d4:d2000") = ""    '(²M°£Â¦³¸ê®Æ)
    For i = 4 To 2000           '(¨M©w¦³´X¦h¦æ)
    Range("D4").Select
    Cells(i, "D") = "=VLOOKUP(""1002""&RC[-3],List!C[-3]:C[13],8,FALSE)"
    Cells(i, "D") = Cells(i, "D").Value
    Next i

¦^´_ 1# JOSH


    ­Ó¤H«Øij..
¦pªG±z­n¥ÎVBA ¼gªk
¥i¥H¤£¥Î©ë§ô©ó "=XXXXXX"
ª½±µ§PÂ_´N¦n..
¨Ò¦p
        For i = 4 To ActiveSheet.Range("a4").CurrentRegion.Rows.Count
                       If (InStr(1, ¤u§@ªí1.Cells(i, 1), "·j´M") >= 1) Then
                     ¤u§@ªí1.Cells(i, 7) = "find"
                     ¤u§@ªí1.Cells(i, 11) = InStr(¤u§@ªí1.Cells(i, 1), "·j´M")
                     s = s + 1
                       else
                        ¤u§@ªí1.cells(i,7)=""
                End If

         next

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP






¤j¤j~~ ½Ð°Ý§Ú¨º¸Ì¿ù¤F¶Ü?
½Ð«ü¾É
ÁÂÁÂ

Sub h()

For i = 4 To ActiveSheet.Range("a4").CurrentRegion.Rows.Count
If (InStr(1, Summary!.Cells(i, 1), "=VLOOKUP(""1002""&RC[-3],List!C[-3]:C[13],8,FALSE)") >= 1) Then
Summary!.Cells(i, 7) = "find"
Summary!.Cells(i, 11) = InStr(Summary!.Cells(i, 1), "=VLOOKUP(""1002""&RC[-3],List!C[-3]:C[13],8,FALSE)")
s = s + 1
Else
Summary!.Cells(i, 7) = ""
                End If

         Next

End Sub

TOP

¤j¤j~~ ½Ð°Ý§Ú¨º¸Ì¿ù¤F¶Ü?
½Ð«ü¾É
ÁÂÁÂ

Sub h()

For i = 4 To ActiveSheet.Range("a4") ...
JOSH µoªí©ó 2012-11-15 17:31



    Spare parts2.zip (9.52 KB)

TOP

¦^´_ 4# JOSH
  1. Sub h()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With Sheets("List")
  4. For Each a In .Range(.[A2], .[A2].End(xlDown))
  5.   d(a.Value) = a.Offset(, 7)
  6. Next
  7. End With
  8. With Sheets("Summary")
  9. For i = 4 To .[IV2].End(xlToLeft).Column
  10. For Each a In .Range(.[A4], .[A4].End(xlDown))
  11. .Cells(a.Row, i) = d(.Cells(2, i) & a)
  12. Next
  13. Next
  14. End With
  15.   
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# JOSH

Sub h()

For i = 4 To ActiveSheet.Range("a4").CurrentRegion.Rows.Count
If (InStr(1, Summary!.Cells(i, 1), "=VLOOKUP(""1002""&RC[-3],List!C[-3]:C[13],8,FALSE)") >= 1) Then
Summary!.Cells(i, 7) = "find"
Summary!.Cells(i, 11) = InStr(Summary!.Cells(i, 1), "=VLOOKUP(""1002""&RC[-3],List!C[-3]:C[13],8,FALSE)")
s = s + 1
Else
Summary!.Cells(i, 7) = ""
                End If

         Next

End Sub

Summary! .cells(XXXXXXXXXXXX
§ï¦¨ Summary.cells(XXXXXXXXXXXXXXX

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦^´_ 5# Hsieh


    ª©¤j~~
¥i¥H³Â·Ð§A¦AÀ°§Ú¬Ý¬Ý¶Ü~
§Ú±a¥X¨Óªº¸ê®Æ³£¤@¼Ë
ÁÂÁÂ

Spare parts 1.zip (266.21 KB)

TOP

Dear Hsieh ª©¤j~~
¥i¥HÀ°§Ú¬Ý¬Ý¶Ü?2012-11-16 18:01
ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD