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

Issue

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

Solution

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

Answered By – Mathieu Guindon

Answer Checked By – Jay B. (AngularFixing Admin)