I am using the OWC and i need to build a range from another range
using a starting cell.

So i have
src.address = "A23:AA24"
dest.address = "A27"

I need to calculate the end of the range based on the dest.address
cell.
the end result would be

dest.address = "A27:AA28"

I need to do this wil Excel vba... any ideas?

thanks
TT

Try this:

Dim src As Range
Dim dest As Range

Set src = Range("A23:AA24")
Set dest = Range("A27")

Set dest = src.Offset(dest.Row - src.Row, dest.Column - src.Column)





"(E-Mail Removed)" wrote:

> I am using the OWC and i need to build a range from another range
> using a starting cell.
>
> So i have
> src.address = "A23:AA24"
> dest.address = "A27"
>
> I need to calculate the end of the range based on the dest.address
> cell.
> the end result would be
>
> dest.address = "A27:AA28"
>
> I need to do this wil Excel vba... any ideas?
>
> thanks
> TT
>
>

Set src = Range("A23:AA24")
Set dest = Range("A27")

With src
Set dest = dest.Resize(.Rows.Count, .Columns.Count)
End With

Debug.Print dest.Address ' $A$27:$AA$28

Before resizing you might also want to ensure the new range does would not
extend off the sheet.

Regards,
Peter T

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am using the OWC and i need to build a range from another range
> using a starting cell.
>
> So i have
> src.address = "A23:AA24"
> dest.address = "A27"
>
> I need to calculate the end of the range based on the dest.address
> cell.
> the end result would be
>
> dest.address = "A27:AA28"
>
> I need to do this wil Excel vba... any ideas?
>
> thanks
> TT
>

Give this a whirl...

Sub test()
Dim src As Range
Dim dest As Range

Set src = Range("A23:AA24")
Set dest = Range("A27").Resize(src.Rows.Count, src.Columns.Count)
MsgBox dest.Address
End Sub
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> I am using the OWC and i need to build a range from another range
> using a starting cell.
>
> So i have
> src.address = "A23:AA24"
> dest.address = "A27"
>
> I need to calculate the end of the range based on the dest.address
> cell.
> the end result would be
>
> dest.address = "A27:AA28"
>
> I need to do this wil Excel vba... any ideas?
>
> thanks
> TT
>
>