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

Topic

New Reply

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.

Reply | Quote

Replies

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:

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

Reply | Quote

> subtract 7 from the SeriesCollection(1)

Subtract 7 from what? SeriesCollection(1) returns a Series object which has an array of XValues and corresponding Values.

> make that Point black.

What point?

One last question, are these embedded charts or are they chart sheets?

Reply | Quote

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

Reply | Quote

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

Option Explicit
Sub Colour()
Dim co As ChartObject
    For Each co In ActiveSheet.ChartObjects
        co.Chart.SeriesCollection(1).Points(2).Interior.ColorIndex = 1
    Next co
End Sub

Reply | Quote