Using Range(Cell1 value, Cell2 value) - VBA

I'm trying to define a range by the contents of two different cells, each containing the indirect cell addresses. I'm not sure whether it's possible, but here's an example:

Cell X100 contains value $A$1
Cell Y200 contains values $C$5

Is there any way I can use Range() and cells X100 and Y200 to arrive at Range("$A$1:$C$5")?

I've tried using Cells.Address but I can't figure out the right format for the application. Any help is appreciated!

Thanks

Edit

Thank you Tom! I have another question for you. The X100 cell is actually variable in my case, and I was using the following formula to find it:

Cells.Find("ID").Offset(1,0).Address

Is there any way to incorporate this sort of formula into the Range application? Or would it be easier to define a static cell in the spreadsheet containing this formula?

Thanks a bunch

Edit 2

Here you are! I'm dimming r and x as ranges and setting them as follows:

r = Cells.Find("ID").Offset(1,0).Address

x = Cells.Find("Description of initiative").offset(1,0).end(xldown).Offset(0,cells.Find("ID").Column-cells.Find("Description of initiative").Column).address

They're convoluted I know, but I printed them out and they are returning the right cells in the $A$1 format.

Hope this clarifies! Really appreciate your help.

Solution

Rather than working with addresses, work with Range objects.

Not sure I fully understand your setup, but something like this is maybe what you're looking for.

Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim startCell As Range
    Set startCell = ws.Cells.Find(What:="ID") '<--- you should specify the other parameters of Find

    Dim endCell As Range
    Set endCell = ws.Cells.Find(What:="Description of initiative") '<--- again, specify parameters of Find

    If startCell Is Nothing Then Exit Sub '<--- Find was unsuccessful
    If endCell Is Nothing Then Exit Sub '<--- Find was unsuccessful

    Set startCell = startCell.Offset(1, 0)

    Dim columnOffset As Long
    columnOffset = startCell.Column - endCell.Column

    Set endCell = endCell.Offset(1).End(xlDown)
    Set endCell = endCell.Offset(, columnOffset) '<--- there's a simpler way to do this, this just gets you back to startCell.Column, but preserving your logic

    Dim myRange As Range
    Set myRange = ws.Range(startCell, endCell)

End Sub

Here's the simpler way to get endCell instead of the offset.

Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim startCell As Range
    Set startCell = ws.Cells.Find(What:="ID") '<--- you should specify the other parameters of Find

    Dim endCell As Range
    Set endCell = ws.Cells.Find(What:="Description of initiative") '<--- again, specify parameters of Find

    If startCell Is Nothing Then Exit Sub '<--- Find was unsuccessful
    If endCell Is Nothing Then Exit Sub '<--- Find was unsuccessful

    Set startCell = startCell.Offset(1, 0)

    Dim lastRow As Long
    lastRow = endCell.Offset(1).End(xlDown).Row
    Set endCell = ws.Cells(lastRow, startCell.Column)

    Dim myRange As Range
    Set myRange = ws.Range(startCell, endCell)

End Sub