如何避免在VBA中将select用于可变单元格范围? [英] How to avoid using select in VBA for variable cell ranges?

问题描述

我听说过不喜欢在VBA中将.select用于excel宏，但是我想知道如果不使用它，如何可以实现我的特定目标?例如，假设有一个单元格(用作标题)，其值为商品".在此之下，所有单元都需要具有VLookup功能.但是，在宏的每次迭代中，列都会移动(随着添加新列)并添加新行(因此，新添加的行也需要添加函数).如何始终可找到该商品列并找到其最低的未填充行?使用select进行操作非常简单

I have heard of the dislike for using .select in VBA for excel macros, but I am wondering how my particular goal can be achieved without its use? For example, say there is a cell(used as a header) with the value "Commodity". Beneath it, all cells need to have a VLookup function. However, on each and every iteration of the macro, the column will shift (as new columns are added) and new rows will be added (so that newly added rows will need to have the function added as well). How is it possible to consistently locate this Commodity column and find its lowest unfilled row? It is very simple to do using select:

Do Until ActiveCell.Value = "Commodity"
Activecell.offset(0,1).select
loop
Do Until ActiveCell.Value = ""
ActiveCell.offset(1,0).select
loop

显然，我希望避免使用这种类型的语法，但是我不知道如何解决它.我所看到的关于避免选择的所有答案似乎都已设置，例如rng = Cell(x，y)之类的东西，但它们始终是已知位置的单元格.我不知道如何在不利用select检查单元格值的情况下执行此操作.

Obviously, I would prefer to avoid using this type of syntax, but I do not know how to get around it. All answers I have seen regarding the avoidance of select appear to set, for example, rng = Cell(x,y) or something, but they are always known-location cells. I do not know how to do this without utilizing select to check cell values.

推荐答案

首先找到Sting所在的列，然后计算其旁边的行，设置范围并输入公式.

First find the column that your Sting is located, then count the rows beside it, set your range and enter the formula.

Sub FindColumn()
    Dim f As Range, c As Integer
    Dim LstRw As Long, rng As Range

    Set f = Rows(1).Find(what:="Commodity", lookat:=xlWhole)

    If Not f Is Nothing Then
        c = f.Column
    Else: MsgBox "Not Found"
          Exit sub

    End If

    LstRw = Cells(Rows.Count, c - 1).End(xlUp).Row
    Set rng = Range(Cells(2, c), Cells(LstRw, c))
    rng = "My Formula"

End Sub

这篇关于如何避免在VBA中将select用于可变单元格范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！