- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-10-16 13:57 ½s¿è
¦^´_ 4# v03586
' Q4.¬d¸ß¥ý¶i¥ý¥X¥\¯à '¨Ï¥ÎªÌ¿ï¾Ün¬d¸ßªº¸ê®Æªí¡¨¤U©Ô¦¡¿ï³æ¡¨, ¿é¤J®Æ¸¹«á,«ö¤U¡£¬d¸ß¡¤«ö¶s,
'Listbox Åã¥Ü¸Ó®Æ¸¹ªº¡y«e¤Tµ§¶¶§Ç¡z¡yLOT¡z¡y¼h¬[¦ì¸m¡z
ªþÀɪº¬d¸ßªí³æ¼Ò²Õªºµ{¦¡- Dim Sh As Worksheet
- Private Sub UserForm_Initialize()
- ComboBox1.AddItem "¦B½c"
- ComboBox1.AddItem "¦^·Å°Ï"
- ComboBox1.AddItem "´á®ðÂd"
- End Sub
- Private Sub CommandButton1_Click()
- Unload Me
- End Sub
- Private Sub ComboBox1_Change()
- Dim E As Worksheet
- If ComboBox1.ListIndex > -1 Then
- Set Sh = Sheets("Database-" & ComboBox1)
- '**¦p¦³¿ù»~§ï¥Î¤U¦Cµ{¦¡½X : ¤u§@ªí¦WºÙªº®t²§
- 'For Each E In Sheets
- ' If InStr(E.Name, ComboBox1) Then
- ' Set Sh = E
- ' Exit For
- ' End If
- 'Next
- Ex_Ans Sh '©I¥sµ{§Ç ¶Ç»¼°Ñ¼Æ
- If Trim(TextBox2) <> "" Then TextBox2_Change
- End If
- End Sub
- Private Sub TextBox2_Change()
- Dim i As Integer, D As Object, Ar
- Set D = CreateObject("SCRIPTING.DICTIONARY")
- If Trim(TextBox2) <> "" And ComboBox1.ListIndex > -1 Then
- i = 2
- '®Æ¸¹¬O Film P/NF ? ->Sh.Cells(i, "C")
- Do While Sh.Cells(i, "a") <> ""
- If Len(Sh.Cells(i, "a")) > 1 And UCase(Sh.Cells(i, "C")) = UCase(Trim(TextBox2)) Then
- '**Len(Sh.Cells(i, "a")) > 1 ¦]¦U¤u§@ªí «e49µ§¸ê®Æ "-" ¬°¦ó??
- If Not D.exists(Trim(TextBox2)) Then
- D(Trim(TextBox2)) = Array(Array(Sh.Cells(i, "B").Text, Sh.Cells(i, "C").Text, Sh.Cells(i, "D").Text))
- Else
- Ar = D(Trim(TextBox2))
- ReDim Preserve Ar(0 To UBound(Ar) + 1)
- Ar(UBound(Ar)) = Array(Sh.Cells(i, "B").Text, Sh.Cells(i, "C").Text, Sh.Cells(i, "D").Text)
- D(Trim(TextBox2)) = Ar
- End If
- End If
- i = i + 1
- Loop
- With TextBox1 'ªþÀɪº¬d¸ßªí³æ¬O TextBox ±±¨î¶µ
- .Text = ""
- .Multi= True
- .MultiÄÝ©Ê «ü©w±±¨î¶µ¬O§_±µ¨ü¨ÃÅã¥Ü¦h¦æ¤å¦r¡C
- End With
- '*********'ªþÀɦp¦³¬O ListBox1 ±±¨î¶µ
- 'With ListBox1
- ' .ColumnCount = 3
- ' .ColumnWidths = "30,50,50"
- ' .Clear
- 'End With
- '*********************************
- If D.Count > 0 And D.exists(Trim(TextBox2)) Then
- 'ReDim Ar(0) '**ªþÀɦp¦³¬O ListBox1 ±±¨î¶µ
- For i = 0 To UBound(D(Trim(TextBox2)))
- ' ReDim Preserve Ar(0 To i) '**'ªþÀɦp¦³¬O ListBox1 ±±¨î¶µ
- TextBox1 = TextBox1 & IIf(TextBox1 <> "", vbCrLf, "") & Join(D(Trim(TextBox2))(i), ",")
- ' Ar(i) = D(Trim(TextBox2))(i) '''ªþÀɦp¦³¬O ListBox1 ±±¨î¶µ
- If i = 2 Then Exit For 'Åã¥Ü¤Tµ§
- Next
- '********'''ªþÀɦp¦³¬O ListBox1 ±±¨î¶µ
- 'If UBound(Ar) > 0 Then
- ' ListBox1.List = Application.Transpose(Application.Transpose(Ar)) ''ªþÀɦp¦³¬O ListBox1 ±±¨î¶µ
- 'Else
- ' Ar = Ar(0)
- ' With ListBox1
- ' .AddItem
- ' For i = 0 To UBound(Ar)
- ' .List(.ListCount - 1, i) = Ar(i)
- ' Next
- ' End With
- 'End If
- End If
- End If
- End Sub
½Æ»s¥N½X Q2-3. ¥t¥~¡uF¡vÄæ¦ì¬O§_¯à¤º«Ø®æ¦¡YYYY/MM/DD HH:MM ??
¦b¤u§@¤W¼g¤W Now ·|¦Û°Ê¥Í¦¨YYYY/MM/DD HH:MM ªº®æ¦¡
³oµ{¦¡½X½Ð¦Ü©ó¤@¯ë¼Ò²Õ,¥i¨Ñ¨ä¥¦µ{¦¡¨Ï¥Î- Option Explicit
- Sub Ex_Ans(Sh As Worksheet)
- Dim St As String, i(1 To 3) As Integer, D As Object, e As Variant, Rng As Range
- 'Set Sh = ¬d¸ß.Sh
- With Sh
- 'With ActiveSheet ' ¥i«ü©w¬°¡yDatabase-¦B½c¡z©Î¡yDatabase-¦^·Å°Ï¡z©Î¡yDatabase-¤J´á®ðÂd¡z
- '.Activate
- St = "½¦¯È¨ì´Á¤é"
- i(1) = Application.WorksheetFunction.Match(St, Rows(1), 0) '**¶Ç¦^½¦¯È¨ì´Á¤éªºÄæ¦ì
-
-
- .Columns(i(1)).TextToColumns Destination:=.Cells(1, i(1)), DataType:=xlDelimited, _
- FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True '***(¤å¦r®æ¦¡, Âà´«¬°¤é´Á)
-
- ' Q1. ©ñ¤J¦B½c«á¸ê®Æ·|¦s¦b¡yDatabase-¦B½c¡z¸ê®Æªí
- ' ¥i§_À°³Â¥[¤Jpºâ¡uI¡vÄæ¦ìªº¶ZÂ÷¹L´Á¤Ñ¼Æ, ¨Ì·Ó¡uG¡vÄæ¦ì¨ì´Á¤épºâ
- ' ¦A¥Ñ¡§§Ö¹L´Áªº¡¨¦b¡uJ¡vÄæ¦ì, Åã¥ÜÀu¥ý®³¨úªº¶¶§Ç
-
- ' Q2. ©ñ¤J¦^·Å°Ï«á¸ê®Æ·|¦s¦b¡yDatabase-¦^·Å°Ï¡z¸ê®Æªí
- ' ¥i§_À°³Â¥[¤Jpºâ¡uJ¡vÄæ¦ìªº¶ZÂ÷¹L´Á¤Ñ¼Æ, ¨Ì·Ó¡uF¡vÄæ¦ì»P¶ZÂ÷¥Ø«e¤é´Ápºâ
- ' ¦A¥Ñ¡§§Ö¹L´Áªº¡¨¦b¡uK¡vÄæ¦ì, Åã¥ÜÀu¥ý®³¨úªº¶¶§Ç
-
- ' Q3. ©ñ¤J´á®ðÂd«á¸ê®Æ·|¦s¦b¡yDatabase-¤J´á®ðÂd¡z¸ê®Æªí
- ' ¥i§_À°³Â¥[¤Jpºâ¡uJ¡vÄæ¦ìªº¶ZÂ÷¹L´Á¤Ñ¼Æ, ¨Ì·Ó¡uF¡vÄæ¦ì»P¶ZÂ÷¥Ø«e¤é´Ápºâ
- ' ¦A¥Ñ¡§§Ö¹L´Áªº¡¨¦b¡uK¡vÄæ¦ì, Åã¥ÜÀu¥ý®³¨úªº¶¶§Ç
-
- St = "¶ZÂ÷¹L´Á¤Ñ¼Æ"
- i(2) = Application.WorksheetFunction.Match(St, Rows(1), 0) '**¶Ç¦^¶ZÂ÷¹L´Á¤Ñ¼ÆªºÄæ¦ì
-
- With .Columns(i(2)).Range("a2:a" & .Range("a" & Rows.Count).End(xlUp).Row) '
- i(3) = i(2) - i(1)
-
- '.FormulaR1C1 = "=IF(ISNUMBER(RC[-2]),RC[-2]-TODAY(), """")"
- .FormulaR1C1 = "=IF(ISNUMBER(RC[-" & i(3) & "]),RC[-" & i(3) & "]-TODAY(), """")"
-
- '**'¶ZÂ÷¹L´Á¤Ñ¼ÆªºÄæ¦ì¼g¤W¤½¦¡
-
- .NumberFormatLocal = "G/³q¥Î®æ¦¡"
- .Value = .Value '**¤½¦¡ÂରÈ
- Set D = CreateObject("scripting.dictionary") '**¦r¨åª«¥ó
- For Each e In .Cells
- If e <> "" Then '¦³ÈªºÀx¦s®æ
- D(e.Value) = "" ' e.Value ¬°¦r¨åª«¥óªºkey
- If Rng Is Nothing Then
- Set Rng = e
- Else
- Set Rng = Union(Rng, e) 'Union ¤èªk ¶Ç¦^¨âөΦhÓ½d³òªº¦X¨Ö½d³ò¡C
- End If
- End If
- Next
- For Each e In Rng
- For i(1) = 1 To D.Count
- If e = Application.Small(D.keys, i(1)) Then
- 'e¬°¦r¨åª«¥ókeyȪº²Ä´X [i(1)] ¤pªºÈ
- e.Offset(, 1) = i(1) 'Àu¥ý®³¨ú¶¶§Ç
- Exit For
- End If
- Next
- Next
- End With
- End With
-
- '±Æ§Ç
- 'Q1-1. ¿é¤J©ñ¤J¦B½c¥\¯à, ½Ð°Ý¬O§_¥i¥H¿é¤J§¹¸ê®Æ«á¦Û°Ê¨Ì·Ó¡uC50¡vÄæ¦ì¥Dn±Æ§Ç, ¦A¥Ñ¡uJ50¡vÄæ¦ì¦¸n±Æ§Ç¶Ü??
-
- With Rng.EntireRow
- 'Q2-1. ¿é¤J¦^·Å°Ï¥\¯à, ½Ð°Ý¬O§_¥i¥H¿é¤J§¹¸ê®Æ«á¦Û°Ê¨Ì·Ó¡uC50¡vÄæ¦ì¥Dn±Æ§Ç, ¦A¥Ñ¡uJ50¡vÄæ¦ì¦¸n±Æ§Ç
- '** key1:=.Cells(1, i(2)), Order1:=1 Àu¥ý®³¨ú¶¶§Ç¬°¥D±Æ§ÇÁä
- .Sort key2:=.Cells(1, "c"), Order1:=1, key1:=.Cells(1, i(2)), Order1:=1, header:=xlNo
-
- 'Q5. ¡y¨ú¥X¦^·Å°Ï¡z¡B¡y¨ú¥X´á®ðÂd¡z¸ê®Æªíªº®³¨ú¶¶§Ç¬O§_·|¦A¦¸«·s±Æ§Ç???
- ' **«ü¸Ì ¸ê®Æªí¤º·|«·s±Æ§Ç
- End With
- End Sub
½Æ»s¥N½X |
|