½Ð°Ý¦p¦ó±NFORMULAR Âà¼g¦¨VBA??

cdcc

Date        Description         Amount        New Category                       
1/5/2010         PRC Salaries         71,904         Payroll in PRC                Criteria                        Type
1/5/2010        HK Salaries         92,129         Payroll in HK                PRC Salaries        Payroll in PRC
7/6/2010        EE Transfer 2010/01 73,038         Fund Transfer                HK Salaries        Payroll in HK


CELL D4  = {INDEX(G:G,LARGE(IF(ISERROR(FIND($F$5:$F$8,$B4)),0,ROW($F$5:$F$8)),1))}

¦]¬°¤j¶qDATA, ©Ò¥HFORMULAR ¹Bºâ³t«×¤ñ¸ûºC.

½Ð°Ý¦p¦ó§PÂ_½d³ò, §@¥X­pºâ?

ÁÂÁÂ

chin15

³Ì¦n¤W¶Ç¹ê»Ú¤å¥ó
³o¼Ë±Æª©¬Ý¤£¥X¸ê®Æ¦ì¸m

cdcc

³Ì¦n¤W¶Ç¹ê»Ú¤å¥ó
³o¼Ë±Æª©¬Ý¤£¥X¸ê®Æ¦ì¸m
chin15 µoªí©ó 2011-5-11 23:03

   ¤w¸g¤W¶Ç¤å¥ó

   §Æ±æ¤j®aÀ°¦£¸Ñµª

Hsieh

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. For Each a In Range([F5], [F65536].End(xlUp))
   
4.    d(a & "") = a.Offset(, 1).Value
   
5. Next
   
6. For Each a In Range([B4], [B65536].End(xlUp))
   
7.    For Each ky In d.keys
   
8.       If InStr(a, ky) > 0 Then a.Offset(, 2) = d(ky): Exit For
   
9.    Next
   
10. Next
    
11. End Sub

½Æ»s¥N½X

cdcc

¦^´_ 4# Hsieh


­º¥ýÁÂÁÂÀ°¦£
   
¦ý¥i§_¸ÑÄÀ"CreateObject("Scripting.Dictionary"³o¥yªº¥\¯à¬O¤°»ò¥Î³~?

keysªº¥\¯à¬O¤°»ò¥Î³~?

¦Ó¥B¨º¤@¥y»yªk¬O¼g °}¦C{}??



ÁÂÁÂ.

Hsieh

Sub ex()
Set d = CreateObject("Scripting.Dictionary")  '«Ø¥ß¤@­Ó¦r¨åÅܼÆ
For Each a In Range([F5], [F65536].End(xlUp))
   d(a & "") = a.Offset(, 1).Value  '§âFÄæ¸ê®Æ·í§@¯Á¤Þ¡AGÄæ¸ê®Æ°µ¹ïÀ³ª½
Next
For Each a In Range([B4], [B65536].End(xlUp))  '¥HBÄæ¸ê®Æ°µ°j°é
   For Each ky In d.keys   '¦r¨åª«¥óªº¨C­Ó¯Á¤Þ­È°µ°j°é
      If InStr(a, ky) > 0 Then a.Offset(, 2) = d(ky): Exit For  '¦pªG¯Á¤Þ­È¦³³QBÄæ¸ê®Æ¥]§t®É¡A±NDÄæ¶ñ¤J¯Á¤Þªº¹ïÀ³­È
   Next
Next
End Sub

cdcc

Sub ex()
Set d = CreateObject("Scripting.Dictionary")  '«Ø¥ß¤@­Ó¦r¨åÅܼÆ
For Each a In Range([F5], ...
Hsieh µoªí©ó 2011-5-12 23:13

    ÁÂÁ­@¤ß±Ð¾É.

dechiuan999

ª©¥D¤j¤j§A¦n¡G

   ¤p§Ì¦³¤@ÂI¤£¤F¸Ñªº¦a¤è¡A
½Ðª©¥D¤j¤j©ú¥Ü¡C

d(a & "") ¬°¦ó¥i¥H¨ú¥N d(a.value)©O¡H

·P®¦¤j¤j¡I

GBKEE

¦^´_ 8# dechiuan999
d(a & "") ¬°¦ó¥i¥H¨ú¥N d(a.value)©O¡H
a&"" ¨Ã«D¬O­n¨ú¥N a.valueªº¥Î·N ,  a&""   -> ±Naªº¤º®eÂà´«¬°¦r¦ê
¦]¬°Scripting.Dictionary¬Oª«¥ó , ¥Î©óÀx¦s¸ê®ÆÃöÁä¦r©M¶µ¥Ø(¥]§tÀx¦s®æ)

1. Sub Ex()
   
2. Dim d As Object, a
   
3. Set D = CreateObject("Scripting.Dictionary")
   
4. With ActiveSheet
   
5.     For Each a In .Range(.[F5], .[F10])
   
6.         D(a) = ""
   
7.     Next
   
8.     For Each a In D.keys
   
9.         MsgBox a.Address & ":  " & a.Value & ":  " & a.Row
   
10.         a.Select
    
11.     Next
    
12. End With
    
13. End Sub

½Æ»s¥N½X

dechiuan999

ÁÂÁÂGBKEEª©¥D¤j¤j¡C
EXCEL VBA¯u¬O³Õ¤jºë²`¡C
¤]¯u¬O¶V¾Ç¶V¦³½ì¡C

·P®¦¤j¤j¡I