[µo°Ý] §ä¥X³Ì¤j­È¹ïÀ³ªºrow, column

acdx

§Ú·Q§ä¤@­Ó½d³ò¤º³Ì¤j­È,¨Ã§ä¨ì©Ò¹ïÀ³¾î¦C/Áa¦Cªº­È
¦p½d¨Ò¤¤³Ì¤j­È¬°18.66423, ¹ïÀ³¿Å¦C­È¬°400, Áa¦C­È¬°25, §Úªºµ{¦¡¦p¤U
    Col = Range("B15").End(xlToRight).Column
    Rw = Range("B15").End(xlDown).Row
    Range("H11") = Application.Max(Range(Cells(16, 2), Cells(Rw, Col)))
    For j = 16 To Rw
          For k = 2 To Col
          MAXV = Range("H11").Value
            If Cells(j, k) = MAXV Then
              Range("I11") = Cells(j, 1).Value
              Range("J11") = Cells(15, k).Value
              GoTo 5
            End If
          Next k
    Next j
Msgbox (MAXV,  Range("I11"), Range("J11"))
§Ú§Q¥Îloop¥h§ä¹ïÀ³³Ì¤j­Èªº¾î/Áa¦C­È, ¦ý¬O·í³o­Ó½d³ò«D±`¤j®É,¥i¯à¬O1500*1500ªº°}¦C, °õ¦æ®É¶¡«h·|Åܪø, ¬O§_¦³¨ä¥L§ó¦³®Ä²v¤èªk¥i¥H°µ?

¤U¸ü (226.88 KB)

2019-2-21 10:21

Hsieh

¦^´_ 5# acdx

1. Sub ex()
   
2. Dim A As Range, Rng As Range, C As Range
   
3. Set A = Range([B16], [B16].End(xlDown))
   
4. Set Rng = Range(A, A.End(xlToRight))
   
5. Set C = Rng.Find(Application.Max(Rng))
   
6. r = Rng(1).Row
   
7. k = Rng(1).Column
   
8. MsgBox C.Offset(r - C.Row - 1, 0)
   
9. MsgBox C.Offset(, k - C.Column - 1)
   
10. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

¦^´_ 5# acdx

¥ý¸Õ¸Õ~~
Sub TEST()
Dim R&, C&, Mx, xF As Range
R = Cells(Rows.Count, 1).End(xlUp).Row
C = Cells(15, Columns.Count).End(xlToLeft).Column
With Range([B16], Cells(R, C))
     Mx = Application.Max(.Cells)
     Set xF = .Find(Mx, Lookat:=xlWhole)
     Range("I11") = Cells(xF.Row, 1).Value
     Range("J11") = Cells(15, xF.Column).Value
End With
End Sub

acdx

¦^´_ 4# ­ã´£³¡ªL
findMax.zip (95.63 KB)

¤U¸ü¦¸¼Æ: 11

2019-2-22 14:44

½d³ò¬OÅܰʪº, ¨C¦¸¹J¨ìªº½d³ò¥i¯à·|¤£¤@¼Ë

­ã´£³¡ªL

³Ì¤j¼Æ­È³Ì¦h¦³´X­Ó???
­Y¥u­n§ä¥X¤@­Ó, ¥ÎRANGE.FIND§Y¥i, ¤£¶·°j°é,
¤W¶Ç¤j²¤¼Ë¥»¸ê®ÆªºÀÉ®×, ¤~¦n³B²z~~

acdx

¦^´_ 2# stillfish00

³o¼Ë¸ò§Ú­ì¥»¤è¦¡®t¤£¦h, ·í½d³ò¤j®Éloop¶·¶]ªº¶µ¼Æ´N¦h¤F,¤£ª¾¹D¦³¨S¦³§ó§Öªº¤è¦¡?
ar = [B16].CurrentRegion.Value³o¼gªk§Ú­Ë¬O¨S·Q¹L, ¦h¾Ç¤@©Û ÁÂÁÂ

stillfish00

¦^´_ 1# acdx
¤£­n¦b¤j½d³òªºloop¤ºÀWÁc¦a¦s¨úÀx¦s®æ

1. Sub Solution()
   
2.     Dim maxv As Double, ar, headers(0 to 1)
   
3.     maxv = 0
   
4.     ar = [B16].CurrentRegion.Value
   
5.     For i = 2 To UBound(ar)
   
6.         For j = 2 To UBound(ar, 2)
   
7.             If ar(i, j) > maxv Then
   
8.                 maxv = ar(i, j)
   
9.                 headers(0) = ar(i, 1)
   
10.                 headers(1) = ar(1, j)
    
11.             End If
    
12.         Next
    
13.     Next
    
14.     'Range("H11") = maxv
    
15.     'Range("I11") = headers(0)
    
16.     'Range("J11") = headers(1)
    
17.     Debug.Print maxv, headers(0), headers(1)
    
18. End Sub

½Æ»s¥N½X