Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search and load into array in Excel xp (Excel xp, win2000)

    I need to search through a worksheet for the words TOTAL U.S. and take the address of the cell and put it into an array. I then need to extract from the address just the numerals (A41 or A2000, I just want the 41 and the 2000) loaded into TOTALUS(0) = A41, NUM(0) = 41

    After I load those 2 arrays I need to search for each chart.object in the worksheet (there will be approx 40) and subtract 7 from the SeriesCollection(1) and make that Point black.

    This is what i've done so far

    dim i as integer
    dim j as integer

    i = 0

    for each cell in ActiveWorksheet
    search for first "TOTAL U.S."
    TOTALUS(i) = range.address
    i = i + 1
    some kind of string code to get numerals from TOTALUS(0) and put number in ADDRESS(0)
    next cell

    j = 1
    for each chart.object
    Search for first chart.object and activate it
    ActiveChart.SeriesCollection(1).select
    ActiveChart.SeriesCollection(1).Points((Address(j) ) - 7).select
    with selection.interior
    .colorIndex = 1
    .Pattern=xlSolid
    end with
    j = j + 1
    next


    I need some help with my dim statement for the searches and how to "extract" only the numerals from the TOTALUS array. Thanks you very much.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and load into array in Excel xp (Excel xp, win2000)

    I don't know enough about charts to know what you are asking in your second question. However, this should do what you asked in the first:

    <pre>Dim oCell As Range
    Dim adrTOTALUS() As String, lRow() As Long
    Dim i As Long
    i = 0
    For Each oCell In ActiveSheet.UsedRange
    If oCell.Value = "TOTAL U.S." Then
    ReDim Preserve adrTOTALUS(i + 1), lRow(i + 1)
    adrTOTALUS(i) = oCell.Address(False, False)
    lRow(i) = oCell.Row
    i = i + 1
    End If
    Next oCell
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search and load into array in Excel xp (Excel xp, win2000)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > subtract 7 from the SeriesCollection(1)
    <img src=/w3timages/blackline.gif width=33% height=2>
    Subtract 7 from what? SeriesCollection(1) returns a Series object which has an array of XValues and corresponding Values.

    <img src=/w3timages/blackline.gif width=33% height=2>
    > make that Point black.
    <img src=/w3timages/blackline.gif width=33% height=2>
    What point?

    One last question, are these embedded charts or are they chart sheets?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search and load into array in Excel xp (Excel xp, win2000)

    j = 1
    for each chart.object
    Search for first chart.object and activate it
    ActiveChart.SeriesCollection(1).select
    ActiveChart.SeriesCollection(1).Points((Address(j) ) - 7).select
    with selection.interior
    .colorIndex = 1
    .Pattern=xlSolid
    end with
    j = j + 1
    next

    I have a list of 50 cities in column A. Next to it i have a bar chart that is a separate object. The bars are all red but i have to turn one of them black. I search thru the 50 cities for the words US TOTAL and say it falls in cell A41. It turns out that the "point" in the bar chart that needs to be made black is 7 less than 41. There are 100 of these charts on the sheet and it is an ongoing job, therefore i want to make an automatic way to make the bar black. Hope you can help me. thanks

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search and load into array in Excel xp (Excel xp, win2000)

    Here's for bar #2, just put in your address stuff instead of the 2. Don't bother with all those activates and selects, just slows you down. HTH --Sam<pre>Option Explicit
    Sub Colour()
    Dim co As ChartObject
    For Each co In ActiveSheet.ChartObjects
    co.Chart.SeriesCollection(1).Points(2).Interior.Co lorIndex = 1
    Next co
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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