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

VBA ·í2­Ó±ø¥ó¤@¼Ë®É¡A¦Û°Ê´M§ä¿é¤J 3 µ{¦¡­×§ï

VBA ·í2­Ó±ø¥ó¤@¼Ë®É¡A¦Û°Ê´M§ä¿é¤J 3 µ{¦¡­×§ï

°ò¥»¨Ï¥ÎUserForm1¿é¤J¸ê®Æ¨S¬Æ»ò°ÝÃD¡A¦ý¸ê®ÆÂà¼g¨ì¤u§@ªí¤Wªº«á¡A¦b¸g¹L§R°£¡A·|µLªk¦b§R°£«áÄæ¦ì¤W°µUserForm1ªº¸ê®ÆªºÂà¼g¡A·|§â¸ê®Æ°µ¸õ¨ì¤U¤@¦Cªº°Ê§@¡A½Ð¦Ñ®v­ÌÀ°¦£­×§ï¡AÁÂÁÂ!
ªþÀÉ: 20181128.rar (27.3 KB)

¦^´_ 1# man65boy
¨C·í¶}±ÒÀÉ®×·|³z¹LThisWorkbookªºOpen¨Æ¥ó°O¿ý¨â­Ó¦r¨åª«¥ó¸ê®Æ¡A¦¹®É°õ¦æ¿é¤J«ö¶s·|¥¿±`¼g¤J¥D­¶¤u§@ªí¤¤¡C
¦ý¬O·í§A¥H¤â°Ê¥h§R°£¥D­¶¤¤ªº¸ê®Æ¡AlRows&¤Î¨â­Ó¦r¨åÀÉ vDIO & vDPeo ¦¹®É¨Ã¨S¦³¦P¨B§ó·s¦¨§A§R°£«áªº¸ê®Æ¡A
¥ç§Y¥¦·|«O¦s§AÁÙ¥¼§R°£«eªº¸ê®Æ¡A³o·|ÅýlRows¡BvDIO¡BvDPeoªº¸ê®Æ¤º®e¥¢¯u¡C
¦p¦A¦¸°õ¦æ¿é¤J«ö¶s®É·|¥H¦r¨åÀɤ¤ªºÂ¸ê®Æ¨Ó¤ñ¹ï¡A¦p¦¹´N·|²£¥ÍµLªk¹w´Áªºµ²ªG¡C

§Ú¨Ã¨S¦³¥h¹ê»Ú¤F¸Ñ§Aµ{¦¡¹B§@¤è¦¡¡A¤£¹L¤j¤è¦V¨ÓÁ¿À³¥i³z¹L¤U¦C¤è¦¡¤©¥H§ïµ½¡G
1.±NThisWorkbookªºOpen¨Æ¥ó¤º©Ò¦³µ{¦¡½X¡A²¾©¹UserForm1ªºInitialize¨Æ¥óµ{§Ç¤¤¡A¦P®É±NThisWorkbook¼Ò²Õ¤ºªºOpen¨Æ¥ó¥þ³¡²MªÅ¡C
2.±NModule1¼Ò²Õ¤ºªº©Ò¦³¥þ°ì«¬¤½¥ÎÅܼƲ¾©¹UserForm1¼Ò²Õ¤ºªº¤WºÝ¡A¨Ã±N¤§«e«Å§iªºPublic§ï¬°Dim lRows&¡BDim vDIO¡BDim vDPeo¡A
¦]¬°¦¹3­ÓÅܼƪº¥Í©R¶g´Á¥u¶·¦s¦b¦¹UserForm1¼Ò²Õ¤¤§Y¥i¡A§¹¦¨«á¦A±NModule1¼Ò²Õ¤º©Ò«Å§iªº¤½¥ÎÅܼƥþ¼Æ²M°£¡C

¥H¤W²L¨£¨Ñ°Ñ

TOP

¦^´_ 2# Kubi

ÁÂÁ¤j¤jªº¼ö¤ß¦^µª¡A¤p§Ì¥\¤O©|²L¡AµLªk³z¹ý²z¸Ñ¡A§ó§ï¹L«áµ{¦¡¬OµLªk¹B¦æªº(¦³¤£¥¿½TªºÄÝ©Ê)¡A½Ð¦Ñ®v¦A¦¸À°¦£¡AÁÂÁ§A!

TOP

¦b¤u§@ªíªí®æª½±µ¿é¤J¤Î¾Þ§@¤£¬O§ó¤è«K???
¥Îªí³æı±o¤Ï¦Óê¤â~~

TOP

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

¦^·Ç¤jªº¸Ü¡Aªí³æ³Ì°ª¤@¤Ñ¦³ªñ¤dµ§ªº¸ê®Æ¡A¦³¨Ç¦­¤W¿é¤J«á¡A±ß¤W7ÂI¦h¤~¦^³ø®É¶¡µ²§ô¡A©Ò¥H¦b¸ê®Æ«Ü¦hÁÙ¨Sµ²§ô®É¡A¥H«e³£¬O¥Î"´M§ä"¥\¯à¡A·Q»¡¨Ï¥Îªí³æ¬O°t¦X¤½¥q±Ä¥Î±ø½X¾÷¿é¤J¡A©Ò¥H¨Ï¥Î¤W¡A·|¤ñ¤u§@ªí¿é¤JÁÙ­n¨Óªº§Ö¡A³o­Ó°ÝÃD¦bhttp://forum.twbts.com/thread-16895-1-1.html   VBA ·í2­Ó±ø¥ó¤@¼Ë®É¡A¦Û°Ê´M§ä¿é¤J 2  ¦³¦Ñ®v­Ì¥HÀ°¤p§Ì¸Ñ¨M¤F³o¶µªí³æªº»s§@¡A¥u¬O³Ìªñµo²{¥X¤p°ÝÃDÂI§ïµ½¡A½Ð¤j¤jÀ°¦£¡AÁÂÁÂ!

TOP

¦^´_ 3# man65boy
½Ð°Ñ¦Ò
20181128-1.rar (23.23 KB)

TOP

¦^´_ 6# Kubi

ÁÂÁÂKubi¤j¤jªº¦^ÂСA¦ý ¤UÀ¹«áµLªk¶}±Ò¡AÀÉ®×·l·´¡C¡C¡C¡C¡C

TOP

¦^´_ 7# man65boy
½Ð±N©³¤UUserForm1¼Ò²Õ¤ºªº©Ò¦³µ{¦¡½X½Æ»s¨Ã¶K¤J§AªºUserForm1¼Ò²Õ¤º

Dim lRows&
Dim vDIO, vDPeo

Private Sub CommandButton1_Click()
  Dim lRow&
  
  If TextBox1 = "" Then
    MsgBox "§A¥²¶·¿é¤J (¨®½ø½s¸¹)"
    Exit Sub
  End If
  
  If TextBox2 = "" Then
    MsgBox "§A¥²¶·¿é¤J (¥q¾÷¤H­û)"
    Exit Sub
  End If
  
  lRow = vDPeo(CStr(TextBox1 & "-" & TextBox2))
  If vDIO(CStr(TextBox1 & "-" & TextBox2)) = "O" Then ' ¦^¨Ó¤F
    Cells(lRow, 3) = TextBox2
    Cells(lRow, 5) = Now
    Cells(lRow, 6) = ComboBox1.Text
    vDIO(CStr(TextBox1 & "-" & TextBox2)) = "I"
  Else ' §Y±N¥~¥X
    lRows = lRows + 1
    Cells(lRows, 1) = TextBox1
    Cells(lRows, 2) = TextBox2
    Cells(lRows, 4) = Now
    vDIO(CStr(TextBox1 & "-" & TextBox2)) = "O"
    vDPeo(CStr(TextBox1 & "-" & TextBox2)) = lRows
  End If
End Sub

Private Sub UserForm_Initialize()
  
  Dim lRow&
  Dim sStr$
  
  Set vDIO = CreateObject("Scripting.Dictionary")
  Set vDPeo = CreateObject("Scripting.Dictionary")
  lRow = 2
  While Cells(lRow, 1) <> ""
    If Cells(lRow, 3) = "" Then
      sStr = "O"
    Else
      sStr = "I"
    End If
    vDIO(CStr(Cells(lRow, 1) & "-" & Cells(lRow, 2))) = sStr
    vDPeo(CStr(Cells(lRow, 1) & "-" & Cells(lRow, 2))) = lRow
    lRow = lRow + 1
  Wend
    lRows = lRow - 1
  
  
  ComboBox1.AddItem "NO"
  ComboBox1.AddItem "Yes"
  ComboBox1.ListIndex = 0
End Sub

¤§«á¦A±NModule1¤ÎThisWorkbook¨â­Ó¼Ò²Õ¤ºªº¸ê®Æ¥þ³¡§R°£«á°õ¦æ¬Ý¬Ý¡C

TOP

¦^´_ 8# Kubi


    ÁÂÁ¦Ѯvªº¦^µª¡Aµ{¦¡°õ¦æ«á¡Aµo²{¦³¤@­Ó°ÝÃD¡A´N¬O§â¨ä¤¤¤@¦C§R°£«á¡A¦A§Q¥Îªí³æ°õ¦æ¼g¤J®É¡A¸ê®Æ¬O·|§â¤§«e§R°£ªº¨º¤@¦C¶ñº¡¡A¦ý ¦pªGªí³æ¤@ª½¼g¤J¡Aµ{¦¡·|§â§R°£«áªº¨º¤@¦C¥H¤U¦C¦ìªº¤å¦rÂл\¹L¥h¡AÀµ½Ð¦Ñ®vÀ°¦£¸ÑÃD¡AÁÂÁ¦Ѯv¡C.
ªþÀÉ: 20181206.rar (29.75 KB)

TOP

¨®½ø½s¸¹ ¤Î ¥q¾÷¤H­û ªº²Õ¦X³£©T©w¤£­«ÂÐ???

¸Õ¸Õ~~
Dim vD As Object

Private Sub CommandButton1_Click()
Dim T$, T1$, T2$, R&
T1 = TextBox1:   T2 = TextBox2:   T = T1 & "|" & T2
If T1 = "" Then MsgBox "§A¥²¶·¿é¤J (¨®½ø½s¸¹)  ": Exit Sub
If T2 = "" Then MsgBox "§A¥²¶·¿é¤J (¥q¾÷¤H­û)  ": Exit Sub
R = Val(vD(T))
If R = 0 Then MsgBox "¨®½ø/¥q¾÷¡G" & T1 & "/" & T2 & "¡A³o²Õ¦X¤£¦s¦b¡@": Exit Sub
If R < 0 Then '¥X¨®
   Cells(-R, 3).Resize(1, 4) = ""
   Cells(-R, 4) = Now: ComboBox1.Text = ""
   vD(T) = -R
Else '¦^¨®
  If ComboBox1.Text = "" Then MsgBox "³o¬O¡i¦^¨®¡j½ë¡A(¦^¨®ª««~) ¥²¶·¿é¤J ": Exit Sub
  Cells(R, 3) = T2:   Cells(R, 5) = Now:   Cells(R, 6) = ComboBox1.Text
  vD(T) = -R
End If
End Sub

Private Sub UserForm_Initialize()
Dim T$, T1$, T2$, i&
Set vD = CreateObject("Scripting.Dictionary")
Arr = Range([F1], [A65536].End(xlUp))
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1):   T2 = Arr(i, 2):   T = T1 & "|" & T2
    If T1 = "" Or T2 = "" Then GoTo 101
    vD(T) = i
    If Arr(i, 4) = "" Or Arr(i, 5) <> "" Then vD(T) = -i
101: Next i
ComboBox1.List = Array("NO", "Yes")
End Sub

111_v1.rar (30.02 KB)

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD