[µo°Ý] ½Ð°ÝVBA ªºµ{¦¡¦³¨S¦³¥i¥H¿ë»{¬Y­ÓÀx¦s®æ¤ºªº¦r¤¸¦³µL¥]§t¬Y´X­Ó¦r¦ê¡H

198188

½Ð°ÝVBA ªºµ{¦¡¦³¨S¦³¥i¥H¿ë»{¬Y­ÓÀx¦s®æ¤ºªº¦r¤¸¦³µL¥]§t¬Y´X­Ó¦r¦ê¡H
¨Ò¦p
¥t¥~µL½×¬O¤j¼g©Î¤p¼g³£¿ë»{¨ì
in   A1  A3  A4 ¥]§t in
A1   window   
A2  office
A3  tina
A4  WINNIE   

¥t¥~¥i§_§ä´M¦r¦êªº¦ì¸m
¨Ò¦p
in   
A1   window                       3
A2  office   
A3  tina                                2
A4  WINNIE                       2

kimbal

½Ð°ÝVBA ªºµ{¦¡¦³¨S¦³¥i¥H¿ë»{¬Y­ÓÀx¦s®æ¤ºªº¦r¤¸¦³µL¥]§t¬Y´X­Ó¦r¦ê¡H
¨Ò¦p
¥t¥~µL½×¬O¤j¼g©Î¤p¼g³£¿ë»{ ...
198188 µoªí©ó 2013-3-1 00:48

¥ÎEXCELªºSEARCH¤½¦¡§Y¥i
    =IF(ISERROR(SEARCH("IN",UPPER(A1))),"",SEARCH("IN",UPPER(A1)))

¤U¸ü (47.44 KB)

2013-3-1 00:54

VBA ªº¸Ü

¤U¸ü (55.44 KB)

2013-3-1 01:06

1. Public Function csearch(find_text, rng_within)
   
2.     Dim result
   
3.     result = InStr(1, UCase(rng_within.Value), UCase(find_text))
   
4.     csearch = IIf(result = 0, "", result)
   
5. End Function

½Æ»s¥N½X

198188

¦^´_ 2# kimbal


    ·PÁ¡A¦ý¥Î¤@¯ëexcel§Úª¾¹D¡A§Ú·Qª¾¹Dvba¦³µL³o­Ó¥\¯à¡H

kimbal

¦^´_  kimbal


    ·PÁ¡A¦ý¥Î¤@¯ëexcel§Úª¾¹D¡A§Ú·Qª¾¹Dvba¦³µL³o­Ó¥\¯à¡H
198188 µoªí©ó 2013-3-1 01:00

    ½Ð¬Ý¤W­±½s¿è

198188

¦^´_ 4# kimbal

·PÁÂ
    csearch = IIf(result = 0, "", result)
½Ð°Ý¦pªG¥u¬OÀˬd¦³¨S¦³¬O§_§ï¦¨csearch = IIf(result = 0, "¨S¦³", ¡§¦³¡¨)

¥t¥~½Ð°Ý
DO
UNTIL
¥i§_¦P®É¦³¨â­ÓUNTILªº±ø¥ó
¨Ò¦p¡G
DO

UNTIL i>=1 or l = 0
UNTIL i>=1 and l = 0

198188

¦^´_  kimbal

·PÁÂ
    csearch = IIf(result = 0, "", result)
½Ð°Ý¦pªG¥u¬OÀˬd¦³¨S¦³¬O§_§ï¦¨csea ...
198188 µoªí©ó 2013-3-1 01:26

    ³o­Óµ{¦¡¬O§_»Ý­n¦bCÄæ³B¼g csearch("in"a1,)?
¦pªG¦bVBA¤ºª`©ú SEARCH "IN"¥i¥H¶Ü¡H¥t¥~­^¤å¤j¼g©M¤p¼g³£¥i¥H¿ë»{¨ì¶Ü¡H©ÎªÌ¦p¦ó«ü©w¿ë»{¤j¼g©Î¤p¼g¡H

kimbal

¦^´_ 6# 198188


    ¦^´_ 4# kimbal


>    csearch = IIf(result = 0, "", result)
> ½Ð°Ý¦pªG¥u¬OÀˬd¦³¨S¦³¬O§_§ï¦¨csearch = IIf(result = 0, "¨S¦³", ¡§¦³¡¨)

¹ï°Ú,´N¬O³o¼Ë.


>¥t¥~½Ð°Ý
>DO
>UNTIL
>¥i§_¦P®É¦³¨â­ÓUNTILªº±ø¥ó
>¨Ò¦p¡G
>DO
>UNTIL i>=1 or l = 0
>UNTIL i>=1 and l = 0

¥i¥Hªº,³o¼Ë´N¥i
    Do
        ...
    Loop Until i> = 5 and l = 0
///
    Do
        ...
    Loop Until i> = 5 Or l = 0



>¦pªG¦bVBA¤ºª`©ú SEARCH "IN"¥i¥H¶Ü¡H¥t¥~­^¤å¤j¼g©M¤p¼g³£¥i¥H¿ë»{¨ì¶Ü¡H©ÎªÌ¦p¦ó«ü©w¿ë»{¤j¼g©Î¤p¼g¡H

²{¦b¬O¤£½×¤j¼g¤p¼g³£¥i¥H¿ë»{¨ìªº,
¦]¬°¥Î¤Fucase§â¨â­Ó¿é¤J³£¥ýÂন¤j¼g,µM«á¤~°µ¤ñ¸û
result = InStr(1, UCase(rng_within.Value), UCase(find_text))
¨Ò¦p:
result  = Instr(1, ucase("abc"), ucase("B"))
­pºâ®É·|Åܬ°
result  = Instr(1, "ABC", "B")
result = 2


®³¨«ucase´Nªº¸Ü´N­n¤j¤p¼g¥þ¹ï¤~¥i¥H§ä¨ì¦ì¸m
result = InStr(1, rng_within.Value, find_text)
¨Ò¦p:
result  = Instr(1, "abc", "B")
result = 0

§â"B"§ï¦¨"b"´N¥i¥H±o¥X2
result  = Instr(1, "abc", "b")
result = 2

mark15jill

¥i¥H°Ñ¦Ò¥H¤U­ì©l½X
¬õ¦r§Y¬°»Ý·j´M±o¦r¦ê
Sub A()
                     ¤u§@ªí3.Cells(1, 16) = "·j´M¥Ø¼Ð·½"
                     ¤u§@ªí3.Cells(1, 17) = "§PÂ_¬O§_§ä¨ì"
                     ¤u§@ªí3.Cells(1, 18) = "ÃöÁä¦r¦ì¸m"
                     ¤u§@ªí3.Cells(1, 19) = "¥N½X"


        For i = 2 To ActiveSheet.Range("b2").CurrentRegion.Rows.Count
                If (InStr(1, ¤u§@ªí3.Cells(i, 2), "¤º") >= 1) Then
                     ¤u§@ªí3.Cells(i, 16) = ¤u§@ªí3.Cells(i, 2) '·j´M¥Ø¼Ð·½
                     ¤u§@ªí3.Cells(i, 17) = "find" '§PÂ_¬O§_§ä¨ì
                     ¤u§@ªí3.Cells(i, 18) = InStr(¤u§@ªí3.Cells(i, 2), "¤º") 'ÃöÁä¦r¦ì¸m
                     ¤u§@ªí3.Cells(i, 19) = "3"  '¥N½X
                     s = s + 1
                End If
        Next
              ' ¤u§@ªí3.Cells(i, 17) = "find"
End Sub

198188

1. Sub Load_State_Detail()
   
2. Dim FRng As Range
   
3. Dim A As Range, Rng As Range
   
4. Dim i As Integer
   
5. Dim LastRec As Integer
   
6. Dim l As Integer
   
7. Dim k As Integer
   
8. Dim j As Integer
   
9. 
   
10. 
    
11. j = 2
    
12. LastRec = Sheets("state").Range("A1").CurrentRegion.Rows.Count
    
13. fs = "W:\Payment Daily Report\DOCS RECEIVED N RELEASED RECORD.xlsx"
    
14. Set Wb = Workbooks.Open(fs)
    
15. With ThisWorkbook.Worksheets("State")
    
16. k = Wb.Sheets("¦¬¥ó°O¿ý").Range("A1").CurrentRegion.Rows.Count
    
17. For l = 2 To LastRec
    
18. 
    
19. Do
    
20. If Worksheets("State").Range("A" & l).Value = Wb.Sheets("¦¬¥ó°O¿ý").Range("A" & j).Value Then
    
21. Worksheets("State").Range("J" & l).Value = Wb.Sheets("¦¬¥ó°O¿ý").Range("h" & j)
    
22. If (InStr(1, Worksheets("State").Cells(l, 10), "OBL") >= 1) Then Worksheets("State").Range("J" & l).Value = Wb.Sheets("¦¬¥ó°O¿ý").Range("h" & j) Else Worksheets("State").Range("J" & l).Value = ""
    
23. End If
    
24. j = j + 1
    
25. Loop Until j = k Or (InStr(1, Worksheets("State").Cells(l, 10), "OBL") >= 1)
    
26. Next
    
27. End With
    
28. End Sub

½Æ»s¥N½X

¦^´_ 7# kimbal

Test.rar (12.3 KB)

¤U¸ü¦¸¼Æ: 6

2013-3-8 15:52

DOCS RECEIVED N RELEASED RECORD.rar (226.13 KB)

¤U¸ü¦¸¼Æ: 5

2013-3-8 15:52

­Óµ{¦¡If Worksheets("State").Range("A" & l).Value = Wb.Sheets("¦¬¥ó°O¿ý").Range("A" & j).Value Then ¥X²{RUN TIME ERROR "9" SUBSCRIP OUT OF RANG?
§Ú³o­Óµ{¦¡ªº§@¥Î¬O
¦btest excelùتºstate sheet AÄ檺­q³æ¸¹¦pªG¦bDOCS RECEIVED N RELEASED RECORDªí¤º¦³³o­Ó­q³æ¸¹¡A¦Ó¥B¦bHÄæùتº¦r¤¸§t¦³"OBL"¡A ¨º»ò¦btest excelùتºstate sheetªºJÄæ´NÅã¥ÜDOCS RECEIVED N RELEASED RECORDªí¤ºHÄ檺¦r¡A§_«h´NªÅ®æ

198188

¦^´_ 8# mark15jill

½ÐÀ°§Ú¬Ý¬Ý¤W­±ªº°ÝÃD¡AÁÂÁÂ