宏解释 [英] Macro Explanation

问题描述

我正在研究如何在VBA的帮助下创建快捷方式。宏将基本上允许我基于单元格的值过滤范围。我在互联网上发现这个宏

  Public Sub FilterOnCellValue（）
 Dim nField As Long 
 With ActiveCell 
 nField = .Column  -  .CurrentRegion.Cells（1）.Column + 1 
 .CurrentRegion.AutoFilter字段：= nField，Criteria1：=。值
结束
 End Sub 
  

代码工作正常，但是我很难想象少数人如何创建。所以我知道到目前为止，这些家伙正在创建一个变量。但是由于某种原因，变量不是字符串格式，而是long，当我基于单元格中的文本进行过滤时，代码工作正常。我以为这个长时间只能用于数字，如果我错了，纠正我（我认为我是:)）。其次。什么与 .column - .currentregion。细胞..... + 1 ？我真的不知道他在那里做了什么。

真的很感激这里的一些帮助。该论坛迄今为止对我来说非常有用。

解决方案

了解任何事情的秘诀是将其分解成部分

无论如何，文档指出，

  Public Sub FilterOnCellValue（）
 Dim nField As Long 
 With ActiveCell 
'.Column：Column Number ie A = 1，B = 2 etc 
'.CurrentRegion.Cells（1）.Column：获取第一个单元格的列号数据区域
 nField = .Column  -  .CurrentRegion.Cells（1）.Column + 1 
'Autofilter将列号作为参数
'参见下面的示例
'nField：获取区域内的过滤器值的列号
'最左侧的字段为1，因此+1 
 .CurrentRegion.AutoFilter字段：= nField，Criteria1：=。值
结束
 End Sub 
  

，例如，如果您的数据是这种格式

  AB 
 1 ID值
 2 1苹果
 3 2橙色
 4 3香蕉
 5 4苹果
 6 5香蕉
  

让我们说你点击单元格B5（Apple）并运行宏，然后

1. 宏首先获取所选单元格的列号= 2




2. 检查当前区域（A1：B6）第一个单元格（ID） ）在步骤1 - 步骤2中计算值，以获得相对于该区域（A1：B6）选择的值所在的列），这个想法是使用.column作为参考，找出步骤区域内所选值的列数：2-1 + 1 = 2




3. 过滤列号通过fr对于所选值（Apple），om第3步（在我们的示例中为2）区域A1：B6

希望这有助于

I was looking into how to create a shortcut with the help of VBA. The macro would basically allow me to filter the range based on cell's value. I found this macro on the internet

 Public Sub FilterOnCellValue()
        Dim nField As Long
        With ActiveCell
            nField = .Column - .CurrentRegion.Cells(1).Column + 1
            .CurrentRegion.AutoFilter Field:=nField, Criteria1:=.Value
        End With
    End Sub

The code works fine, but I have a hard time trying to understant how the dude create. So what I know so far is the guys is creating a variable. but for some reason the variable is not in a string format but in "long" and the code works perfectly when I filter based on text in a cell. I thought that long can only be used for numbers, correct me if I am wrong (which i think I am:)). Secondly. whats up with .column - .currentregion. cells.....+1? I real have no idea what he did there.

Would really appreciate some help here. the forum has been very useful to me so far.

解决方案

The secret to understanding anything is to break it into parts

Anyways, the documentation of Autofilter states that

   Public Sub FilterOnCellValue()
                Dim nField As Long
                With ActiveCell
               '.Column: Column Number i.e  A=1,B=2 etc
               '.CurrentRegion.Cells(1).Column: Gets the col No of the firstcell of the data region          
               nField = .Column - .CurrentRegion.Cells(1).Column+1
               'Autofilter takes column number as the parameter 
               'see example below
               'nField: gets the col number of the filter value within the region
               ' leftmost field is 1 hence +1                  
                .CurrentRegion.AutoFilter Field:=nField, Criteria1:=.Value
                End With
            End Sub

for e.g if your data is in this format

     A      B
1    ID   Value
2    1    Apple
3    2    Orange
4    3    Banana
5    4    Apple
6    5    Banana

Lets say you click on cell B5(Apple) and run the macro then

1. The macro first gets the column number of the selected cell =2

2. checks which column the current region's (A1:B6) first cell(ID) is in =1

3. calculate value in Step 1-Step 2 to get which column the selected value lies in, with respect to the region (A1:B6) , the idea is using the .column as reference to find out the column number of selected value within the region result of the step:2-1+1=2

4. Filters the column number passed from step 3(in our example value is 2) of the region A1:B6 for the selected value(Apple)

Hope this helps

这篇关于宏解释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！