[µo°Ý] Excel VBA ½Ð¯q

zz0660

¤U¸ü (77.79 KB)

2021-7-21 00:17

½Ð°Ý ¼gEXCELªºVBA ¦p¦ó§PÂ_ AAªí³æªºC1Àx¦s®æ¤º®eªº­^¤å¦r¡A¨Ó¨M©wQQªí³æ¹ïÀ³ªº¦a¤è½Æ»s¡AµM«á¶K¦bAAªí³æ C2ªº¦ì¸m?
½Ð¦U¦ì¨ó§U¡AÁÂÁÂ!

samwang

¦^´_ 1# zz0660

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, T, T1, i&, j&
T = Sheets("AA").Range("c1")
With Sheets("QQ")
    Arr = .Range("a1:n" & .[b65536].End(3).Row)
    For i = 1 To UBound(Arr) Step 9
        For j = 2 To UBound(Arr, 2) Step 8
            T1 = Arr(i, j): If T1 = "" Then GoTo 99
            If T1 = T Then
                .Cells(i, j).Offset(1).Resize(4, 5).Copy Sheets("AA").[c2]
                Exit Sub
            End If
99:     Next
    Next
End With
End Sub

ML089

¦^´_ 1# zz0660

©ñ¦b AAªí³æªº¼Ò²Õ

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$1" Then
        Set xQ = Sheets("QQ").Cells.Find(AA.[C1], LookIn:=xlValues, LookAt:=xlWhole)
        xQ(2, 1).Resize(8, 8).Copy Sheets("AA").[c2]
    End If
End Sub

zz0660

¦^´_ 2# samwang

Arr = .Range("a1:n" & .[b65536].End(3).Row)

For i = 1 To UBound(Arr) Step 9
        For j = 2 To UBound(Arr, 2) Step 8

³o´X¥y¬O¤°»ò·N«ä?

samwang

¦^´_  samwang

Arr = .Range("a1:n" & ..End(3).Row)

For i = 1 To UBound(Arr) Step 9
        Fo ...
zz0660 µoªí©ó 2021-7-21 23:24

step 9 ©M step 8 ¥i²¾°£¡A¦]¬°¬Ý§Aªº¬d¸ß¸ê®Æ¦³©T©w¤~³o¼Ë³]©w¡AÁÂÁÂ

Arr = .Range("a1:n" & ..End(3).Row)   '±N­n¬d¸ß¸ê®Æ¸Ë¤J¼Æ²Õ  
    For i = 1 To UBound(Arr) Step 9     '±q²Ä1¦C¶}©l´`Àô,¨C¦¸¸õ9¦C
        For j = 2 To UBound(Arr, 2) Step 8   '±q²Ä2Äæ¶}©l´`Àô¡A¨C¦¸¸õ8Äæ

zz0660

¦^´_ 5# samwang


    ±z¦n¡A°²³]QQ¸Ì­±ªº¼Æ¦r³£¦³¤½¦¡²Õ¦¨¡A¦ýÅã¥Ü¦bAAªº¦a¤è¡A¥u·Q­n³æ¯Âªº¼Æ¦r¡A½Ð°Ý­n¦h¥[¤°»òµ{¦¡½X¡H

For j = 2 To UBound(Arr, 2) Step 8   ³o­Ó­n«ç»ò§ï¦¨²Ä¤@Äæ¡H
ª½±µ§â2§ï¦¨1ªº¸Üµ{¦¡·|¿ù»~¡C

samwang

¦^´_ 6# zz0660


±z¦n¡A°²³]QQ¸Ì­±ªº¼Æ¦r³£¦³¤½¦¡²Õ¦¨¡A¦ýÅã¥Ü¦bAAªº¦a¤è¡A¥u·Q­n³æ¯Âªº¼Æ¦r¡A½Ð°Ý­n¦h¥[¤°»òµ{¦¡½X¡H
>> ¤£¤Ó¯à²z¸Ñ±zªº°ÝÃD¡A¥i§_´£¨Ñªþ¥ó¥[¥H»¡©ú¡AÁÂÁÂ

For j = 2 To UBound(Arr, 2) Step 8   ³o­Ó­n«ç»ò§ï¦¨²Ä¤@Äæ¡H
ª½±µ§â2§ï¦¨1ªº¸Üµ{¦¡·|¿ù»~¡C   
>> 2§ï¦¨1ªº¸Üµ{¦¡¤£·|¿ù»~¡A¥i§_´£¨ÑÀɮסAÁÂÁÂ

zz0660

¦^´_ 7# samwang

¦b¦¹´£¨ÑÀÉ®× AAQQ.rar (24.62 KB)

¤U¸ü¦¸¼Æ: 3

2021-7-23 10:03

QQ¶K¤Wªº¤º®e ¦pAA¨º¼Ë¡A¦ý¥u·QÅã¥Ü¯Â¼Æ¦r¡AÁÙ¦³AAªí³æ¦³T1~T3ªº«ö¶s¡A«öT1´N·|Åã¥ÜT1ªº¸ê®Æ¡A´N³o¼ËÅo¡A·PÁ±z¡C

samwang

¦^´_ 8# zz0660


«öT1´N·|Åã¥ÜT1ªº¸ê®Æ
>> T1¬O«ü­þ¸Ì¸ê®Æ? T2?? T3??

samwang

¦^´_ 8# zz0660

½Ð´ú¸Õ¬Ý¬Ý¡A¤U­±¬OT1¡A¦pªG­nT2®ÉT1©MT3²¾°£´N¥i¥H¡AT3¦P¤W¡AÁÂÁÂ

Sub T1()
Dim Arr, T, T1, i&, j&
T = Sheets("AA").Range("c1")
With Sheets("QQ")
    Arr = .Range("a1:h" & .[b65536].End(3).Row) 'T1   T1,T2,T3½Ð¦Û¦æ¿ï¾Ü§ó´«
    'Arr = .Range("j1:q" & .[k65536].End(3).Row) 'T2
    'Arr = .Range("s1:z" & .[t65536].End(3).Row) 'T3
    For i = 1 To UBound(Arr) Step 19
        T1 = Arr(i, 2): If T1 = "" Then GoTo 99
        If T1 = T Then
            Sheets("AA").[B3].Resize(16, 8).Value = .Cells(i, 1).Offset(1).Resize(16, 8).Value
            Exit Sub
        End If
99:  Next
End With
End Sub