[µo°Ý] ·j´M¥N¸¹«á¡A´M§ä¹ïÀ³¦C¼ÆªºÀx¦s®æ

abc9gad2016

·Q½Ð±Ðª©¤W«e½ú

¦p§Æ±æ¯à¦b"´M§äsheet"¤¤¡A¬d¸ß¤u§@ªí1¤¤ªº¥ô¤@­Ó¥N¸¹

¬d¸ß«á:©¹¥k·j´M¨ì¦³¼Æ¦rªºÀx¦s®æ¡A¦A¥h¹ïÀ³ªº²Ä¤G¦C¬d©ÒÄݪº½s¸¹

Á|¨Ò:´M§äGV8CX3Y00¡A©¹¥k·j´M·|µo²{¦bN¡BR¡BSÄæ¦ì¦³¼Æ¦r
¹ïÀ³¨ì©ÒÄݪº²Ä¤G¦C¬°MM¡BQQ¡BRR

·Q½Ð±Ð«e½ú¦³¨S¦³¨ç¼Æ©ÎVBA¦³¿ìªk°õ¦æ³o¼Ëªºµ²ªG(§ì¥X¬Y¥N¸¹²Ä¤G¦C¹ïÀ³ªº½s¸¹)
¦]¥N¸¹Äæ¸ò½s¸¹¦C·|¨ì¤W¤d¶µ¡A¤â°Ê¬d¸ß¤w¸g§ÖµLªk­t²ü ·PÁÂ

¨ç¼Æ°ß¤@·Q¨ìªº¥u¦³IF¡A·j´M<>""¡A¦ý§ì¨ì«á­n¦p¦ó¨Ï¤§¹ïÀ³¨ì²Ä¤T¦C
¬O¥dÃöªº¨BÆJ

·j´M½d¨Ò.rar (151.65 KB)

·j´M½d¨Ò
¤U¸ü¦¸¼Æ: 31

2021-1-22 16:58

hcm19522

(¿é¤J½s¸¹12057) googleºô§}:https://hcm19522.blogspot.com/

Andy2483

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾ÇÂǦ¹©«¾Ç²ßª«¥ó(Àx¦s®æ)ªº¨Ï¥Î,¾Ç²ß¨ì«Ü¦hª¾ÃÑ,¥H¤U¬O¤ß±oµù¸Ñ,½Ð«e½ú¦A«ü¾É

Option Explicit
Sub FindData()
Dim xR As Range, xA As Range, xF As Range, j%
'¡ô«Å§iÅܼÆ:(xR,xA,xF)¬OÀx¦s®æÅܼÆ,j¬Oµu¾ã¼Æ
Set xR = [´M§ä!a2]
'¡ô¥OxRÅܼƬO"´M§ä"ªíªº[A2]Àx¦s®æ
xR(1, 2).Resize(1, 200) = ""
'¡ô¥OxRÅܼƥk1®æÀx¦s®æÂX®i¦V¥k200®æ½d³òÀx¦s®æ­È¬OªÅ¦r¤¸
Set xA = Sheets("¤u§@ªí1").UsedRange
'¡ô¥OxAÅܼƬO "¤u§@ªí1"ªí¦³¨Ï¥ÎÀx¦s®æÂX®i³Ì¤p¤è¥¿½d³òÀx¦s®æ
Set xF = xA.Columns(1).Find(xR, Lookat:=xlWhole)
'¡ô¥OxFÅܼƬO xAÅܼƲÄ1Äæ¥H Range.Find ¤èªk¦^¶Çª«¥ó(Àx¦s®æ),
'¥ç¥HxRÅܼƴM§ä xAÅܼƲÄ1Äæ¸Ì­È¥þ¦PªºÀx¦s®æ,¦^¶Çµ¹xFÅܼÆ
If xR = "" Or xF Is Nothing Then Exit Sub
'¡ô¦pªGxRÅܼƭȬOªÅ¦r¤¸ ©ÎxF¬OµLª«¥ó? True´Nµ²§ôµ{¦¡°õ¦æ
For j = 2 To xA.Columns.Count
'¡ô³]¶¶°j°é!j±q2 ¨ì xAÅܼÆÄæ¼Æ
    If xF(1, j) <> "" Then Set xR = xR(1, 2): xR = xA(2, j)
    '¡ô¦pªGxFÅܼƲÄ1¦CjÅܼÆÄæÀx¦s®æ­È¤£¬OªÅªº?
    'True´N¥ORÅܼƬO¥k1®æÀx¦s®æ,¥OxRÅܼƬOxAÅܼƲÄ2¦CjÅܼÆÄæÀx¦s®æ­È
Next j
End Sub
¾ÇµL¤î¹Ò!
©Î³\¯u¬Û¥u¦³¤@­Ó,¦ý¬O§ä´M¯u¬Ûªº¤èªk¤£¥u¤@ºØ
ÁÂÁ«e½ú±`¥Î¤£¦Pªº¤èªk«ü¾É«á¾Ç

abc9gad2016

¦^´_  abc9gad2016


    Sheets("¦¨«~").[L2].Resize(1, 200) = ""  --> ²M°£¤W¤@¦¸¬d¸ß¸ê®Æ

For  ...
samwang µoªí©ó 2021-1-26 17:39

   ©ú¥Õ¤F¡A·PÁÂS¤j

samwang

¦^´_ 19# abc9gad2016


    Sheets("¦¨«~").[L2].Resize(1, 200) = ""  --> ²M°£¤W¤@¦¸¬d¸ß¸ê®Æ

For J = 10 To UBound(Arr, 2)
-->¦]¬°·j´M½d³ò¬°J64:BR64
³]©w½d³òArr = Range([¤u§@ªí1!BR64], [¤u§@ªí1!A65536].End(3))
UBound(Arr, 2) Arr³Ì«á¤@Äæ BR

abc9gad2016

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


    ÁÂÁª©¥D!

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)

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

­ã´£³¡ª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

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