Keith_H
11-24-2004, 04:10 PM
Hi to all on the forum.
This is the issue I am trying to resolve. I have a report (standard format, but the length varies). Firstly I want to select the active area of the spreadsheet and name it. Secondly I want to divide the active area into its consitiuent elements. Thirdly, where necessary, divide into further mini-groupings.
I've been able to get the first part done by the use of some "borrowed" code.
Sub Method1()
LastRow = Cells(65536, 1).End(xlUp).Row
LastCol = Cells(1, 10).End(xlToLeft).Column
' the report width is never more than 10 columns
MyArea = "='" & ActiveSheet.Name & "'!R1C1:R" & LastRow & "C" & LastCol
ActiveWorkbook.Names.Add Name:="TheData", RefersToR1C1:=MyArea
End Sub
I've then created a second mini-macro to define some cells
Dim TheData As Range
Cells.Find(What:="CONFIGURATION").Activate
ActiveCell.Name = "Config"
Cells.Find(What:="COMPONENT DETAIL - PURCHASE AND ONE TIME CHARGE").Activate
ActiveCell.Name = "Component_detail"
Cells.Find(What:="GROSS PROFIT - PURCHASE and ONE TIME CHARGE").Activate
ActiveCell.Name = "Gross_profit"
Cells.Find(What:="SOLUTION SUMMARY").Activate
ActiveCell.Name = "Solution_summary"
Cells.Find(What:="COMPONENT DETAIL - METRICS").Activate
ActiveCell.Offset(-4, 0).Select
ActiveCell.Name = "Last_Deleg"
'this will be the last cell in the deleg block
Cells.Find(What:="FEATURE DETAILS - PURCHASE AND ONE TIME CHARGE").Activate
ActiveCell.Offset(-4, 0).Select
ActiveCell.Name = "Last_GP"
'this will be the last cell in the GP block
'last cell in Solution block has yet to be defined
'last cell in Config block will be defined in a future macro
End Sub
'Although these have worked, I've tried to reference these individual cells.
'This is what I was trying to do yesterday, but to no avail.
Range("Gross_profit", "Last_GP").Select
GpFirstRow = Range("Gross_profit").Row
GpFirstCol= Range("Gross_profit").Column
GpLastRow = Range("Last_GP").Row
GpLastCol = Range("Last_GP").Column
GpTop = "R" & GpFirstRow & "C" & GpFirstCol
GpTail = "R" & GpLastRow & "C" & GpLastCol
GpArea = "='" & ActiveSheet.Name & "'!" & GpTop & GpTail
ActiveWorkbook.Names.Add Name:="GPData", RefersToGpTop:-GpTail
The last line is flagged as a syntax error. I am fairly new to scripting in general; it seems to be something related to the RefersTo syntax.
Please can someone advise where I am failing
:confused:
Regards
Keith
This is the issue I am trying to resolve. I have a report (standard format, but the length varies). Firstly I want to select the active area of the spreadsheet and name it. Secondly I want to divide the active area into its consitiuent elements. Thirdly, where necessary, divide into further mini-groupings.
I've been able to get the first part done by the use of some "borrowed" code.
Sub Method1()
LastRow = Cells(65536, 1).End(xlUp).Row
LastCol = Cells(1, 10).End(xlToLeft).Column
' the report width is never more than 10 columns
MyArea = "='" & ActiveSheet.Name & "'!R1C1:R" & LastRow & "C" & LastCol
ActiveWorkbook.Names.Add Name:="TheData", RefersToR1C1:=MyArea
End Sub
I've then created a second mini-macro to define some cells
Dim TheData As Range
Cells.Find(What:="CONFIGURATION").Activate
ActiveCell.Name = "Config"
Cells.Find(What:="COMPONENT DETAIL - PURCHASE AND ONE TIME CHARGE").Activate
ActiveCell.Name = "Component_detail"
Cells.Find(What:="GROSS PROFIT - PURCHASE and ONE TIME CHARGE").Activate
ActiveCell.Name = "Gross_profit"
Cells.Find(What:="SOLUTION SUMMARY").Activate
ActiveCell.Name = "Solution_summary"
Cells.Find(What:="COMPONENT DETAIL - METRICS").Activate
ActiveCell.Offset(-4, 0).Select
ActiveCell.Name = "Last_Deleg"
'this will be the last cell in the deleg block
Cells.Find(What:="FEATURE DETAILS - PURCHASE AND ONE TIME CHARGE").Activate
ActiveCell.Offset(-4, 0).Select
ActiveCell.Name = "Last_GP"
'this will be the last cell in the GP block
'last cell in Solution block has yet to be defined
'last cell in Config block will be defined in a future macro
End Sub
'Although these have worked, I've tried to reference these individual cells.
'This is what I was trying to do yesterday, but to no avail.
Range("Gross_profit", "Last_GP").Select
GpFirstRow = Range("Gross_profit").Row
GpFirstCol= Range("Gross_profit").Column
GpLastRow = Range("Last_GP").Row
GpLastCol = Range("Last_GP").Column
GpTop = "R" & GpFirstRow & "C" & GpFirstCol
GpTail = "R" & GpLastRow & "C" & GpLastCol
GpArea = "='" & ActiveSheet.Name & "'!" & GpTop & GpTail
ActiveWorkbook.Names.Add Name:="GPData", RefersToGpTop:-GpTail
The last line is flagged as a syntax error. I am fairly new to scripting in general; it seems to be something related to the RefersTo syntax.
Please can someone advise where I am failing
:confused:
Regards
Keith