基于单元格值创建动态范围 [英] Creating Dynamic Range based on cell value

问题描述

我是VBA的新手，我正在尝试根据列A中包含的单元格创建动态范围。同一张表上有许多范围，每个范围的大小都会改变，因此我无法识别单元格作为一个范围的起点，因为它会改变。我有一张表2中列A的不同范围名称的列表，如果需要，我可以使用它来创建不同的范围。我在想，最好的方法是使用find，但是再一次，我对此很新。这是我到目前为止（没有多少，我知道）。任何帮助是极大的赞赏！谢谢！

I'm new to VBA and I'm trying to create dynamic ranges based on cell values contained in column A. There are many ranges on the same sheet and the size of each range will change, thus I cannot identify a cell as the starting point for a range, since it will change. I have a list of the different range names in column A of Sheet 2, which I can use to create the different ranges if needed. I'm thinking the best way to do this is using find, but again, I'm very new to this. Here's what I have so far (not much, I know). Any help is greatly appreciated! Thanks!

 Sub NameRange()

Dim rngselect As Range, FinalRange As Range, cell As Range
Dim ws As Worksheet

Set ws = Worksheets("OOE")
Set rngselect = ws.Range("$A:Z")

ws.Columns(i, 1).Find(California).Select
.Name = "Cali"

End Sub

推荐答案

我以前做过，并不像听起来那么简单。命名范围是整个工作簿的属性，因此必须是唯一的。如果您更改名称的范围（也不会通知您），则VBA不会生成错误。

I've done this before and it's not as simple as it sounds. Named ranges are properties of the entire workbook and therefore must be unique. VBA will NOT generate an error if you change the range of a name (nor will it notify you).

正如所述，
- 不选择范围没有必要。
- 由于您被限制为A列使用查找值在列B中的循环

With that being said, - don't select the range there is no need. - since you are restricted to column A use a loop in case the find value is in column B

' Worksheet with names
Dim name_ws as Worksheet
' Assuming you're worksheet with the names is in the same workbook and named "Names"
Set name_ws = ThisWorkbook.Worksheets("Names")

' Find might be better but I haven't had my coffee so you'll get the double loop
for j = 1 to name_ws.Cells.SpecialCells(xlCellTypeLastCell).Row
   for i = 1 to ws.Cells.SpecialCells(xlCellTypeLastCell).Row
      if ws.Cell(i, 1) = name_ws.Cells(j, 2) then _
         Range(ws.Cell(i,1), ws.Cell(i, 26)).Name = name_ws.Cells(j, 1)
   next i
next j

请注意：ws是您的变量，您不会在问题中指定您的命名范围的开始和结束单元格，所以我只是去了26个单元格。

Please note: ws is your variable and you don't specify the start and end cell of your Named range in the question so I just went 26 cells over.

请注意：我假设工作表的B列名称包含搜索变量，列A包含您要使用的名称

Please note: I am assuming Column B of the worksheet with the names contains the search variable and Column A contains the name you want to use

这篇关于基于单元格值创建动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！