How do I convert an address back to a range in VBA?-CodePudding

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

CodePudding user response：

myCell.address(,,,true) gives you the full external address of the range.

myCell.Parent.Name gives you the name of the worksheet

CodePudding user response：

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).