Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Unable to sort in Excel because of merged cells

I was given a large spreadsheet (737 rows and 45 columns) to clean up before
transferring into a database file. Certain cells text, others numbers,
emails, etc. and some are color coded to identify a specific distribution
process.

Whenever I try to sort the data, the following message shows up and it won't
allow me to sort. "The operation requires the merged cells to be identically
sized"

I have spent the better part of a day trying to find the merged cells, but
have failed. Is there some way to find merged cells with the sheet without
clicking on every cell individually? Is there some other way to accomplish
the task for sorting? Thank you.


Post your answer or comment

comments powered by Disqus
I was given a large spreadsheet (737 rows and 45 columns) to clean up before
transferring into a database file. Certain cells text, others numbers,
emails, etc. and some are color coded to identify a specific distribution
process.

Whenever I try to sort the data, the following message shows up and it won't
allow me to sort. "The operation requires the merged cells to be identically
sized"

I have spent the better part of a day trying to find the merged cells, but
have failed. Is there some way to find merged cells with the sheet without
clicking on every cell individually? Is there some other way to accomplish
the task for sorting? Thank you.

how do i allow multiple lines of text in one row of merged cell? the reason why i did not merge two rows or more of cells is because i would like to allow freedom of number of rows of text.

pls tell me if u think its not possible, thx

Hi,
I want to sort in EXCEL with preference to alphabets instead of numerics. Since by default the numerics are preffered over alphabets as in order -
0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ ] ^ _ `
{ | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

But i want it in order -
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ ] ^ _ ` { | } ~ + < = >

Such that for input -
AF2
A51
AB3

I get (required output) -
AB3
AF2
A51

Instead of (by default SORT) -
A51
AB3
AF2

Hello all,

I have a group of virtically merged cells. I can't find a way of
accessing one of the inner values of this range of merged cells
(non-topleft values) to use in a formula.

As an example,

A B C
1 1 1 1
2 <2> 1 2
3 <2> 1 0
4 3 1 3

Here, A2 and A3 are merged, and C=A*B (B is all 1 for simplicity). In
this case, C3 will yield 0 because the value returned by A3 (a non-top
merged cell) yields 0. I would like C3 to also be 2, by forcing A3 to
take the value of the merged range it is a part of.

I know I can unmerge the cells, and manually enter in the values in
each of the cells (A2 and A3 in this case). But I would like to retain
the look-and-feel of a merged cell while being able to work with
formulae.

Is there any way of doing this, or is unmerging my only option?
Thanks in advance.
-Nitin

Hey All,

Once again I have encountered an issue with excel.

I am trying to reference the text in a Named Range which consists of merged cells.

=IF(Logic!E5 = 1, Logic_P1_N, "")

Logic_P1_N = Named Range of Merged Cells with Text

When I evaluate the formula the issue arises with the Logic_P1_N reference, which returns #Value!.

I think for some reason when the formula references the range of merged cells it can't decide which cell value to go with...

I really appreciate any help that could be provided! THANKS!!!

In Excel 2000, on merged cells I am using conditional formatting to apply
formatting including fill color and borders. On some cells the borders only
show on part of the merged cell. It may appears like the border is only on
the first cell of the merged cells. How do I fix?

I'm trying to figure out a formula that will count each cell in a range of
merged cells as having the same text as the 1st cell in the range.

Have a large excell file and when trying to sort the entire file w/o the
heading rows, an error comes up "this operation requires the merged cells to
be the same size". I know I can select the table less the heading rows, but
this requires an addition step and time.

Is there a way to sort, eliminating the first two rows in the sort, w/o
having to highlight the rest of the table after the headings? I've added the
first two rows to freeze to keep them on top, but this doesn't help in the
sorting.

Hi,
I am using Excel 2008, and I am trying to copy a table from Word to Excel.
The table in Word contains 3 columns and about 3000 rows. In each of the row,
the 3rd column contains a big block of text, with some newline characters in
it.
I want to port this table to excel so that I can do sort, filter and take
counts based on the different values in the first 2 columns.

My problem is that, when I copy this table and paste it in excel, the 3rd
column value actually get pasted as multiple rows, while the corresponding
entries in col1 and col2 get pasted as merged cells. Now, I cannot sort or
filter because the merged cells are of uneven sizes. If I unmerge the cells,
I do not have 1-1 mapping between the entries in col1 and col3.

Is there a way, when I paste these values in excel, the entire block of text
in Col3 get pasted in a single cell. Or is there something that I can do in
Word to remove the newline characters.

Any help will be very much appreciated.

Thanks
Jay

Have a large excell file and when trying to sort the entire file w/o the
heading rows, an error comes up "this operation requires the merged cells to
be the same size". I know I can select the table less the heading rows, but
this requires an addition step and time.

Is there a way to sort, eliminating the first two rows in the sort, w/o
having to highlight the rest of the table after the headings? I've added the
first two rows to freeze to keep them on top, but this doesn't help in the
sorting.

When I run the macro I get "Unable to set the XValues property of Series class" error.


	VB:
	
 temp() 
    ActiveChart.SeriesCollection(1).XValues = Range("F7:F12") 
    ActiveChart.SeriesCollection(2).XValues = Range("F7:F12") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The error is generated when executing the second line. I believe that this issue occurs because the macro is not able to access the second series as it contains all #N/A's. Whether or not the series contains errors, VBA should be allowed to change the chart's source data.

I have attached the workbook where we can replicate the error. Please could someone see whether its an issue with Excel?

I am trying to set the background color of cells based on their content for scheduling purposes (for example, if a coworker is taking calls the cell's content is "C" and the background is white; if the coworker is at lunch the content is "L" and the background is green). The code I've written for this is as follows:


	VB:
	
 Excel.Range) 
     
    Application.EnableEvents = False 
     
    Dim Color As Long 
     
    For Each TargetCell In Target.Cells 
         
         ' find the default color for the cell
        Color = Worksheets("Data").Range("A2").Interior.ColorIndex 
         
         ' check each cell in the Data!A3:A20 range for the correct schedule code
        For Each DataCell In Worksheets("Data").Range("A3:A20") 
            If DataCell.Value = TargetCell.Value Then 
                 ' when we find the right schedule code, grab the background color
                Color = DataCell.Interior.ColorIndex 
                Exit For 
            End If 
        Next DataCell 
         ' after we have found the right color for the cell, set the color
        TargetCell.Interior.ColorIndex = Color ' this is the line that errors
    Next TargetCell 
     
    Application.EnableEvents = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Data!A2 is formatted with the default color, and Data!A3:A20 has cells with the scheduling code and that are formatted as I need them to be. The code checks the changed cell against the list A3:A20 and once it finds the correct code it copies that ColorIndex to the TargetCell's ColorIndex. This works like a champ when I make changes to cells manually. If I change the cell from "C" to "L" it automatically changes the background to green like it should. The problem arises because I have a drop down list that I use for quickly shifting a person's schedule from the early shift to the late shift or vice versa. Whenever I use this list to change the schedule, I get an error that pops up and says "Run-time error '1004': Unable to set the ColorIndex property of the Interior class." The code that triggers the cell's contents to change is as follows:


	VB:
	
CellRange = "C" & i 
Range(CellRange).FormulaR1C1 = "C" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where i is an integer counter. Setting it to C is just one example, it does not matter what string I set the cell's formula to. I found another thread where the sheet being protected was the problem and gave the same error when trying to set the Interior.ColorIndex property, but nothing in my worksheet is protected. Again, selecting the cell and typing in a value works properly, but using VBA code to set the contents of the cell causes the run-time error. Does anyone have any ideas what might cause this to happen?

I am running Excel 2007 on Windows 7.

I have a workbook that I created in Excel 2003. It has extensive VBA code to help run it. When I switched over to Excel 2007 I encountered all sorts of code issues that I had to resolve. I have ironed them all out but ONE. HEre is my issue:

I have some cells that will not allow me to change their format. The worksheets/workbook has no protection (nor has it *ever*). The way I discovered this is my VBA code needs to change the border style on some cells. It throws an error when it cannot complete this step.

I can select the cell and change the contents. But I cannot change the formatting at all.

This issue seems to crop up while I am using the workbook. If I go back to an older version or close without saving, I can sometimes get the code with the formatting to run. But soon after it will crash. It is as if my code were locking some cells but I have *never* used any protection/locking ability in my code.

I tried running Microsoft Office Diagnostics but it ran just fine with no errors.

Here is the first few lines of one spot where the code is erroring. IT will error anytime that the code tries to change the format of the cell:

	VB:
	
 OADPrimeTargetBox_LostFocus() 
    Dim CompCol As String 
    Application.ScreenUpdating = False 
    ASeed_Values 
    GV.APrimeBoxSel = OADPrimeTargetBox.ListIndex 
    Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & AStartRow - 1).Font.ColorIndex = 1 
    Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & AStartRow - 1).Font.FontStyle = "Regular" 
    With Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row) 
        .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone 
        .Borders(xlEdgeTop).Weight = xlThin 
        .Borders(xlEdgeBottom).Weight = xlThin 
        .Borders(xlInsideHorizontal).Weight = xlThin 
        .Borders(xlEdgeRight).LineStyle = xlLineStyleNone 
        .Borders(xlInsideVertical).LineStyle = xlLineStyleNone 
    End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is the error: Run-time error '1004': Unable to set the LineStyle property of the Border class

It happens on that first line with .Borders (line 9).

I have a spreadsheet where there are multiple sections I'm trying to sort. Every section has sorted without issue except for one. I keep getting the error message that it is unable to sort merged cells. I have selected the entire section to be sorted and "unmerged" all cells but the same error message keeps coming up. I have also searched via "find" for any merged cells and there are none in this section. Any advice on how to resolve this?

When a user selects a merged cell within a named range the column width is reset to accommodate the longest string in a separate list. In order to do this the code first loops through the list to get the largest string which it places in a variable intLargestString. The code fails on the second line below with run-time error '1004' unable to set the ColumnWidth property of the Range class


	VB:
	
Application.Goto Target 
Selection.ColumnWidth = intLargestString 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas appreciated Auto Merged Post;

Solved - the worksheet was protected

Hello

I am trying to write a program to open Excel automatically. When the program opens the addins do not appear. I found this discussion (http://www.ozgrid.com/forum//archive...p?t-19093.html) which discusses the problem but unfortunately the given solution does not seem to work for me.

I would like to install the Analysis ToolPak Add-in. I can find four addins which I think refer to this namely:

ANALYS32.XLL
ATPVBAEN.xla
funcres.xla
PROCDB.XLA

I am using the following code (which I found here: http://support.microsoft.com/default...b;en-us;280290) to try and load them:


	VB:
	
 xlApp = CreateObject("excel.application") 
 
xlApp.workbooks.add 
Set oaddin_one = xlApp.Addins.Add("C:Program FilesMicrosoft OfficeOFFICE11LibraryAnalysisANALYS32.XLL",True) 
oAddin_one.Installed = True 
 
Set oaddin_two = xlApp.Addins.Add("C:Program FilesMicrosoft OfficeOFFICE11LibraryAnalysisATPVBAEN.xla",True) 
oAddin_two.Installed = True 
 
Set oaddin_three = xlApp.Addins.Add("C:Program FilesMicrosoft OfficeOFFICE11LibraryAnalysisfuncres.xla",True) 
oAddin_three.Installed = True 
 
Set oaddin_four = xlApp.Addins.Add("C:Program FilesMicrosoft OfficeOFFICE11LibraryAnalysisPROCDB.XLA",True) 
oAddin_four.Installed = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The first three seem to work but do not result in the application being opened with the addin. When I include procdb.xla I get the error message:

Run-time error '1004':
Unable to get the Add property of the AddIns class

Both discussions above reference http://support.microsoft.com/default...b;en-us;108888, but this link seems to no longer work so I don't know where to go next.

I'd really appreciate any help on this matter.

Many thanks

Jo

I am stuck on this. It ought to be a simple reason as to why this is
problematic, in that it works on smaller groups of data (say under 40
records), but doesn't on larger groups of records (40+).

I'm displaying 9 columns in a datagrid, and have a radiobutton at the top of
the page that says Web, Excel & Word. Web is selected by default. If you
click one of the other two buttons, this code is executed:

Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged

DataGrid1.AllowSorting = False

RbtnExport.Visible = False

btnReset.Visible = False

btnReports.Visible = False

Response.Charset = ""

EnableViewState = False

Dim sFile As String = Session("User") & "-Product List-" & Today()

sFile = sFile.Replace("/", "")

RbtnExport.Visible = False

Select Case RbtnExport.SelectedItem.Value

Case "Excel"

Response.ContentType = "application/vnd.x-msexcel"

Case "Word"

Response.ContentType = "application/msword"

End Select

Response.AddHeader("Content-Disposition", "Attachment; filename=" & sFile &
".xls")

btnReset.Visible = True

btnReports.Visible = True

RbtnExport.Visible = True

DataGrid1.AllowSorting = True

EnableViewState = True

End Sub

The smaller datagrids display fine, but anything more than 40 records
(approx 1K per record), doesn't work. When I save the file, it saves as
whatever.xls, and on a File/Open or a double click, it'll give me an "Unable
to read file." message and an OK box in Excel. You click on OK, and nothing
is loaded.

Small (20-30 records) files (approx 25K) work fine. I do nothing any
different in displaying a 75 record file than I do here, but Excel simply
will not open it.

I have tried several other options, looking at aspone.excel, and also trying
the render html option but that doesn't work - I get an error about a
control being outside a form or something. I even tried a component that
did all that, and all it would ever return is a blank page.

I am at the end of my rope on this one. Would appreciate any help.

SC

Hi,

I have some ranges defined in the worksheet.

I want to make ready only certain cells in the sheet.

Below is code which I am using, However it is locking all the cells in the range. I want to lock the cells upto some specific row in the range.
like till first 20 rows in the range.

Dim shtAnySheet As Worksheet
    Dim sPassword As String
    Dim r As Range
    sPassword = "well"
    Set shtAnySheet = WorkBookLock.Worksheets("Information")
    r = Range("AccountCode").Cells
    ' the range accountcode defined at workbook level
    r(20,1).Locked = True 
    shtAnySheet.Protect sPassword
I get an error message saying "Unable to set locked property of the Range Class. When I come out of debug mode it says "Cannot change the part of merged cell " , Actually the column cell is a merge of two cells...and it is bcoz of this it throwing the above error.

Can somebody tell how to set the locked property of some cells in the range where the column cell is merged cell.

Thanks

I have an Excel workbook with one sheet and an imbedded macro that loops pulling in several lists of data from another source. The data is formatted and sorted then the sheet saved and the loop moves on to the next file. This works a treat and generates 15 workbooks all saved. My next step was to add the creation of a pivot table to the loop but I encountered the error "unable to get the pivotfields property of the pivot table class".

In an attempt to isolate where the problem was I took a spreadsheet with the listed data and recorded a macro to pivot the table plain and simple. However, when I run this macro the same error occurs. If I run debug then the following line is highlghted:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Line Price"), "Count of Line Price", xlCount

I do have some basic programming skills and could write this by hand if someone can point me to a tutorial, or, this may be an obvious problem to resolve. I have searched the net and read a few articles including the KB articale on Microsfts site which has not helped me.

If anyone can help then I'd apprecite their time and patience.

Cheers

Hardhat

Hi all

I wrote some vbscript code that inserts pictures into Excel. These images resides on the network. When I run in on my laptop, it works fine. When I send it to my client, they get an error:
Microsoft Office Excel, Unable to get the Insert property of the pictures class

We both run Excel 2003. The only difference that I could spot thus far is, the client has Office SP3, I don't.

Could this be the reason? How do I get around it?

Kind regards,

smithcza

I am learning Excel as I go along. I need to transfer data from previous cells and ultimately sort two columns (name and value) by the value. The values have 2 decimal points. Only one digit will display in the cell. When I change the decimal point I get the famous "#". I understand that it is too big for the cell.

Problem- I cannot change the cell width because it messes up the previous items on the speadsheet.

Microsoft told me I will not be able to sort the items if I merge cells to accommodate all of the digits. When I try this Excel experiences a problem and shuts down on me.

For some reason when I copy the info from the previous cells the name will automatically take up multiple cells without merging, the value does not???

Is there something that I can do? Or something I shouldn't be doing! I need to see the true value of the item, not the rounded up single digit value.

Lori

I'm trying to sort a spreadsheet of names and addresses, and Excel says it can't because there are merged cells. I understand how to unmerge a selected cell, using "Alignment & Spacing" in the Formatting Palette. But I don't know where these merged cells are in the spreadsheet! How do I tell Excel to unmerge all cells? And is there a preference I can set so that Excel never again merges cells (unless I do it manually)?

Hello,
I've refrained from posting here for a couple of days because I've been trying to troubleshoot this to no avail. Here is what's going on:

1. I have a Worksheeet that is protected in ThisWorkbook as below:

	VB:
	
 Workbook_Open() 
    Sheet1.Protect Password:="password", _ 
    UserInterFaceOnly:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
2. Also in ThisWorkbook I have a BeforePrint Macro that inserts a picture (watermark), but generates the "Unable to get the insert property of the pictures class" Run-time as per the title.a. The error only appears when the worksheet is protected. If I unprotect, the macro runs perfectly.
b. I have verified and the worksheet does have the focus when running the code.Code in RED below is where the debugger points to.

	VB:
	
) 
     
    Dim strFilePath As String 
    Dim strItar As String 
    Dim strDXItar As String 
    Dim strDOItar As String 
    Dim strDX As String 
    Dim StrDO As String 
     
    [COLOR=seagreen] 'Indirect references to file path and file names in the Path worksheet[/COLOR]
    strFilePath = Sheet3.Range("A2") 
    strItar = "" & Sheet3.Range("A5") 
    strDXItar = "" & Sheet3.Range("A6") 
    strDOItar = "" & Sheet3.Range("A7") 
    strDX = "" & Sheet3.Range("A8") 
    StrDO = "" & Sheet3.Range("A9") 
     
On Error Goto ErrHandler: 
     
    [COLOR=seagreen] 'Watermark Subroutine[/COLOR]
    If Range("I14") = "Yes" Then 
        Select Case Range("J14") 
            [COLOR=seagreen] 'ITAR only[/COLOR]
        Case "", "N/A", "(Select One)" 
            [COLOR=red]                With ActiveSheet.Pictures.Insert _ 
            (strFilePath & strItar)[/COLOR] 
            .Top = Range("A1").Top 
            .Left = Range("A1").Left 
            .Width = 880 
            .Height = 680 
        End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Let me know if any more code or anything else is needed to help. Thanks in advance.

-Marcus

Thank you for taking the time to look at this.

For the last day I have been getting the following error: "Run-time error '1004': Unable to set the Xvalues property of the series class."

The archetecture of the marco is sound being that I have used this methology in another macro and it works correctly every time. The XValuesand Values range are also correct and I know this because I had the macro select the data cells and it does this correctly as well. The data for simplicity at the moment has no empty cells or abnormal data. The data cells are all filled with some kind of data. For the XValue Range the data would look something like "2070087 4/27/2007" and the Values range is filled with a number between 0 and 100. What I can't figure out is why it crashes with a valid establish range. I highlighted the lines it crashes on in read, they are near the bottom.


	VB:
	
 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 x = 1 To Sheets.Count 
        If TypeName(Sheets(x)) = "Worksheet" Then 
            CurrentSheet = Sheets(x).Name 
             
             'verify the sheet is a data sheet
            If CurrentSheet Like "* Raw" Then 
                 
                 'find first and last columns
                ColumnCount = 1 
                Do Until Len(Sheets(x).Cells(2, ColumnCount + 1).Value) = 0 And Len(Sheets(x).Cells(2, ColumnCount +
2).Value) = 0 
                    If Sheets(x).Cells(2, ColumnCount).Value Like "Product" Then 
                         
                         'construct vector of possible answers
                        p = 3 
                        Do Until Len(Sheets(x).Cells(p, ColumnCount).Value) = 0 
                            NewProductFlag = False 
                            For Each Item In Possibilities 
                                If Item Like Sheets(x).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(x).Cells(p, ColumnCount).Value 
                            End If 
                            p = p + 1 
                        Loop 
                         
                    End If 
                    ColumnCount = ColumnCount + 1 
                Loop 
            End If 
        End If 
    Next x 
     
     'Cycle through charts finding coresponding worksheets
     'x = 0
     'p = 0
    For xxx = 1 To Sheets.Count 
        If TypeName(Sheets(xxx)) = "Chart" Then 
            ChartName = Sheets(xxx).Name 
             
             'get the name of the worksheet being used
            For x = 1 To Sheets.Count 
                If TypeName(Sheets(x)) = "Worksheet" Then 
                    CurrentSheet = Sheets(x).Name 
                     
                     'verify the sheet is a data sheet
                    If CurrentSheet Like "* Raw" Then 
                         
                         'find first and last columns
                        ColumnCount = 1 
                        FirstColumn = 0 
                        LastColumn = 0 
                        Do Until Len(Sheets(x).Cells(2, ColumnCount + 1).Value) = 0 And Len(Sheets(x).Cells(2, ColumnCount +
2).Value) = 0 
                             'first column
                            If Sheets(x).Cells(1, ColumnCount).Value Like ChartName Then 
                                FirstColumn = ColumnCount 
                                 'last column
                            ElseIf Len(Sheets(x).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 
                        Do Until Len(Sheets(x).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(x).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 Until (ParameterCount = LastColumn - (FirstColumn + 6)) 
                             
                             
                             'select proper product ranges to graph
                            ItemCount = 0 
                            MyIndex = LastRow 
                            Do 
                                If Criteria = "All" Then 
                                    If ItemCount = 0 Then 
                                        Set ProductXRange = Sheets(x).Cells(MyIndex, 3) 
                                        Set ProductYRange = Sheets(x).Cells(MyIndex, ParameterCount + 6) 
                                    Else 
                                        Set ProductXRange = Union(ProductXRange, Sheets(x).Cells(MyIndex, 3)) 
                                        Set ProductYRange = Union(ProductYRange, Sheets(x).Cells(MyIndex, ParameterCount +
6)) 
                                    End If 
                                    ItemCount = ItemCount + 1 
                                ElseIf Sheets(x).Cells(MyIndex, ProductColumn).Value = Criteria Then 
                                    If ItemCount = 0 Then 
                                        Set ProductXRange = Sheets(x).Cells(MyIndex, 3) 
                                        Set ProductYRange = Sheets(x).Cells(MyIndex, ParameterCount + 6) 
                                    Else 
                                        Set ProductXRange = Union(ProductXRange, Sheets(x).Cells(MyIndex, 3)) 
                                        Set ProductYRange = Union(ProductYRange, Sheets(x).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(ChartName).ChartType = xlColumnClustered 
                             '///Verifying/Ranges//
                             'Sheets(x).Activate
                             'ProductXRange.Select
                             'ProductYRange.Select
                             'Sheets(x).Deactivate
                             '/////////////////////
                            With Sheets(ChartName).SeriesCollection(ParameterCount) 
                                [COLOR="Red"].XValues = ProductXRange[/COLOR] 
                                [COLOR="red"].Values = ProductYRange[/COLOR] 
                            End With 
                             'convert back to a line graph
                            Sheets(ChartName).ChartType = xlLineMarkers 
                            ParameterCount = ParameterCount + 1 
                        Loop 
                    End If 
                End If 
            Next x 
        End If 
    Next xxx 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Does anyone see anything that would cause this error or know of some way of fixing it?

Thank you everyone in advance.

Aaron


No luck finding an answer? You could always try Google.