vba 在Excel VBA的Range函数中传递一个变量并循环它

Passing a variable in the Range function in Excel VBA and looping it

提问by user2453309

I have a source Excel sheet called Shipper VP. There are names of three persons in the cells B4,C4,D4 of the Shipper VP and corresponding data in the columns. I am extracting their data for each person and creating separate excel tabs for each person and renaming it to their respective name. Plus there are bunch of other calculations. This is the code: (everything works)

我有一个名为 Shipper VP 的源 Excel 工作表。Shipper VP的单元格B4、C4、D4中有三个人的名字，列中有相应的数据。我正在为每个人提取他们的数据，并为每个人创建单独的 excel 选项卡并将其重命名为他们各自的名字。另外还有一堆其他的计算。这是代码：（一切正常）

Sub Test()
    Dim wsSource As Worksheet, ws As Worksheet
    Dim rg As Range, c As Range
    Dim n As Integer 

    Set wsSource = Sheets("Shipper VP")
    Set rg = wsSource.Range("B4:D4")
    n = 1
    For Each c In rg
         'add sheet and rename
        Set ws = Sheets.Add
        ws.Name = c.Text

         'other calculations
         '...

        n = n + 1
    Next c
End Sub

However, at present I know that there are three persons in the Source sheet(ws.Source), I want to loop the same thing for “n” number of persons. For each person, it starts from B4, and continues to C4, D4, E4 etc. till the number of persons in the Source Sheet. The last column is for Grand Total but I extract data only till the previous column and not for the Grand Total column.

但是，目前我知道源表（ws.Source）中有三个人，我想为“n”个人数循环同样的事情。对于每个人，从 B4 开始，一直到 C4、D4、E4 等，直到 Source Sheet 中的人数。最后一列用于总计，但我只提取前一列的数据，而不是总计列。

Now in the above code Instead of ("B4:D4") in the rg range, I want D4 cell to be a variable so that it can be anything like Z4 or AK4 etc. It there are three persons, the cell address it takes from the source sheet (wsSource) is from B4:D4, if there are 10 persons, it will be from B4:K4. If there are 100 persons again it will change to B4:x4, where x will be the (100+1)th column of the source excel sheet(wsSource).

现在在上面的代码中而不是 ("B4:D4") 在 rg 范围内，我希望 D4 单元格是一个变量，以便它可以是 Z4 或 AK4 等任何东西。它有三个人，它需要的单元格地址来自源表（wsSource）来自B4:D4，如果有10个人，它将来自B4:K4。如果再次有 100 人，它将更改为 B4:x4，其中 x 将是源 Excel 表 (wsSource) 的第 (100+1) 列。

I am not able to pass this variable to the following code: Set rg = wsSource.Range("B4:D4")

我无法将此变量传递给以下代码： Set rg = wsSource.Range("B4:D4")

So, what it should do if there are "n" persons (which I may not know the number of persons) in the source sheet, there should be a variable which checks until the cell value starting from B4 is null, then in the next code it should automatically set the range for rg = wsSource.Range(“B4:x4”), where x is the (100+1)th column.

所以，如果源表中有“n”个人（我可能不知道人数）应该怎么做，应该有一个变量来检查，直到从 B4 开始的单元格值为空，然后在下一个代码它应该自动设置 rg = wsSource.Range(“B4:x4”) 的范围，其中 x 是第 (100+1) 列。

But during searching when there are three persons, it will get the null value in the F4 column and not in E4 column of the source sheet(Shipper VP), because the name of the persons are from B4 to D4, then there is an extra Grand Total column in E4. But in the range for rg, I want only from B4 to D4 and not till the E4 column. The same is to be done for "n" persons in the Source sheet.

但是在搜索时当有3个人时，会在源表（Shipper VP）的F4列而不是E4列中得到空值，因为人名是从B4到D4，那么还有一个额外的E4 中的总计列。但是在 rg 的范围内，我只想要从 B4 到 D4 而不是直到 E4 列。对 Source 表中的“n”个人也是如此。

If B4 is also a variable, then it is much better like ‘y' or something. Then I can set ‘y' as B4 as the starting cell number. And after that it will loop from B4 to the second last column value of Row 4 in the source sheet(ws.Source), since last column value of Row 4 will be Grand Total.

如果 B4 也是一个变量，那么它就像 'y' 或其他东西要好得多。然后我可以将“y”设置为 B4 作为起始单元格编号。之后它将从 B4 循环到源表（ws.Source）中第 4 行的倒数第二列值，因为第 4 行的最后一列值将是总计。

I will be really grateful if somebody can help me out in this.

如果有人能帮助我解决这个问题，我将不胜感激。

Thanks. Regards, Dip

谢谢。问候，浸

回答by Zaider

You could modify your code to use a range based on cell numbers rather than their letter reference and grab the second last column in use with the following code.

您可以修改代码以使用基于单元格编号而不是字母引用的范围，并使用以下代码获取使用的倒数第二列。

Sub Test()
    Dim first As Long
    Dim last As Long
    Dim row As Long
    Dim wsSource As Worksheet, ws As Worksheet
    Dim rg As Range, c As Range
    Dim n As Integer 

    Set wsSource = Sheets("Shipper VP")
    first = 2
    row = 4

    With wsSource
        ' This returns the last column with data.  There could be empty cells along the way.
        'You could look into converting the column reference into the letter so that you could use a string in the range such as "B4:E4"
        last = .Cells(row, .Columns.Count).End(-4159).Column - 1 'xlToLeft
    End With

    Set rg = wsSource.Range(Cells(row, first), Cells(row, last))

    n = 1
    For Each c In rg
         'add sheet and rename
        Set ws = Sheets.Add
        ws.Name = c.Text

         'other calculations
         '...

        n = n + 1
    Next c
End Sub