Referencing Dynamic Range

[Frank Kim]

Hi All,



I just joined the news group and this is my first question. Please
help...^^;



How do I reference an ActiveCell? Is it possible to use the address of an
active cell instead of specifying a cell below? Active cell for the example
below is Cells(1, 1) and I need to reference that with, for example, "c1".



Sub copyrange()

Workbooks("B.xls").Worksheets("Sheet1").Activate

Workbooks("B.xls").Worksheets("Sheet1").Cells(1, 1).Select

Set c1 = ActiveCell

Set mc1 = Workbooks("B.xls").Worksheets("Sheet1").Cells(1, 1)

MsgBox mc1.Address()


End Sub



How do I reference the range? Basically, I want to use the addresses of two
cells as a reference for a copy range. Ultimately, I am trying to
find/locate a copy range, which is dynamic.



Sub copyrange()

Workbooks("B.xls").Worksheets("Sheet1").Activate
Workbooks("B.xls").Worksheets("Sheet1").Cells(1, 1).Select
Set mc1 = Workbooks("B.xls").Worksheets("Sheet1").Cells(1, 1)
ActiveCell.Offset(2, 2).Activate
Set mc2 = Workbooks("B.xls").Worksheets("Sheet1").Cells(3, 3)

Dim Rng1 As Range, Rng2 As Range
Set Rng1 =
Workbooks("B.xls").Sheets("Sheet1").Range("mc1.Address:mc2.Address")
Set Rng2 = Workbooks("B.xls").Sheets("Sheet2").Range("A1")
Rng1.Copy Rng2
End Sub



Thank you,

Frank

 

[Barb Reinhardt]

I'm not sure I'd say what you are doing is a dynamic range, but here is some
code to do what you want.

I don't like using activate or select unless it's absolutely necessary.
This should give you some ideas of things you can do.

Sub copyrange()
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myRange As Range

Set myWB = Nothing
On Error Resume Next
Set myWB = Workbooks("B.xls")
On Error GoTo 0
If myWB Is Nothing Then
MsgBox ("The workbook you want isn't open")
End
End If

Set myWS = Nothing
On Error Resume Next
Set myWS = myWB.Worksheets("Sheet1")
On Error GoTo 0

If myWS Is Nothing Then
MsgBox ("YOur worksheet of choice doesn't exist")
End
End If

Set myRange = myWS.Cells(1, 1)


MsgBox myRange.Address
MsgBox myRange.Offset(1, 0).Address

End Sub

 

[Dave Peterson]

Using those "End" statements (not "End If"'s) is a bad idea. The End stops the
procedure, but it also clears any static/public variables.

It looks as though all of those "end"'s could be replaced with "Exit Sub" and
the code would work as intended.