Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Complicated chart source data (xl97/WinNt4)

    I'm having real problems with a chart I'm working on. This chart is created (or at the moment, updated) when someone chooses the month and year they are interested in, when it picks up the data from the appropriate sheet in a source workbook which has a sheet for each month going back several years. I have no control over this source workbook, and the people who produce this source keep changing the layout, so sheets with data from last year have the same information as those from three years ago or last month but they're likely to be in different columns and/or on different rows. Therefore I can't set up the chart to read the data with fixed references for the series.

    The only definites are (1) the headings will be in row 5, (2) the descriptions for the data I want will be the same and (3) they will be in column A .

    So what I need to do, as far as I can tell, is find some way of saying:

    - In row 5, apart from col A (which has the date), for each cell with an entry, take that entry as the heading, eg column D.
    - Repeat for all columns through column AZ, eg columns E, H, I, M etc. and use the set of headings gained as x-axis labels on the chart (some will be contiguous, some not).

    - Check column A for a cell containing 'Last Month total', eg in row 15.
    - Take the figure from the cell where this crosses with the headings, ie D15, E15, H15, I15, M15, etc.
    - Repeat for all columns through column AZ and use the set of figures gained as series 1 on the chart.

    - Check column A for a cell containing 'This Month total', eg in row 20.
    - Take the figures from this row where they cross with the headings to get series 2 on the chart, ie D20, E20, H20, I20, M20, etc.

    etc.

    Unless someone has a better idea? I did think of telling it to remove all columns from C onwards where row 5 was blank, so as to get a contiguous set of data, but I'm not sure how practical this is, bearing in mind the size of the workbook (around 1.3Mb and growing every month) and the fact that changes are not saved (it has to be returned to its original state when I've finished with it), so putting quite a strain on the system while working with it.

    Am I wishing for the moon here? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> As I said, it's complicated ...!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    The obvious solution, of course, is to tell the people who create the workbook to get their act together and to produce something that is consistently usable. But this is real life, so no doubt it's out of the question...

    Since you don't want to modify the source workbook, how about copying the cells you need into a contiguous range in a new workbook? You could first determine the row numbers of the rows you need, then loop through the columns in row 5 to collect the data.

    So say you need row 5 (for the headings), row 15, row 20 and row 37.
    Loop through the cells in B5:AZ5, if a cell is not blank, copy the cells in rows 5, 15, 20 and 37 in that column to contiguous cells in your new workbook.

    It should be easy (I hope) to create a chart based on the resulting range.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    Sounds good, thanks. How on earth would I do it?!
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    PS re the source, I agree they should (get their act together) but yes, this is real life, so no, they won't and in any case the number/content of the columns does change occasionally, so there will be legitimate changes in the source data then.

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    Could you provide a small demo workbook with dummy data?

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    It's difficult (I'd have to create it - the real data is far too sensitive!).

    Could you just tell me how I'd go about telling it to take the column number where row 5 contains 'blah' and the row number where column 1 contains 'something else'? I think I know how to go from there ...?

    (she says, hopefully, with fingers crossed!)
    Beryl M


  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    Try this:
    <code>
    Sub Test()
    Dim lngCol As Long
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim oCell As Range
    Set oCell = Range("B5:AZ5").Find(What:="Blah", _
    LookAt:=xlWhole, MatchCase:=False)
    If oCell Is Nothing Then
    MsgBox "'Blah' not found"
    Else
    lngCol = oCell.Column
    MsgBox "'Blah' found in column " & lngCol
    End If
    lngMaxRow = Range("A65536").End(xlUp).Row
    Set oCell = Range("A6:A" & lngMaxRow).Find(What:="Something else", _
    LookAt:=xlWhole, MatchCase:=False)
    If oCell Is Nothing Then
    MsgBox "'Something else' not found"
    Else
    lngRow = oCell.Row
    MsgBox "'Something Else' found in row " & lngRow
    End If
    End Sub
    </code>
    Adapt as needed. Good luck!

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    Thanks, Hans, that's perfect, and I'm pottering along nicely except for one thing - how do I reference a range using R1C1 annotation?!

    The only way I could think of to use the lngcol and lngrow variables was to make an R1C1 reference with them, but if I put that reference in i.range() it complains!

    How do I phrase it properly, please?!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    Not sure what you want. You can refer to the cell in row lngRow and column lngCol as Cells(lngRow, lngCol)

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    That pointed me in the right direction, thanks!

    One more quick question - how do I tell it that the range I want is cell B2 and the rest of the occupied cells in the row ,eg one time it might be B2:F2, another time B2:I2, however many that is? I assume it's something like current region and .end(xlleft) but I don't know how to put it together!

    Ta every so muchly!
    Beryl M


  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    In the following situation:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>H</td><td align=center>I</td><td align=center>J</td><tr><td align=center>1</td><td align=right>

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    Hans, XL isn't playing nice today! I've since discovered that even one of the constants I thought I had, isn't, ie the fact that the headings are always on row 5, so now I've had to configure a variable for this and modify your suggested line of code to allow for it:

    Set oCell = i.Range(Cells(chTitle, "B"), Cells(chTitle, "AZ")).Find(What:=chDept, LookAt:=xlWhole, MatchCase:=False)

    The variable is chTitle, a long, and picks up the row number fine, chDept is a string; everything else is exactly as you said in your post and it worked perfectly until now.

    Why then does the code fall over on this line with "Error 1004: Application-defined or Object-defined error"? Have I got something wrong in my code?

    <img src=/S/weep.gif border=0 alt=weep width=21 height=16>
    Beryl M


  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    What is i in i.Range(...) ?

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    For Each i In Workbooks("headcount.xls").Worksheets

    Again, it worked perfectly before I had to include the variable in the range! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated chart source data (xl97/WinNt4)

    It works OK in a test workbook I created. Try setting a breakpoint on the offending line (click in the margin to the left of the line, or click in the line and press F9). When the code pauses, hover the mouse over the variables to inspect their values. Make sure that chTitle is set correctly, for instance - if it hasn't been set, it will have the default value 0, and since there is no row 0, this would generate the error message you get.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •