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

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

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

½Ð°Ý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 ©ó 2013-3-1 01:07 ½s¿è
½Ð°Ý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)))
twbts20130301.jpg
2013-3-1 00:54


VBA ªº¸Ü
twbts20130301instr.jpg
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
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 2# kimbal


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

TOP

¦^´_  kimbal


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



    ½Ð¬Ý¤W­±½s¿è
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 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

TOP

¦^´_  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

TOP

¦^´_ 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
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¥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

  ¦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

  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. j = 2
  10. LastRec = Sheets("state").Range("A1").CurrentRegion.Rows.Count
  11. fs = "W:\Payment Daily Report\DOCS RECEIVED N RELEASED RECORD.xlsx"
  12. Set Wb = Workbooks.Open(fs)
  13. With ThisWorkbook.Worksheets("State")
  14. k = Wb.Sheets("¦¬¥ó°O¿ý").Range("A1").CurrentRegion.Rows.Count
  15. For l = 2 To LastRec

  16. Do
  17. If Worksheets("State").Range("A" & l).Value = Wb.Sheets("¦¬¥ó°O¿ý").Range("A" & j).Value Then
  18. Worksheets("State").Range("J" & l).Value = Wb.Sheets("¦¬¥ó°O¿ý").Range("h" & j)
  19. 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 = ""
  20. End If
  21. j = j + 1
  22. Loop Until j = k Or (InStr(1, Worksheets("State").Cells(l, 10), "OBL") >= 1)
  23. Next
  24. End With
  25. End Sub
½Æ»s¥N½X
¦^´_ 7# kimbal

Test.rar (12.3 KB) DOCS RECEIVED N RELEASED RECORD.rar (226.13 KB)
­Óµ{¦¡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ªÅ®æ

TOP

¦^´_ 8# mark15jill

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

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD