VBA killing my named ranges

[JonR]

Hi

I am using named ranges in a spreadsheet to dynamically chart data. the
names are defined through an OFFSET function
= OFFSET(sheet2!$E$6,0,0,COUNTA($E:$E)-1,1)

The data is updated through an external ODBC query and made usable by VBA
code (below).

The problem is that whenever I run the macro, the range names are scrambled
and I get a #REF? error instead of the beginning reference cell, such as:

= OFFSET(sheet2!#REF?,0,0,COUNTA($E:$E)-1,1)

The end result if this is blank charts and question marks.

Any ideas why this is happening? Alternatively, is there a way to define a
named range in VBA so I can define the ranges at the tail end of the macro,
eliminating the problem?

TIA
Jon

Sub ChartData()

'Generates chart data
Dim inCount As Integer
Dim inX As Integer
Dim P1S1 As Integer
Dim P1S2 As Integer
Dim P1S3 As Integer
Dim P2 As Integer
Dim P3 As Integer
Dim P4 As Integer
Dim inRows As Integer

Dim ODate As Date
Dim BDate As Date
Dim EDate As Date
Dim DateStep As Date

BDate = Worksheets("Sheet2").Cells(1, 2).Value


EDate = Worksheets("Sheet2").Cells(2, 2).Value

DateStep = BDate

'MsgBox "BDate = " & BDate 'For Auditing

'MsgBox "DateStep = " & DateStep 'For Auditing

P1S1 = 0
P1S2 = 0
P1S3 = 0
P2 = 0
P3 = 0
P4 = 0


'MsgBox inRow 'For Auditing

'Clear old data

Worksheets("Sheet2").Activate
Cells(6, 4).Activate


If ActiveCell.Value <> "" Then

inrow = ActiveCell.End(xlDown).Row

Range(Cells(6, 4), Cells(inrow, 12)).Delete

End If
'-------------------------------------------------

'Populate Dates


x = 6


Do While DateStep < EDate + 7

If DateStep > EDate Then
DateStep = EDate
End If
Cells(x, 4).Select
ActiveCell.Value = DateStep - 1

Cells(x + 1, 4).Select

ActiveCell.Value = DateStep

Cells(x + 2, 4).Select
ActiveCell = DateStep + 5


DateStep = DateStep + 7


x = x + 3

Loop



'MsgBox "stop" 'for auditing



'----------------------------------------------------
'Collect Data
Cells(6, 4).Activate

inY = ActiveCell.End(xlDown).Row


For x = 6 To inY - 1 Step 3

BDate = Cells(x + 1, 4).Value
EDate = Cells(x + 3, 4).Value


Worksheets("TIVOLI DATA").Activate
Cells(1, 1).Activate

inRows = ActiveCell.End(xlDown).Row

For z = 2 To inRows

If Cells(z, 3).Value > BDate Then
If Cells(z, 3).Value < EDate Then

Select Case Cells(z, 7).Value

Case "P1/S1"
P1S1 = P1S1 + 1
Case "P1/S2"
P1S2 = P1S2 + 1
Case "P1/S3"
P1S3 = P1S3 + 1
Case "P2"
P2 = P2 + 1
Case "P3"
P3 = P3 + 1
Case "P4"
P4 = P4 + 1
End Select

End If
End If

Next z

'MsgBox "stop" 'for auditing


Worksheets("Sheet2").Activate


Cells(x, 5).Value = 0
Cells(x, 6).Value = 0
Cells(x, 7).Value = 0
Cells(x, 8).Value = 0
Cells(x, 9).Value = 0
Cells(x, 10).Value = 0
Cells(x, 11).Value = 0
Cells(x, 12).Value = 0

Cells(x + 1, 5).Value = P1S1
Cells(x + 1, 6).Value = P1S2
Cells(x + 1, 7).Value = P1S3
Cells(x + 1, 8).Value = P2
Cells(x + 1, 9).Value = P3
Cells(x + 1, 10).Value = P4

Cells(x + 1, 11).Value = Application.WorksheetFunction.Sum(P1S1, P1S2, P1S3,
P2, P3, P4)
Cells(x + 1, 12).Value = Application.WorksheetFunction.Sum(P1S1, P1S2, P1S3)

Cells(x + 2, 5).Value = P1S1
Cells(x + 2, 6).Value = P1S2
Cells(x + 2, 7).Value = P1S3
Cells(x + 2, 8).Value = P2
Cells(x + 2, 9).Value = P3
Cells(x + 2, 10).Value = P4

Cells(x + 2, 11).Value = Application.WorksheetFunction.Sum(P1S1, P1S2, P1S3,
P2, P3, P4)
Cells(x + 2, 12).Value = Application.WorksheetFunction.Sum(P1S1, P1S2, P1S3)


P1S1 = 0
P1S2 = 0
P1S3 = 0
P2 = 0
P3 = 0
P4 = 0

Next x

End Sub

 

[Don Guillett]

try this idea

= OFFSET(sheet2!$E$6,0,0,COUNTA($E:$E)-1,1)

= OFFSET(sheet2!$E$1,5,0,COUNTA($E:$E)-1,1)

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi

I am using named ranges in a spreadsheet to dynamically chart data. the
The da> names are defined through an OFFSET function
= OFFSET(sheet2!$E$6,0,0,COUNTA($E:$E)-1,1)

ta is updated through an external ODBC query and made usable by VBA

 

[Daniel.M]

Hi Jon,

You're clearing the E6 in your macro (with the delete command).

Use Range(Cells(6, 4), Cells(inrow, 12)).ClearContents ' instead

Regards,

Daniel M.

 

[JonR]

Thanks Dan & Don.

I tried both, just out of curiosity -- they both work

 

[Don Guillett]

Glad to help. Pls try to stay in the ORIGINAL thread, if possible.