abc9gad2016

¦^´_ 2# °a¤ªºµ


   Á¤j¤j~´ú¸Õ¦¨¥\
¥t¥~·Q½Ð±Ð­Yªí®æ¤¤¦h¤F¤@¨ÇµLÃöªº¸ê°T¡A­n§ï¥ÑF7:X24³o­Ó½d³ò´M§äF6¶}©lªº¹ïÀ³­Èªº¸Ü

¤U¸ü (72.56 KB)
3

2021-1-25 16:34

À³¸Ó­n¦p¦ó­×§ïµ{¦¡©O ÁÂÁÂ

samwang

¦^´_ 10# abc9gad2016

¤£¦n·N«ä¡A¤Öºâ1Äæ¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
  Sub tt2()
Set xD = CreateObject("Scripting.Dictionary")
Sheets("´M§ä").[B2:T2] = ""
T = Sheets("´M§ä").[A2]
xD(T & "") = ""
Arr = Range([¤u§@ªí1!T2], [¤u§@ªí1!A65536].End(3))
For i = 2 To UBound(Arr)
     N = xD(Arr(i, 1) & ""): If N = 0 Then GoTo 99
     For j = 2 To 20
         If Arr(i, j) <> "" Then: M = M + 1: Arr(1, M) = Arr(1, j)
     Next
99:  Next
If M > 0 Then Sheets("´M§ä").[B2].Resize(1, M) = Arr
End Sub

°a¤ªºµ

¦^´_ 11# abc9gad2016

Public Sub ´M§ä¬Û¹ïÄæ¦ì½m²ß()
    Application.ScreenUpdating = False
    Range(Sheets(2).Cells(2, 2).End(xlToRight), Sheets(2).Cells(2, 2)).ClearContents
    Arr = Range(Sheets(1).Cells(Rows.Count, 1).End(xlUp), Sheets(1).Cells(6, 1).End(xlToRight))
    Set xD = CreateObject("Scripting.Dictionary")
   
    For Y = 1 To UBound(Arr, 2)
        xD(Arr(1, Y)) = Y
    Next Y
   
    For X = 3 To UBound(Arr, 1)
        For Y = 6 To UBound(Arr, 2)
            If Arr(X, 1) = [´M§ä!A2] And Arr(X, Y) <> "" Then
                E = E + 1
                Sheets(2).Cells(2, 1 + E) = Arr(1, xD(Arr(1, Y)))
            End If
        Next Y
    Next X
    Application.ScreenUpdating = True
End Sub

«Øij ­×§ï samwang ¤j¤jªº¦ì¸m ¤ñ¸û¦n ¥Lªº¼gªk ¤ñ§Úªº¦n«Ü¦h

abc9gad2016

¦^´_ 13# °a¤ªºµ

ÁÙ¬O¬Û·í·PÁ¤j¤j¤À¨É~

abc9gad2016

¦^´_  abc9gad2016

¤£¦n·N«ä¡A¤Öºâ1Äæ¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
  Sub tt2()
Set xD = CreateObject("Sc ...
samwang µoªí©ó 2021-1-25 17:41

    ·PÁ¤j¤j¡A¬Q±ß¥Î±zªºµ{¦¡½X«áµy°µ­×§ï¦³¦¨¥\§Ë¦¨§Ú­nªº¼Ë¤l  ÁÂÁÂ
Sub ·j´M()
'
' ·j´M ¥¨¶°
' ·j´M
''
Worksheets("´M§ä").Range("B2:V2").ClearContents
Set xD = CreateObject("Scripting.Dictionary")
Sheets("´M§ä").[F6:X6] = ""
T = Sheets("´M§ä").[A2]
xD(T & "") = ""
Arr = Range([¤u§@ªí1!X6], [¤u§@ªí1!A65536].End(3))
For i = 2 To UBound(Arr)
    N = xD(Arr(i, 1) & ""): If N = 0 Then GoTo 99
    For J = 6 To 24
        If Arr(i, J) <> "" Then: M = M + 1: Arr(1, M) = Arr(1, J)
    Next
99: Next
If M > 0 Then Sheets("´M§ä").[B2].Resize(1, M) = Arr
End Sub

abc9gad2016

¦^´_ 12# samwang


    ¤£¦n·N«ä·Q¦A½Ð±ÐS¤j¡A¦pªG¨Ì·Ó³o¥÷ªí®æ§Ú·Q·j´M½d³ò¬°J64:BR64
¤£ª¾¹D§Ú­þÃä­×§ï¿ùÁٽЫüÂI¡AµLªk°õ¦æ¥X­nªºµ²ªG ÁÂÁÂ

¤U¸ü (90.31 KB)
Q

2021-1-26 10:05

­ã´£³¡ªL

Sub FindData()
Dim xR As Range, xA As Range, xF As Range, j%
Set xR = [´M§ä!a2]
xR(1, 2).Resize(1, 200) = ""
Set xA = Sheets("¤u§@ªí1").UsedRange
Set xF = xA.Columns(1).Find(xR, Lookat:=xlWhole)
If xR = "" Or xF Is Nothing Then Exit Sub
For j = 2 To xA.Columns.Count
    If xF(1, j) <> "" Then Set xR = xR(1, 2): xR = xA(2, j)
Next j
End Sub

samwang

¦^´_ 16# abc9gad2016

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub tt3()
Set xD = CreateObject("Scripting.Dictionary")
Sheets("´M§ä").[B2].Resize(1, 200) = ""
T = Sheets("´M§ä").[A2]
xD(T & "") = ""
Arr = Range([¤u§@ªí1!BR64], [¤u§@ªí1!A65536].End(3))
For i = 2 To UBound(Arr)
     N = xD(Arr(i, 1) & ""): If N = 0 Then GoTo 99
     For j = 10 To UBound(Arr, 2)
         If Arr(i, j) <> "" Then: M = M + 1: Arr(1, M) = Arr(1, j)
     Next
99:  Next
If M > 0 Then Sheets("´M§ä").[B2].Resize(1, M) = Arr
End Sub

abc9gad2016

¦^´_ 18# samwang


·PÁ¤j¤j!´ú¸Õ­×§ï¦¨¥\¡A·Q½Ð°Ý³o¨â¬q­×§ï¤ñ¸û¦h¡A¥i¥H½Ð±Ð¬O¤°»ò·N«ä¶Ü ÁÂÁÂ
Sheets("¦¨«~").[L2].Resize(1, 200) = ""

For J = 10 To UBound(Arr, 2)

abc9gad2016

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


    ÁÂÁª©¥D!