Defining range names

B

Barb Reinhardt

I have the following and I can't seem to figure out what I'm doing wrong.
Can someone assist?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rangename As String
Dim seriesrange As String
Dim RowCount As Integer


'If Not Intersect(Target, Range("Data_Area")) Is Nothing Then

'Count the number of data entries
RowCount = Cells(Rows.Count, "A").End(xlUp).Row
Debug.Print RowCount
If RowCount - 1 >= 40 Then

'Define P_Bar for first N points up to 30

rangename = "Init_Count"
seriesrange = RowCount - 1
Debug.Print rangename, seriesrange
ActiveWorkbook.Names.Add Name:="Init_Count", RefersTo:=RowCount - 1


rangename = "Defectives_Init"
seriesrange = "=OFFSET('Data for chart by WW'!$B$2,0,0,30)"
ActiveWorkbook.Names.Add Name:=rangename, RefersTo:=seriesrange


rangename = "Opportunities_Init"
seriesrange = "=OFFSET('Data for chart by WW'!$C$2,0,0,RowCount)"
Debug.Print rangename, seriesrange
ActiveWorkbook.Names.Add Name:=rangename, RefersTo:=seriesrange

rangename = "P_Bar_Init"
seriesrange = "=Sum(Defectives_Init)/Sum(Opportunities_Init)"
Debug.Print rangename, seriesrange
ActiveWorkbook.Names.Add Name:=rangename, RefersTo:=seriesrange

'seriesrange = "=OFFSET(Statics!R1C3,MATCH(R" & i &
"C2,Statics!C3,0)-1,0,COUNTIF(Statics!C3,R" & i & "C2),3)"
' seriesrange = "=OFFSET(Statics!R1C3,MATCH(Statics!R" & I &
"C2,Statics!C3,0)-1,0,COUNTIF(Statics!C3,Statics!R" & I & "C2),3)"
' ActiveWorkbook.Names.Add Name:=rangename, RefersTo:=seriesrange

'Define P_Bar for all points
End If


End Sub
 
J

Jim Thomlinson

Can you narrow it down a bit. Which part is not working. (RowCount should be
long not integer)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top