VBA单元格地址Max（） [英] VBA Cell address of Max()

问题描述

  sdMax = WorksheetFunction.Max（Range（D2，Cells（emptyRow，4）） ）
  

查找列D的最大数量

如何找到最大数量的位置？

解决方案

在vba中定义为用户定义的函数，作为字符串地址

 函数AddressOfMax（rng As Range）As String 
 AddressOfMax = WorksheetFunction.Index（rng，WorksheetFunction .Match（WorksheetFunction.Max（rng），rng，0））。地址
 
结束函数
  

或返回范围引用

 功能AddressOfMax（rng As Range）As Range 
 Set AddressOfMax = rng.Cells（WorksheetFunction.Match（WorksheetFunction.Max（rng），rng，0））
 
结束函数
  

这些功能假定rng是一列宽的

这些功能可以在sh eet

例如

  = AddressOfMax（C：C）
  

或vba

例如

  Dim r As Range 
设置r = AddressOfMax（Range（D2，Cells（emptyRow，4）））
  

I have something like

sdMax = WorksheetFunction.Max(Range("D2", Cells(emptyRow, 4)))

to find the maximum number of column D

How do I find the location of this maximum number?

解决方案

Defined as a user defined function in vba, returning the address as a string

Function AddressOfMax(rng As Range) As String
    AddressOfMax = WorksheetFunction.Index(rng, WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0)).Address

End Function

Or returning a range reference

Function AddressOfMax(rng As Range) As Range
    Set AddressOfMax = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0))

End Function

these functions assume rng is one column wide

These functions can be used in the sheet
eg

=AddressOfMax(C:C)

or in vba
eg

Dim r As Range
Set r = AddressOfMax(Range("D2", Cells(emptyRow, 4)))

这篇关于VBA单元格地址Max（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！