ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

§Ú·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°µ?
123.jpg
2019-2-21 10:21

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2019-2-21 17:42 ½s¿è

¦^´_ 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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 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¾Ç¤@©Û ÁÂÁÂ

TOP

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

TOP

¦^´_ 4# ­ã´£³¡ªL
findMax.zip (95.63 KB)
½d³ò¬OÅܰʪº, ¨C¦¸¹J¨ìªº½d³ò¥i¯à·|¤£¤@¼Ë

TOP

¦^´_ 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

TOP

¦^´_ 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²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD