Named Ranges

[Guest]

I want to pick up row 2- column 2 as ACAPSBranchList and row 1 column 1 as ACAPSNoteList. I wrote the following code for the acapsnotelist and it worked so I just added some code for the second named range but it picks up column 1 & 2 for instead of just column 2. What am I doing wrong? Can I have two named ranges in the same code or do I need to write a whole new sub? Please help!

Dim StrGivenRange As String
Dim strTopLeftCorner As String
Dim strBottomRightCorner As String
Dim strSheetName As String

StrGivenRange = ""
strTopLeftCorner = ""
strBottomRightCorner = ""

Application.Worksheets("ACAPS").Activate
Range("A65536").Activate
Selection.End(xlUp).Activate

strBottomRightCorner = ActiveCell.Address(, , xlR1C1)
StrGivenRange = "=ACAPS!R2C1:" & strBottomRightCorner
ActiveWorkbook.Names.Add Name:="ACAPSNoteList", RefersToR1C1:=StrGivenRange
strBottomRightCorner = ActiveCell.Address(, , xlR1C1)
StrGivenRange = "=ACAPS!R2C2:" & strBottomRightCorner
ActiveWorkbook.Names.Add Name:="ACAPSBranchList", RefersToR1C1:=StrGivenRange

End Sub

 

[Guest]

Hi Marie,

When the line
strBottomRightCorner = ActiveCell.Address(, , xlR1C1)
since Activecell is a cell in column A strBottomRightCorner returns an
address in column A.
Instead, try:
strBottomRightCorner = ActiveCell.Offset(0,1).Address(, , xlR1C1)
This should return the address directly on the right of the active cell,
therefore in column B.

Regards,
Sebastien

 

[Guest]

Sebastien,
Thanks, it worked!

Hi Marie,

When the line
strBottomRightCorner = ActiveCell.Address(, , xlR1C1)
since Activecell is a cell in column A strBottomRightCorner returns an
address in column A.
Instead, try:
strBottomRightCorner = ActiveCell.Offset(0,1).Address(, , xlR1C1)
This should return the address directly on the right of the active cell,
therefore in column B.

Regards,
Sebastien