Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA killing my named ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA killing my named ranges
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA killing my named ranges
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA killing my named ranges
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming | |||
named ranges, I think | Excel Programming | |||
Using Named Ranges in VB | Excel Programming |