Posts by jamesparker_1


    Why is the below code not valid? I am using a userform with a cancel button and i cant it to copy and paste some lines on sheet Chart_Data as it unloads. But i just get the error

    "Runtime Error '1004': Method 'Select' of object '_Worksheet' Failed"



    Re: Blank First Value On Table Included On Graph

    Hi Guys!

    Problem Solved! I was so confused why i was having such a problem so i Had a good look at the data and it seems that I'd been sent information with a single space in what appeared to be blank cells which was the cause of the problem!



    Re: Blank First Value On Table Included On Graph

    No i appreciate the help, To behonest my code usually looks similar to someone using an axe to crack open an egg if you know what i mean.

    I'll have a go at intergrating that into my program and see if works. It seems a weird problem to have though cause if the cell is blank then the cell is blank, full stop! I dont know why Excel isnt recognising that!

    Thanks again for your help!!

    Re: Blank First Value On Table Included On Graph

    Hi KJ!

    That's great! I knew there was an option. It's weird tho! Although i have the option set up as 'Plot nothing'. Excel still plots it as zero when I create the graph and i have to go through and press 'Delete' on every blank cell to make the graph plot nothing. I cant understand why i would need to do that when the cells are already empty!!

    Just Used you VBA code and Yeah, i think that is what i need too except for the same problem as above. It seems really odd to me!!!

    Any ideas?


    This is something i'm trying to do in VBA but I would also love to know how to fix this problem when using excel without using VBA.

    Basically, If you have a data table of January to December and January's entry is blank. When you produce a line graph, the line starts from zero and goes up to the value for february. Is there a way to tell excell to ignore January if it's blank and start at february rather than changing the Source range which i can't do for my VBA code that i've written.




    I'm using the below VBA code:

    Dim c, DataRow
            With Data
                Set c = .Range("A5:A350").Find(KPI, LookIn:=xlValues)
                If Not c Is Nothing Then
                            DataRow = c.Row
                End If
            End With

    Now, my problem is that If KPI is for example = "Favourite Hotel" and if i have data that looks like :

    Favourite Hotel - London
    Favourite Hotel - Cardiff
    Favourite Hotel
    Favourite Hotel - Birmingham

    Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).

    How do i make it search for an exact match?


    Re: Defining Variable Data Row For Chart Series

    Great! Just tried it and It worked! Might keep with the subroutine for this one as I can then use that in some other worksheets but it is interesting to know what the solution is!

    Thanks again for your time, help and effort!


    Re: Defining Variable Data Row For Chart Series

    Ok cool, I appreciate that as I too would love to learn what is going on.

    I've attached the file below. Sheet Q4 is the one i have the Private code attached to. Basically, a user would double click on an event (in this case Event 1) the macro then searches for the Area and the Event in the data sheet (i did this be concatinating Area and Event on both sheets and searching for that value). When it finds it it copies the line to Chart data where it is all formated ready for the graph to be produced. I hope that is a clear enough explaination.

    Re: Defining Variable Data Row For Chart Series

    Ok, I tried to do some testing and i foud that if I use your code in a Subroutine and call it into the main Macro then it all works perfectly. But if the routine you proposed is created in the Private Sub Worksheet_BeforeDoubleClick main code then it errors.

    It's not a problem to use the subroutine method but It does dispell the copy/format being a problem. Do you think it is a problem with having the code in a Private Sub Worksheet_BeforeDoubleClick macro?

    Re: Defining Variable Data Row For Chart Series

    Hi Dave/Andy.

    I meant it bugged out at the same point with regard to the first posting that andy supplied (see below) which he then incorporated into his further posting.

    Quote from Andy Pope


    [vba]ActiveChart.SeriesCollection(1).XValues = _
    "='Chart Data'!R1C10:" & range("'Chart Data'!J1").End(xltoright).Address( , ,xlR1C1)[/vba]

    Anyway, I have now tried Andy's code in my book1.xls and it works fine so It must be something happening due to the rest of my code.

    I'm not sure if it is useful to include the full code of my macro (Hopefully i've done something obvious) but here goes...

    Re: Defining Variable Data Row For Chart Series

    ok So the relevant code is:


    I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).

    Is this possible?

    ActiveChart.SeriesCollection(1).XValues = "='Chart Data'!R1C10:R1C21"
    ActiveChart.SeriesCollection(1).Values = "='Chart Data'!R2C10:R2C21" '

    Thanks James


    I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.

    ws.Protect Password:="PASSWORD", userinterfaceonly:=True
                ws.EnableOutlining = True

    This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons.

    Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up.

    For Reference Below is my full code:



    Re: Unhide Sheet With Index Name


    No your not being thick. Its really hard to describe somethings over forums i find. And I appreciate your help loads!

    I think we agree on the codename/index name being the thing that you cannot change, and the Sheet name being the name that u can edit on the tabs in excel.

    So yes, i want to unhide a sheet based on its codename.But...

    My sheets codenames are WF_Edin_2006,WF_Edin_2007,WF_Edin_2008 etc. Variable which the user selects called 'Year' and the user can select 2006, 2007, 2008 etc.

    In VBA, I want to concatenate the string "WF_Edin_" with the variable 'Year' to give me the codename/index name of the sheet i want to unhide.

    I then unhide the sheet based on its codename.