ªð¦^¦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)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-12-7 00:04 ½s¿è

¦^´_ 1# man65boy

§Aªºµ{¦¡¼gªº¦n¤À´².......
§A­ì¥»ªºµ{¦¡¥u­n§ï¨ä¤¤¤@¦æ§Y¥i
°Q½×¦ê¦nªø......Ãi±o¬Ý¡A¦n¹³ÁÙ¦³¨ä¥L°ÝÃD
¥u¸Ñ¨M§A1¼Óªº°ÝÃD¡A¤£À°§U¨ä¥LDebug

lRows =  lRows+ 1
§ï¬°
lRows = [A1].End(4).Row + 1

20181128+.rar (26.61 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 13# ­ã´£³¡ªL
ÁÂÁ­ã¤j¦Ñ®vªºÀ°¦£¡A§¹¬üªº¤u§@ªí¡A¯u¬O¤Ó¹ê¥Î¤F¡AÁÂÁ­ã¤j¦Ñ®v¡C
­ã¤jªº¦W¨¥:
¡Õ­ã´£³¡ªL¡Öª¾ÃÑ+¡DÂI¼Æ¦³¦h¦³¤Ö¡D¤½¥J´«¤£¤F¡D·Oµ½®½¤£¤F¡Dªù²¼©è¤£¤F¦h¤F´I¤£¤F¡D¤Ö¤F¦º¤£¤F¡D¾÷¤ß°l²Ö¦ó®É¤Fª¾ÃÑ+¡DÀY»Î¦³°ª¦³§C¡D¦W¤ù¦L¤£¤F¡D©ÛµP±¾¤£¤F¡D¹Ó»x¨è¤£¤F°ª¤F¶Æ¤£¤F¡D§C¤F²Û¤£¤F¡D­W¤ß¿Ñ¨ú¦ó®É¤F ¡@¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡× ºw¤ô®ÄÀ³

TOP

¦^´_ 11# man65boy

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 '¥X¨®
   R = [A:A].Find(What:="", LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
   Cells(R, 1) = T1: Cells(R, 2) = T2:  Cells(R, 4) = Now
   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) = 0
End If
TextBox1 = "": TextBox1.SetFocus: TextBox2 = "": ComboBox1 = ""
End Sub

Private Sub UserForm_Initialize()
Dim T$, T1$, T2$, i&, Arr
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 <> "" And T2 <> "" And Arr(i, 3) = "" Then vD(T) = i
Next i
ComboBox1.List = Array("NO", "Yes")
End Sub

111_v2.rar (27.91 KB)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-12-6 17:32 ½s¿è

¦^´_ 11# man65boy


1) °²¨Ï³~¤¤§â¤u§@ªí¤ºªº¸ê®Æ¨ä¤¤¤@¦C¡A§R°£®É¡A¦A¦¸¨Ï¥Îªí³æ¿é¤J®É¡A¤]­n§â·sªº¸ê®Æ¡A¶ñ¸É¦b¨º¤@¦C
   __¬O«ü[²M°£]§a! ¦CÅܦ¨ªÅ¥Õ, ¦Ó¤£¬O¾ã¦C§R°£???
  __©Ò¥H, ·sªº¸ê®Æ­n±q¤¤¶¡¦³ªÅ¥Õªº¦CÀu¥ý¶ñ¸É, ¨S¦³¤¤¶¡ªÅ¥Õ¦C¤F, ¤~±q³Ì«á¤@¦C¤U­±ªÅ¥Õ¦C¶ñ¼g???
  __­n·s¼g¤Jªº¸ê®Æ»P³Q§R°£ªº¨S¦³¹ïÀ³Ãö«Y???
2) ¤v¦³[¦^¨®]®É¶¡ªº¦C, ¤£¦A¹ï¨ä°µ¥ô¦ó³B²z???  (ªí¥Ü¸Ó¤u§@¤w§¹¦¨)
3) ¥u¦³[¥X¨®]®É¶¡ªÌ, µ¥[¦^¨®]®É, ¥u­n¶ñ¤J__¤H­û(2)/¦^¨®®É¶¡/ª««~???
4) ·s¼W[¥X¨®]®É, ¥u¶ñ¤J__¨®½ø/¤H­û(1)/¥X¨®®É¶¡???

TOP

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


    Á­ã¤j¤jªº¦^µª¡Aµ{¦¡°õ¦æ¥u¦³´M§ä(¼g¤Jªð¦^¨®)ªº¥\¯à¡A¨S­ì¥ýªº¼g¤J(¼W¥[¨®½ø½s¸¹&¥X¨®)ªº¥\¯à¡A§Ú§â¦WºÙ§ï¤@¤U¡A·|¤ñ¸û²z¸Ñ¡A¤£µM¤p§Ì·|»~¾É¦Ñ®v­Ì¡A©ç¨Æ!
ªí³æ³]­p¥\¯à¬O¡A°t¦X¤½¥q±ø½X¾÷¡A¯à§Ö³t¼g¤J¡A´M§ä¡A­pºâ®É¶¡¡A¤u§@ªíAÄæ(¨®½ø½s¸¹)<<¬O­^¤å+¼Æ¦r¤£©T©wªº¡ABÄæ(¥X¨®¤H­û(1))<<¤]¬O¬O­^¤å+¼Æ¦r¤£©T©wªº¡ACÄæ(ªð¨®¤H­û(2))<<¤]¬O¬O­^¤å+¼Æ¦r¤£©T©wªº¡A³Ì­«­nªº¬OB.CÄæ¬Û¦P¤@¦Cªº¤H­û¸¹½X¡Aµ´¹ï¬O¤@¼Ëªº¡A·N«ä¬O¨Ï¥Îªí³æ¿é¤J¼g¤J¡A·í¨®½ø½s¸¹¿é¤J11¡A¥q¾÷¤H­û¿é¤J¤ý«Ø¥Á¡Aªí³æ¤¤ªº(¦^¨®ª««~)¦b¥X¨®¤H­û¦¨¥ß®É¡A¬OµLªk¼g¤Jªº¡A«ö½T©w¡A¸ê®Æ§¹¥þ¼g¤J¡AµM«á¡A§Ú¦A¦¸¨Ï¥Îªí³æ¿é¤J¡A·í¨®½ø½s¸¹¦A¦¸¿é¤J11¡A¥q¾÷¤H­û¦A¦¸¿é¤J¤ý«Ø¥Á®É¡Aªí³æ´N­n¥h´M§ä¤u§@ªí¸Ì¡A­þ­ÓÄæ¦ì¬O11¡A­þ­Óªð¨®¤H­û¬O¤ý«Ø¥Á¡A§ä¨ì«á¼g¤J¡Aªí³æ¤¤ªº(¦^¨®ª««~)¦bªð¨®¤H­û¦¨¥ß®É¡A¬O¥i¥H¼g¤Jªº¡A©Ò¦³ªº¸ê®Æ³£¨Ì§Ç©¹¤U¼g¤J¡A°²¨Ï³~¤¤§â¤u§@ªí¤ºªº¸ê®Æ¨ä¤¤¤@¦C¡A§R°£®É¡A¦A¦¸¨Ï¥Îªí³æ¿é¤J®É¡A¤]­n§â·sªº¸ê®Æ¡A¶ñ¸É¦b¨º¤@¦C¡A¦p¦A¦³·s¸ê®Æ¡A¤]­n©¹¤U¦C¿é¤J¡A(¦r¤Ó¦h¡A¦³ÂI¦Ç)¡A¦³³Ò¦Ñ®vÀ°¦£¡C
20181206.rar (59.55 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

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

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

¦^´_ 6# Kubi

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

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD