- ©«¤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
|
¦^´_ 10# ii31sakura - Option Explicit
- Sub Ex()
- Dim d As Object, Rng As Range, S As String
- Set d = CreateObject("scripting.dictionary")
- Set Rng = Sheets("¤ñ¹ïdata").Range("A2")
- Do While Rng <> ""
- d(Rng & Rng.Cells(1, 2) & Rng.Cells(1, 3)) = ""
- Set Rng = Rng.Cells(2, 1)
- Loop
- Set Rng = Sheets("¨Ó·½data").Range("A2")
- Do While Rng <> ""
- If d.EXISTS(Rng & Rng.Cells(1, 2) & Rng.Cells(1, 3)) Then
- If d.EXISTS("¤ñ¹ï¨ì") Then
- Set d("¤ñ¹ï¨ì") = Union(Rng.Resize(, 3), d("¤ñ¹ï¨ì"))
- Else
- Set d("¤ñ¹ï¨ì") = Rng.Resize(, 3)
- End If
-
- S = IIf(S <> "", S & vbLf, "") & Rng.Address(0, 0) & " §ä¨ì " & Rng & "-" & Rng.Cells(1, 2) & "-" & Rng.Cells(1, 3)
- End If
- Set Rng = Rng.Cells(2, 1)
- Loop
- If S <> "" Then
- d("¤ñ¹ï¨ì").Parent.Activate
- d("¤ñ¹ï¨ì").Select
- MsgBox "¨Ó·½data " & vbLf & S
- End If
- End Sub
- Sub Ex3()
- Dim Rng(1 To 2) As Range, Rng2_Address As String
- Set Rng(1) = Worksheets("¤ñ¹ïdata").Range("A2") '¤ñ¹ïdataªº²Ä¤@µ§¸ê®Æ(¤é´Á)
- Sheets("¨Ó·½data").UsedRange.Offset(1).Interior.ColorIndex = xlNone
- Do While Rng(1) <> "" '°õ¦æ¨ì±ø¥ó¤£¦¨¥ß
- With Sheets("¨Ó·½data").Range("A:A") '½d³ò:³o¤u§@ªíªºAÄæ
- Set Rng(2) = .Find(Rng(1), AFTER:=.Cells(1), LookIn:=xlFormulas) '·j´M¤é´Á:n¥Î¤½¦¡LookIn:=xlFormulas
- Do While Not Rng(2) Is Nothing '°õ¦æ¨ì±ø¥ó¤£¦¨¥ß
- If Rng2_Address = "" Then Rng2_Address = Rng(2).Address '°O¿ý²Ä¤@¦¸§ä¨ìªº¦ì¸m
- If Rng(1).Cells(1, 2) = Rng(2).Cells(1, 2) And Rng(1).Cells(1, 3) = Rng(2).Cells(1, 3) Then '
- ' Rng(1).Cells(1, 3) = Rng(2).Cells(1, 3) '¤ñ¹ïªº²Ä¤GÄæ=¨Ó·½dataªº²Ä¤GÄæ
-
- Rng(1).Cells(1, 4) = Rng(2).Row '¦¹¬q¬°§ä¸Ó¸ê®Æªºrow
- Rng(2).Resize(, 3).Interior.Color = vbYellow
-
- Exit Do
- End If
- Set Rng(2) = .FindNext(Rng(2)) 'Ä~Äò©¹¤U·j´M
- If Rng2_Address = Rng(2).Address Then '¦^¨ì²Ä¤@¦¸§ä¨ìªº¦ì¸m
- Exit Do 'Â÷¶}°j°é
- End If
- Loop
- Rng2_Address = ""
- Set Rng(1) = Rng(1).Offset(1) '¤ñ¹ïdataªº¤U¤@µ§¸ê®Æ(¤é´Á)
- End With
- Loop
- End Sub
½Æ»s¥N½X |
|