VBA功能仅在可见范围内 [英] VBA function over visible range only

问题描述

我试图在自动筛选的电子表格上运行PercentRank函数，使其仅在可见的单元格上运行。我尝试定义一个范围对象，即：

  Dim x As Range 
x = ActiveSheet.Range（K1：K6027 ）.Rows.SpecialCells（x1CellTypeVisible）
  

然后尝试使用该范围的函数ie：

  PercentRank = WorksheetFunction.PercentRank（Range（x，* value to be ranking *））
 但是该代码返回编译错误：参数不可选消息。我假定是因为％的功能的语法需要两个参数来设置它所运行的值的范围，但是我不清楚概念上如何将其转换为代码。任何帮助将不胜感激。
解决方案
您需要设置 x并使用它。另外，你的括号需要改变：
  Set x = ActiveSheet.Range（K1：K6027）。Rows.SpecialCells xlCellTypeVisible）
 MyPercentRank = WorksheetFunction.PercentRank（x，*值排名*）
  
 
I'm trying to run the PercentRank function over an autofiltered spreadsheet such that it only runs over visible cells. I tried defining a range object ie:
Dim x As Range
x = ActiveSheet.Range("K1:K6027").Rows.SpecialCells(x1CellTypeVisible)
And then tried using that range the function ie:
PercentRank = WorksheetFunction.PercentRank(Range(x, *value to be ranked*) )
But that code returns a "compile error : argument not optional" message. I'm assuming it's due to the syntax of the percentrank function requiring two arguments to set yhe range of values it runs over, but I'm just not clear conceptually then on how to translate that into code. Any help would be much appreciated.
解决方案
You need to Set x and use that. Also, your brackets need shifting around:
Set x = ActiveSheet.Range("K1:K6027").Rows.SpecialCells(xlCellTypeVisible)
MyPercentRank  = WorksheetFunction.PercentRank(x, *value to be ranked*)


                        
这篇关于VBA功能仅在可见范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！