如何将地址转换回VBA范围？

发布于 2025-02-01 15:14:58 字数 663 浏览 4 评论 0 原文

我有一个输入框，要求用户选择一个单元格，将其存储为一个范围。然后，此范围将转换为地址（字符串），因此！工作表也没有保存。

a for循环将循环浏览工作表，但是，我需要引用启动单元格（范围）以运行宏 - 问题是，我不能再引用启动单元格，因为这现在是字符串，而不是范围。

我需要在for循环中重新将地址（字符串）重新转换回范围。

Set myCell = Application.InputBox( _
    prompt:="Select a cell", Type:=8)
    
celladdress = myCell.Address()
    
NoSheets = Sheets.Count

For x = 2 To NoSheets

'covert address back to range

'Identify blend data
NumRows = Range(myCell, myCell.Offset(1, 0).End(xlDown)).Rows.Count
NumColumns = Range(myCell, myCell.Offset(0, 1).End(xlToRight)).Columns.Count

Set StartRange = Range(myCell, myCell.Offset(NumRows - 1, NumColumns - 1))
StartRange.Copy

原文

I have an input box that asks the user to select a cell, storing this as a range.
Then, this range will be converted to an address (string) so the !worksheet isn't also saved.

A For loop will cycle through the worksheets, however, I need to reference the start cell (range) to run a macro - the problem is, I cannot reference the start cell any longer, as this is now String, not a range.

I need to re-convert the address (string) back to a range, within the For loop.

Set myCell = Application.InputBox( _
    prompt:="Select a cell", Type:=8)
    
celladdress = myCell.Address()
    
NoSheets = Sheets.Count

For x = 2 To NoSheets

'covert address back to range

'Identify blend data
NumRows = Range(myCell, myCell.Offset(1, 0).End(xlDown)).Rows.Count
NumColumns = Range(myCell, myCell.Offset(0, 1).End(xlToRight)).Columns.Count

Set StartRange = Range(myCell, myCell.Offset(NumRows - 1, NumColumns - 1))
StartRange.Copy

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寻找一个思念的角度 2025-02-08 15:14:58

您需要一个工作表该循环中的对象引用：

For x = 2 To NoSheets
    Dim currentSheet As Worksheet
    Set currentSheet = thatWorkbook.Worksheets(x)
    ...
Next

一旦有了工作表，您仍然可以使用 mycell 在 range > Currentsheet ，不处理单元格字符串：

Set currentCell = currentSheet.Cells(myCell.Row, myCell.Column)

避免访问 range> range> 和 cells ;它们隐含地指的是 ActivesHeet ，这通常意味着仅适用于 worksheet.activate 呼叫（您通常希望避免使用 select select）和激活完全）。

You want a Worksheet object reference inside that loop:

For x = 2 To NoSheets
    Dim currentSheet As Worksheet
    Set currentSheet = thatWorkbook.Worksheets(x)
    ...
Next

Once you have the sheet, you can still use myCell to get a Range on the currentSheet, without dealing with cell address strings:

Set currentCell = currentSheet.Cells(myCell.Row, myCell.Column)

Avoid unqualified calls to Range and Cells; they're implicitly referring to the ActiveSheet, which typically means code that only works with Worksheet.Activate calls sprinkled through (you generally want to avoid having to use Select and Activate altogether).