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

[µo°Ý] vlookup³t«×ºC¡A¨Ï¥Îvba¨ú¥Nªºµ{¦¡½X

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-5 01:57 ½s¿è

¦^´_ 10# n7822123

´£¨Ñ¥ÎFind¨Ó¬d§äªº¤èªk¡A¤£¹L¨S¦³¦r¨åª«¥ó¨Óªº§Ö(³æ¯Â¥Î¨Ó´ú¸Õ)

Sub test_3()
Dim R%, T1
T1 = Timer
Application.ScreenUpdating = False
Sheets("Search").Activate  '­Y«ö¶s¦b"Search"­¶­±¥i¬Ù²¤¦¹¦Cµ{¦¡
For R = 2 To [A1].End(4).Row
    On Error Resume Next
      Cells(R, 2).Resize(, 3) = [data!A:A].Find(Cells(R, 1), lookat:=xlWhole).Offset(, 1).Resize(, 3).Value
      If Err = 91 Then Cells(R, 2).Resize(, 3) = "No Data"   '§ä¤£¨ì·|²£¥Í¿ù»~½X:91
    On Error GoTo 0
Next R
MsgBox "¦@¯Ó®É" & Round(Timer - T1, 2) & "¬í"
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 9# ­ã´£³¡ªL

­ã¤j
¦]¬°¤£À´±o®M¥Î, ©Ò¥H¥Î¤½¦¡°µ¤F¤@­Ó½d¨Ò
¥i¥H½Ð§A§â¥¦¼g¦¨VBA¶Ü?

¸ê®Æ¦@¦³6­¶, ¨C¤@­¶³Ì¤Öªº¸ê®Æ¦³5¸Uµ§, ³Ì¦hªº¦³7¸Uµ§

Dictionary_01.rar (17.94 KB)

TOP

¦^´_ 12# Qin

Sub Ex_01()
Dim xD, Arr, Brr, xA As Range, xS As Worksheet, R&, C%, i&, j%
Set xD = CreateObject("scripting.dictionary")
R = [Search!A1].Cells(Rows.Count, 1).End(3).Row
Arr = [Search!A1:O1].Resize(R)
For i = 2 To UBound(Arr, 2):   xD(Arr(1, i) & "") = i - 1:   Next '¼Ð°O[Äæ]¦ì¸m
For i = 2 To UBound(Arr):   xD(Arr(i, 1) & "") = i - 1:   Next  '¼Ð°O[¦C]¦ì¸m
Set xA = [Search!B2:O2].Resize(R - 1)  '¸ê®Æ¶ñ¤J°Ï(§Y­ì¤½¦¡°Ï)
xA = "No Data"  '¹w¥ý¶ñ¤J[No Data], «Ý¦³²Å¦X¦AÂл\
Arr = xA.Value  '±a¤JArray

For Each xS In Sheets(Array("AB", "CD", "EF", "GH", "KL", "MN"))
    Brr = xS.UsedRange
    For i = 2 To UBound(Brr)
        R = Val(xD(Brr(i, 1) & "")): If R = 0 Then GoTo 101
    For j = 2 To UBound(Brr, 2)
        C = Val(xD(xS.Name & "_" & Brr(1, j)))
        If C > 0 Then Arr(R, C) = Brr(i, j)
    Next j
101: Next i
Next
xA.Value = Arr
End Sub

Dictionary_01v.rar (17.7 KB)

TOP

¦^´_ 6# Qin

¤£ºC£«,win 10 ,2010 ¤U ´ú¸Õ¥u»Ý9-10¬í.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 13# ­ã´£³¡ªL

­ã¤j
§Ú¤S¹J¨ì°ÝÃD¤F...
2­Óµ{¦¡½X, ·Q±N¥¦­×§ï¦¨:
1) ¤£½×¬O¿é¤J¤j¼g©Î¤p¼g³£¥i¥H§ì¨ì¸ê®Æ
2)±j¨îPart No. ¤@©w­n§¹¾ã¿é¤J, ¤~·|§ì¨ì¸ê®Æ
3) ¦pªG"A" Äæ¬Y­Ó³æ¤¸®æ¿é¤J¿ù»~, §R°£«á, B & C Äæ³æ¤¸®æ¨½ªº¸ê®Æ¤]­n¤@°_"²M°£"

Test1.rar (16.47 KB)

TOP

¦^´_ 15# Qin
¢°¡^¦Û°Ê¨ú¹ïÀ³­È
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range, xF As Range
With Target
     If .Columns.Count > 1 Or .Column <> 1 Then Exit Sub
     For Each xR In .Cells
         If .Row = 1 Then GoTo 101
         xR(1, 2).Resize(1, 2).ClearContents
         If xR = "" Then GoTo 101
         Set xF = [Sheet1!A:A].Find(xR, LookAt:=xlWhole, MatchCase:=False)
         If xF Is Nothing Then GoTo 101
         xR(1, 2).Resize(1, 2) = xF(1, 2).Resize(1, 2).Value
101: Next
End With
End Sub

¥i¥H¥u¹ïAÄæ³æ¤@Àx¦s®æ¿é¤J¨ú¹ïÀ³­È, ©Î¤@¦¸¶K¤J¦h­Ó¬d¸ß­È¨ú¹ïÀ³~~
¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð
¢±¡^¦r¨åªk¤ñ¹ï¨ú­È
¥u­n§ï¨â­Ó¡]¥[Ucase, ©ÎLcase, ±N­^¤å¦r±j¨îÂର¤j¼g©Î¤p¼g¡^
xD(UCase(Arr(i, 1))) = i
R = Val(xD(UCase(Brr(i, 1))))
¡@
¡@
¡@

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-11 11:53 ½s¿è

¦^´_ 15# Qin


¦r¨å¥i³]©w¬O§_°Ï¤À¤j¤p¼g
¹w³]¼Ò¦¡¤U¡A·|°Ï¤À¤j¤p¼g
§â¦r¨åªºCompareModeÄݩʳ]¬°1¡A§Y¤£¤À¤j¤p¼g
¥H¤U¬OTest½d¨Ò

Sub ex()
Set D = CreateObject("scripting.dictionary")
D.CompareMode = 1      '¦r¨å¤£°Ï¤À¤j¤p¼g
D("abc") = 22
D("ABC") = 55
MsgBox D("abc") & "," & D("ABC")
End Sub

¸Ô²ÓVBA»¡©ú¦p¤U¹Ï
111.png
2018-11-11 11:51
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 16# ­ã´£³¡ªL

¥i¥H¥u¹ïAÄæ³æ¤@Àx¦s®æ¿é¤J¨ú¹ïÀ³­È, ©Î¤@¦¸¶K¤J¦h­Ó¬d¸ß­È¨ú¹ïÀ³~~

  
¤ÓêɤF, ­ì¨Ó¤@½gµ{¦¡½X´N¯à¸Ñ¨Mªº¨Æ §Ú«o儍儍ªº¥H¬°­n¥Î2½g¤~¯à¹ê²{
­ã¤j§A¤Ó¤û¤F°Õ¡K
³o§¹¥þ¬O§Ú·Q­nªº®ÄªG.
°ª¿³«á, «oµo²{¦Û¤v¤£À´±o­×§ïÄæ¦ì.
¦]¬°¦³«Ü¦hExcel Àɳ£­n¥Î¨ì¦¹µ{¦¡½X
¦]¦¹¤S¦A«pÃC¤W¨Óµo°Ý.

°ÝÃD¦bªþÀÉ
bcca ÀÉ password :  1234    &   pass

wPrg.rar (65.25 KB)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-11-19 16:42 ½s¿è

¦^´_ 18# Qin

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range, xF As Range, xCr, xCf, j%
xCr = Array(3, 6, 7) '¥»ªí­n¶K¤JªºÄæ¦ì
xCf = Array(2, 4, 5) '¨Ó·½ªí­n½Æ»sªºÄæ¦ì
With Target
     If .Columns.Count > 1 Or .Column <> 1 Then Exit Sub
     For Each xR In .Cells
         If .Row = 1 Then GoTo 101
         xR(1, 2).Resize(1, 7).ClearContents
         If xR = "" Then GoTo 101
         Set xF = Sheet1.[A:A].Find(xR, LookAt:=xlWhole, MatchCase:=False)
         '_Sheet1¬°¨Ó·½ªíªº[ÄݩʦWºÙ], ¤u§@ªí¦WºÙ¥i¥ô·N§ó§ï¦Ó¤£¼vÅT(¨£¤U¹Ï)
         If xF Is Nothing Then GoTo 101
         For j = 0 To UBound(xCr)
             xR(1, xCr(j)) = xF(1, xCf(j)).Value
         Next j
101: Next
End With
End Sub

XX001.gif
2018-11-19 16:41

TOP

¦^´_ 19# ­ã´£³¡ªL

­ã¤j
ÁÂÁ§A¤SÀ°¤F§Ú­Ó¤j¦£
1)Åý§Ú¥i¥H¥ô·N¨Ï¥Î¤£¦PªºÄæ¦ì
2)50­¶¤u§@ªí¤£¦]¤u§@ªí¦WºÙÅܰʪº°ÝÃD¸Ñ¨M¤F, §K¥h¤F»Ý­n³v­¶¥h­×§ïªº·Ð´o

·Q¦A½Ð°Ý, ¦³¨S¦³³o¼Ë²§·Q¤Ñ¶}ªº¼gªk
´N¬O·í§Ú§âwPrg¸ê®Æ½Æ»s¥hbcca ÀÉ®É,¬O§_¤]¥i¥H¦P®É­×§ï¤u§@ªí¦WºÙ¨Ã¥[¤W·í¤Ñ¤é´Á. " w_PRG_20181124"

¦]¬°¦³¤Ó¦h¹³³o¼ËªºÀÉ­n³B²z, ¦pªG¥H¤Wªº­n¨D¥i¥H¹ê²{, ¨º¹ê¦b¬O¤Ó§¹¬ü¤F.

wPrg1.rar (61.94 KB)

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD