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

[µo°Ý] ¦p¦ó¸g¥Ñªí³æªº¿é¤J,¦b²M³æ§ä¥X¹ïÀ³ªº¸ê®Æ

[µo°Ý] ¦p¦ó¸g¥Ñªí³æªº¿é¤J,¦b²M³æ§ä¥X¹ïÀ³ªº¸ê®Æ

½Ð°Ý¦U¦ì«e½ú

¤p§Ì¤w¸Õ¹L³\¦h§ÚÀ´ªº¤è¦¡¥h¼g,¤´¤£¯à¼g¥X§Ú­nªºµ²ªG,

1.¦bªþÀɸÌÀÉ®×, ¦@¦³3­ÓÄæ¦ì(²M³æ), ¦Ó¦bªí³æ¿é¤J item°T®§ , «h¹ïÀ³²M³æ¤º§ä¥Xitem ¹ïÀ³ªº qty

2.¦b«ö¤U½T»{®É,«h¸ÓÄæ¦ì«hmark  ¦¨¥ô¤@ÃC¦â.

3.«ö¤U²M°£, «h²M°£²M³æ¤º©Ò¦³¸ê®Æ¤Î®æ¦¡(ÃC¦â)


¦A³Â·Ð¦U¦ì¤F test.rar (12.96 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-8-20 00:46 ½s¿è
½Ð°Ý¦U¦ì«e½ú

¤p§Ì¤w¸Õ¹L³\¦h§ÚÀ´ªº¤è¦¡¥h¼g,¤´¤£¯à¼g¥X§Ú­nªºµ²ªG,

1.¦bªþÀɸÌÀÉ®×, ¦@¦³3­ÓÄæ¦ì(²M ...
melvinhsu µoªí©ó 2014-8-19 18:16


test-a.zip (12.7 KB)

µe­±¤Wª«¥ó¦WºÙ©w¸q:
item¿é¤J®Ø¬°  TextBox2, qty µ²ªGÅã¥Ü°Ï¬° Label3,
²M³æCLEAR«ö¶s¬° CommandButton2, ½T»{«ö¶s¬° CommandButton1

ActiveSheet ¤º
  1. Private Sub CommandButton1_Click()
  2.    If Not rTar Is Nothing Then _
  3.         rTar.Resize(1, 3).Interior.ColorIndex = Int(13 * Rnd + 2) * 2
  4. End Sub

  5. Private Sub CommandButton2_Click()
  6.   With Columns("A:C")
  7.     .ClearContents
  8.     .Interior.ColorIndex = -4142
  9.   End With
  10. End Sub

  11. Private Sub TextBox2_Change()
  12.   Dim lRow&
  13.   Dim dItm
  14.   
  15.   Label3 = ""
  16.   lRow = 2
  17.   Do While Cells(lRow, 1) <> ""
  18.    If Cells(lRow, 1) = TextBox2 Then
  19.      Label3 = Cells(lRow, 3)
  20.      Set rTar = Cells(lRow, 1)
  21.      Exit Do
  22.    End If
  23.    lRow = lRow + 1
  24.   Loop
  25.   If Label3 = "" Then MsgBox "§ä¤£¨ì¸ê®Æ"
  26. End Sub
½Æ»s¥N½X
Module1 ¤º
  1. Public rTar As Range
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-20 07:17 ½s¿è

¦^´_ 2# luhpro
  1. Private Sub CommandButton2_Click()
  2.         With Columns("A:C")
  3.             .ClearContents
  4.             'ClearContents ¤èªk ²M°£½d³òªº¤½¦¡¡C²M°£¹Ïªí¤¤ªº¸ê®Æ¦ý«O¯d®æ¦¡³]©w¡C
  5.             '****²M°£½d³òªº¤½¦¡¤]¥]§t²M°£½d³òªº¦r¦ê***
½Æ»s¥N½X
¸Õ¸Õ¬Ý
  1. 'Sheet1¼Ò²Õªºµ{¦¡½X
  2. Option Explicit
  3. Dim d As Object
  4. Private Sub CommandButton1_Click()
  5.     Dim Text(1 To 2) As String
  6.     Text(1) = Trim(TextBox1)
  7.     Text(2) = Trim(TextBox2)  '¼Ó¥DÀɮצ³¥Î¨ìTextBox2
  8.     UsedRange.ClearFormats    '¨ú¥N  ²M³æCLEAR«ö¶s , CommandButton2
  9.     If Text(1) <> "" And Text(2) <> "" Then
  10.         If d Is Nothing Then item_qty
  11.         If d.EXISTS(Text(1) & Text(2)) Then
  12.             d(Text(1) & Text(2)).Interior.ColorIndex = 44
  13.         End If
  14.     End If
  15. End Sub
  16. Private Sub item_qty()
  17.     Dim r As Range
  18.     Set d = CreateObject("scripting.dictionary") '¦r¨åª«¥ó
  19.     For Each r In UsedRange.Rows
  20.         'item(AÄd)&qty(CÄd)¤º®e¬°¦r¨åª«¥ókey­È,«ü©w¨ì Range
  21.         Set d(r.Cells(1, 1) & r.Cells(1, 3)) = r.Cells(1, 1).Resize(, 3)        
  22.     Next
  23. End Sub
½Æ»s¥N½X
¤£¥Î¨Ï¥Î CommandButton1,
TextBox1,TextBox2 ¦³§ïÅܪ½±µÅã¥Ü
  1. 'Sheet1¼Ò²Õªºµ{¦¡½X
  2. Option Explicit
  3. Dim d As Object
  4. Private Sub TextBox1_Change()
  5.     Ex
  6. End Sub

  7. Private Sub TextBox2_Change()
  8.     Ex
  9. End Sub
  10. Private Sub Ex()
  11.     Dim Text(1 To 2) As String
  12.     Text(1) = Trim(TextBox1)
  13.     Text(2) = Trim(TextBox2)  '¼Ó¥DÀɮצ³¥Î¨ìTextBox2
  14.     UsedRange.ClearFormats    '¨ú¥N  ²M³æCLEAR«ö¶s , CommandButton2
  15.     If Text(1) <> "" And Text(2) <> "" Then
  16.         If d Is Nothing Then item_qty
  17.         If d.EXISTS(Text(1) & Text(2)) Then
  18.             d(Text(1) & Text(2)).Interior.ColorIndex = 44
  19.         End If
  20.     End If
  21. End Sub
  22. Private Sub item_qty()
  23.     Dim r As Range
  24.     Set d = CreateObject("scripting.dictionary") '¦r¨åª«¥ó
  25.     For Each r In UsedRange.Rows
  26.         'item(AÄd)&qty(CÄd)¤º®e¬°¦r¨åª«¥ókey­È,«ü©w¨ì Range
  27.         Set d(r.Cells(1, 1) & r.Cells(1, 3)) = r.Cells(1, 1).Resize(, 3)
  28.         
  29.     Next
  30. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁÂ2 ¦ì°ª¤âªº¦^ÂÐ ,

³o¦A½Ð±Ð_  
1.¦btextbox2 , ¿é¤J¤ñ¹ïªº¸ê®Æ¥u¯à¬° ­^¤å ,¼Æ¦r¤£¯à§@¿é¤J . ­ì¦]¬O¦p¦ó©O


ÁÂÁÂ

TOP

¤p§Ì, ­Y ±N²M³æªº itemÄæ¦ì ,§ï¥H14-16 ½X ªº¼Æ¦r , ¦btextbox2µLªk§@¤ñ¹ï

2.¬Û¦P¦btextbox2,¥Îscanner  scan¸ê®Æ¶i¤J, ¥u¦³¥X²{ÀY«eªº1½X .

³o­ì¦]¬O¦p¦ó©O.  ÁÂÁ ,¦A³Â·Ð¦U¦ì

TOP

¤p§Ì¦Aªþ¤WÀÉ®× ,

¦bitemÄæ¦ì ,¦p§ï¥H ¼Æ¦r ®æ¦¡ªº °ê»Ú±ø½X.

1.µLªk§@¤ñ¹ï ,

2.¬Û¦P¦btextbox2,¥Hscan ¤è¦¡¥u¦³ÀY1½X

test-2.rar (16.92 KB)

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-8-23 09:52 ½s¿è
¤p§Ì¦Aªþ¤WÀÉ®× ,
¦bitemÄæ¦ì ,¦p§ï¥H ¼Æ¦r ®æ¦¡ªº °ê»Ú±ø½X.
1.µLªk§@¤ñ¹ï ,
2.¬Û¦P¦btextbox2, ...
melvinhsu µoªí©ó 2014-8-21 09:52

°ÝÃD1 :
¨º¬O¦]¬°
Cells(lRow, 1) ¤º¬O¬O¼Æ¦r,
¦Ó TextBox2 ¤º¬O¤å¦r.

§Y«K¬Ý¨ìªº³£¬O¤@¼Ëªº¦r,
¦ý¨âªÌªº§ÎºA¤£¦P,
©Ò¥H Cells(lRow, 1)  = TextBox2  ªº¸ê®Æ¤ñ¹ï·|¤£¦¨¥\.
¥u­n¦b Cells(lRow, 1) ¥~¥[¤W CStr () §â¥¦¥ýÂন¤å¦r´N¥i¥H¤ñ¹ï¦¨¥\¤F.

°ÝÃD2:
¦]¬°  Sub TextBox2_Change µ{§Ç¹ê»Ú¤W¬O¨C¦¸¤º®eµo¥ÍÅÜ°Ê(¨Ò¦p¿é¤J 1 ­Ó¦r)´N·|°õ¦æ 1 ¦¸,
©Ò¥H If Label3 = "" Then MsgBox "§ä¤£¨ì¸ê®Æ" ³o¤@¦æ°T®§·|¤@ª½¥X²{(³y¦¨ scanner  scan¸ê®Æ¿é¤J¤¤Â_, «áÄò¸ê®Æ¶i¤£¨Ó),
¥u­n®³±¼§Y¥i.

¥t, Range ªº Copy «ü¥O¨Ï¥Î®É,
­Y¥u¦³«ü©w Destination ¦ÓµL¨ä¥L¯S®í»Ý¨D,
«h Destination:= ¥i¥H¤£¥Î¦C¥X¨Ó,
¥u­n¥´ Cells(2, 8).Copy Cells(3, 8) §Y¥i.

«á¨Ó§Úµo²{ Label3 ¥X²{¤å¦r(§Y¤ñ¹ï¦¨¥\)ªº²{¶H«Ü¤£©úÅã,
©Ò¥H§Ú¥[¤W·í¨ä¤º®e¥X²{¤å¦r®É,
©³¦â·|ÅܲLºñ¦â,
¤Ï¤§´N«ì´_¦¨¥Õ¦â
³o¼Ë¤ñ¹ïµ²ªG´N·|©úÅã¦h¤F.

ºî¤W©Ò­zµ{¦¡­×§ï¦p¤U:
  1. Private Sub TextBox2_Change()
  2.    Cells(2, 8).Copy Cells(3, 8)
  3.   
  4.   Dim lRow&
  5.   Dim dItm
  6.   
  7.   Label3 = ""
  8.   lRow = 2
  9.   Do While Cells(lRow, 1) <> ""
  10.    If CStr(Cells(lRow, 1)) = TextBox2 Then
  11.      With Label3
  12.        .Caption = Cells(lRow, 3)
  13.        .BackColor = &HC0FFC0
  14.      End With
  15.      Set rTar = Cells(lRow, 1)
  16.      Exit Do
  17.    Else
  18.      Label3.BackColor = &H80000005
  19.    End If
  20.    lRow = lRow + 1
  21.   Loop
  22. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-23 10:41 ½s¿è
¥t, Range ªº Copy «ü¥O¨Ï¥Î®É,
­Y¥u¦³«ü©w Destination ¦ÓµL¨ä¥L¯S®í»Ý¨D,
«h Destination:= ¥i¥H¤£¥Î¦C¥X¨Ó,
¥u­n¥´ Cells(2, 8).Copy Cells(3, 8) §Y¥i.
luhpro µoªí©ó 2014/8/23 09:25


¬O¥i¤£«ü©ú²Ä¤@­Ó°Ñ¼Æªº¦WºÙ

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
¤£«ü©ú»Ý   , , , expression.Find( "TEST", , , , , , ,  ,  TRUE)
«ü©ú¥i¬Ù²¤ , , , expression.Find( "TEST",  SearchFormat:=True)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁÂ2 ¦ìªº«ü¾É, ¤Î ¨ó§U, ¤S¾Ç¨ì¤F ¤@½Ò,

¦A¦¸·PÁÂ

TOP

¦^´_ 7# luhpro


    À´¤F,,¯uªº·PÁÂ

TOP

        ÀR«ä¦Û¦b : ¡i°µ¤Hªº¶}©l¡j¨C¤@¤Ñ³£¬O¬G¤Hªº¶}©l¡A¨C¤@­Ó®É¨è³£¬O¦Û¤vªºÄµ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD