Excell 2000 - Graph Problem

Hi dog42,

Is it possible to
insert a new sheet in the workbook
reorder the data like

Date       Shop1    Shop2
01/01/00   ++++     ++++
02/01/00   ++++     ++++
03/01/00   ++++     ++++

and then plot the graph
you can link the data cells of course because every other row contains a new record for both shops...

Just a thought
HTH:O)Bruintje

yeah, that would fix the problem, but unfortunately i can't change the data as you mentioned.  The data is actually is being received from an access database.  

i couldn't make a separate query for each shop due to the fact that there are actually over 20 and there are about 15 data fields.  I am using visual basic to cut down the number of shops to 2 and a specific date range so that the data for the 2 specified shops can be compared easily.

is it not possible to do this in the format the data is at the moment? - i can think of some very messy ways of doing what i want but i was wondering if this was a built in function of excel

thanks for the reply
Dog42

Unfortionatly if you cant change the data there is no WIZARD in excell that wil help you.
there is an easy way to do it an way but you have to update it everytime you change data, and thats not a sollution.
Any way good luck

(maybe you can change it from access)

thanks for the info
just out of interest, what was the easy solution

maybe the  pivottable? and indeed it's no solution for data that's frequently updated....


I'm pretty sure you could do more from Access, but i'm no expert there....

So i wrote a little VBA routine to reorder your data in a second sheet called "ShopSheet"

I've done this presuming your excel sheet with the original data is called "Shops", but you can change that in the macro to suit your needs...

it'll copy paste to get the format usable for graph drawning...

Sub DateReorder()
    Dim count, i, j As Integer
    Dim CountRange As Range
    Dim ResultSheet As Worksheet
   
    Sheets("Shops").Select
    count = Range("A1", Selection.End(xlDown)).Rows.count
   
    Set ResultSheet = Sheets.Add
    ResultSheet.Name = "Shopsheet2"
    Range("A1").Value = "date"
    Range("B1").Value = "shop1"
    Range("C1").Value = "shop2"
   
    i = 0
    j = 2
    count = 5
    For i = 2 To count
      ResultSheet.Range("A" & i).Value = Sheets("shops").Range("B" & j).Value
      ResultSheet.Range("B" & i).Value = Sheets("shops").Range("C" & j).Value
      ResultSheet.Range("C" & i).Value = Sheets("shops").Range("C" & j + 1).Value
      j = j + 2
    Next i
End Sub


HTH:O)Bruintje

oops.....

has to be

Sub DateReorder()
    Dim count, i, j As Integer
    Dim CountRange As Range
    Dim ResultSheet As Worksheet
   
    Sheets("Shops").Select
    count = Range("A1", Selection.End(xlDown)).Rows.count
   
    Set ResultSheet = Sheets.Add
    ResultSheet.Name = "Shopsheet2"
    Range("A1").Value = "date"
    Range("B1").Value = "shop1"
    Range("C1").Value = "shop2"
   
    i = 0
    j = 2
    For i = 2 To count
      ResultSheet.Range("A" & i).Value = Sheets("shops").Range("B" & j).Value
      ResultSheet.Range("B" & i).Value = Sheets("shops").Range("C" & j).Value
      ResultSheet.Range("C" & i).Value = Sheets("shops").Range("C" & j + 1).Value
      j = j + 2
    Next i
End Sub

that's a very good idea - it works fine when i set count to a specific value, but when it tries to get count from Selection.End(xlDown)).Rows.count it throws an error.  i am not used to using vb in excel, what is this code trying to do - get the maximum number of rows?

dog42

yeah exact, it get the rows from A1 to the last one in that column or at least that's what it's meant to do....

change the line into

for i=2 to count-1 and i think it's solved --> count will be 65536...

i'm still getting the same error, sorry

 run-time error: '1004'
 Method 'Range' of object '_worksheet' failed

(and the error occurs on the line that sets 'count')

Adjusted points from 100 to 250

i'm still getting an error - now it's changed to

 run-time error: '1004'
 object defined or object defined error

any idea why this is happening?

(i'm giving you the points as you've done quite a bit and i'm beginning to think i could settle with the one series)

Comment accepted as answer

wew thanks...

a workable solution could be depending on how many rows there would be in a sheet just fill that in into the count hardcoded....

which version of excel are you using?

excel 2000 from proffesional edition of office (is that version 9?)

the problem with hardcoding the 'count' variable is that the data is updated daily so it will change - the number of rows is around 40,000 at the moment but could be 41,000 in a few months

maybe it's the place wher you put the macro....

i insert a new module and just paste the code and run it with F8 stepping through the code, everything is getting filled like it should, then you could F5 and let the thing do it's work...

i ran it through Excel97 and excel2K without a problem....

so do run the code from a new module and not from the thisworksheet etc....
and be sure that the sheet is called like the one you mention in the code...

i hope you got something in this...

btw it shouldn't be a problem setting it to 65536 which is the last row of every sheet, although it got some overhead.... but i hope above gives you some direction...