I am new to this site so please forgive me if this post isn't completly correct. For the past 2 weeks I have been beating my head against the wall trying to figure out what is wrong with the macro I created. The problem I am experiancing is that the range I am trying to put together does not seem to be working for some reason. My entire code is as follows:
Expand|Select|Wrap|Line Numbers
- Sub Proto1()
- '
- ' Proto1 Macro
- ' Macro recorded 8/30/2007 by aaron.verellen
- '
- '
- Dim CurrentSheet As String, ChartName As String
- Dim RowCount As Integer, ColumnCount As Integer, FirstColumn As Integer, LastColumn, _
- FirstRow As Integer, LastRow As Integer, Row As Integer, Column As Integer, _
- ProductColumn As Integer, ArrayIndex As Integer, ParameterCount As Integer, _
- ItemCount As Integer, MyIndex As Integer
- Dim UserProductFlag As Boolean, NewProductFlag As Boolean
- Dim Possibilities() As String
- Dim ProductXRange As Range, ProductYRange As Range
- 'Initalize public/global values
- LockProduct = False
- 'Identify unique products and place into an array
- ArrayIndex = 1
- ReDim Preserve Possibilities(ArrayIndex) As String
- Possibilities(ArrayIndex - 1) = "All"
- For Each wsheet In ThisWorkbook.Worksheets
- 'CurrentSheet = wsheet.Name
- 'verify the sheet is a data sheet
- If wsheet.Name Like "* Raw" Then
- 'find first and last columns
- ColumnCount = 1
- Do Until Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 1).Value) = 0 And Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 2).Value) = 0
- If Sheets(wsheet.Name).Cells(2, ColumnCount).Value Like "Product" Then
- 'construct vector of possible answers
- p = 3
- Do Until Len(Sheets(wsheet.Name).Cells(p, ColumnCount).Value) = 0
- NewProductFlag = False
- For Each Item In Possibilities
- If Item Like Sheets(wsheet.Name).Cells(p, ColumnCount).Value Then
- NewProductFlag = True
- End If
- Next
- If NewProductFlag = False Then
- ArrayIndex = ArrayIndex + 1
- ReDim Preserve Possibilities(ArrayIndex - 1) As String
- Possibilities(ArrayIndex - 1) = Sheets(wsheet.Name).Cells(p, ColumnCount).Value
- End If
- p = p + 1
- Loop
- End If
- ColumnCount = ColumnCount + 1
- Loop
- End If
- Next
- 'Cycle through charts finding coresponding worksheets
- 'x = 0
- 'p = 0
- 'For xxx = 1 To Sheets.Count
- ' If TypeName(Sheets(xxx)) = "Chart" Then
- For Each csheet In ThisWorkbook.Charts
- 'ChartName = csheet.Name
- 'get the name of the worksheet being used
- 'For x = 1 To Sheets.Count
- ' If TypeName(Sheets(x)) = "Worksheet" Then
- For Each wsheet In ThisWorkbook.Worksheets
- 'CurrentSheet = wsheet.Name
- 'verify the sheet is a data sheet
- If wsheet.Name Like "* Raw" Then
- 'find first and last columns
- ColumnCount = 1
- FirstColumn = 0
- LastColumn = 0
- Do Until Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 1).Value) = 0 And Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 2).Value) = 0
- 'first column
- If Sheets(wsheet.Name).Cells(1, ColumnCount).Value Like csheet.Name Then
- FirstColumn = ColumnCount
- 'last column
- ElseIf Len(Sheets(wsheet.Name).Cells(2, ColumnCount + 1).Value) = 0 Then
- LastColumn = ColumnCount
- End If
- If FirstColumn <> 0 And LastColumn <> 0 Then Exit Do
- ColumnCount = ColumnCount + 1
- Loop
- 'find last row
- RowCount = 2
- 'CurrentSheet = Sheets(wsheet.Name).Cells(RowCount, FirstColumn).Value
- Do Until Len(Sheets(wsheet.Name).Cells(RowCount, FirstColumn).Value) = 0
- RowCount = RowCount + 1
- Loop
- LastRow = RowCount - 1
- 'figure out product column
- ColumnCount = FirstColumn
- Do
- ColumnCount = ColumnCount + 1
- Loop Until Sheets(wsheet.Name).Cells(2, ColumnCount).Value Like "Product"
- ProductColumn = ColumnCount
- '////////////////////////////////////////////////////////////////////////////////////////////
- 'this is to be added to code to input in the product name
- 'mid(instr(1,[product string],"+"),1,len([product string])-instr(1,[product string],"+"))
- '////////////////////////////////////////////////////////////////////////////////////////////
- If Not LockProduct Then
- UserProductFlag = False
- Do
- 'get user input
- With ProductSelection.ComboBox1
- For Each Item In Possibilities
- .AddItem Item
- Next
- End With
- ProductSelection.Show
- 'make sure user input is good
- For Each Item In Possibilities
- If Item Like Criteria Then UserProductFlag = True
- Next
- Loop Until UserProductFlag = True
- End If
- 'graph
- ParameterCount = 1
- Do While (ParameterCount <= Sheets(csheet.Name).SeriesCollection.Count)
- 'select proper product ranges to graph
- ItemCount = 0
- MyIndex = LastRow
- Do
- If Criteria = "All" Then
- If ItemCount = 0 Then
- Set ProductXRange = Sheets(wsheet.Name).Cells(MyIndex, 3)
- Set ProductYRange = Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6)
- Else
- Set ProductXRange = Union(ProductXRange, Sheets(wsheet.Name).Cells(MyIndex, 3))
- Set ProductYRange = Union(ProductYRange, Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6))
- End If
- ItemCount = ItemCount + 1
- ElseIf Sheets(wsheet.Name).Cells(MyIndex, ProductColumn).Value = Criteria Then
- If ItemCount = 0 Then
- Set ProductXRange = Sheets(wsheet.Name).Cells(MyIndex, 3)
- Set ProductYRange = Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6)
- Else
- Set ProductXRange = Union(ProductXRange, Sheets(wsheet.Name).Cells(MyIndex, 3))
- Set ProductYRange = Union(ProductYRange, Sheets(wsheet.Name).Cells(MyIndex, ParameterCount + 6))
- End If
- ItemCount = ItemCount + 1
- End If
- MyIndex = MyIndex - 1
- Loop Until ItemCount = 35 Or MyIndex = 2
- 'convert to a column graph
- Sheets(csheet.Name).ChartType = xlColumnClustered
- With Sheets(csheet.Name).SeriesCollection(ParameterCount)
- .XValues = ProductXRange
- .Values = ProductYRange
- End With
- 'convert back to a line graph
- Sheets(csheet.Name).ChartType = xlLineMarkers
- ParameterCount = ParameterCount + 1
- Loop
- End If
- 'End If
- Next
- 'Next x
- 'End If
- 'Next xxx
- Next
- End Sub
I am not sure but I think the problem has to do with the actual range that I am trying to put in for the XValues. Like it doesn't exist or something. Does anyone know what is wrong?
Ok...the problem through talking with coworkers and experimenting is that I am not using a continuous range. When I manually put in ranges I can utilize non continuous ranges however when using a macro that is not so. Does anyone know of a way to get around this problem?
Thanks,
Aaron