Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel macro needed (XP)

    Hi,

    I'm writing a macro to customize a chart that is created by an outside program. The program (Advent Axys) creates the chart in Excel, reference my macro for customization and then displays the chart in Axys. The graph is a pie graph. The data being used is a series of 2 rows and about 4-12 colums. The first row has the titles. The second row has amounts. The graph displays %.

    The problem is in the way the other program populates the series. There are duplicates in the colums. For example, if the total sum for label A is 10, Axys may parse the data into:
    A B A C
    2 x 8 x

    I'd like to come up with a way to recognize when this happens and then combine the parsed figures, and delete the unneeded column.

    Any thoughts.

    Thanks in advance,

    Jay

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

    Re: Excel macro needed (XP)

    If you can live with sorting the labels, the following macro might help. It assumes the data start in cell A1 in the active worksheet. It's easy to modify it: just change the line <font face="Georgia">Set rng = Range("A1").CurrentRegion</font face=georgia>.

    Sub HandleDuplicates()
    Dim rng As Range
    Dim intColCount As Integer
    Dim i As Integer
    ' Range with data
    Set rng = Range("A1").CurrentRegion
    ' Sort from left to right
    rng.Sort Key1:=Range("A1"), Orientation:=xlSortRows
    ' Number of columns
    intColCount = rng.Columns.Count
    ' Loop through columns, backwards
    For i = intColCount - 1 To 1 Step -1
    ' Test if label is equal to next
    If rng.Cells(1, i) = rng.Cells(1, i + 1) Then
    ' If so, add values together
    rng.Cells(2, i) = rng.Cells(2, i) + rng.Cells(2, i + 1)
    ' And remove next column
    rng.Columns(i + 1).Delete Shift:=xlShiftToLeft
    End If
    Next i
    Set rng = Nothing
    End Sub

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro needed (XP)

    Thanks, that was quick.

    Any way to do it without the sort?

    Jay

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

    Re: Excel macro needed (XP)

    Sorry for the delay, the internet connection to all sites abroad was lost for a while; it's still extremely slow. Here is a version that doesn't sort. It'll be significantly slower for large ranges, because it has a double loop instead of a single one.

    Sub HandleDuplicates()
    Dim rng As Range
    Dim intColCount As Integer
    Dim intMaxCol As Integer
    Dim intCurCol As Integer
    Dim i As Integer
    ' Range with data
    Set rng = Range("A1").CurrentRegion
    ' Number of columns
    intColCount = rng.Columns.Count
    intMaxCol = intColCount
    intCurCol = 1
    Do While intCurCol < intMaxCol
    ' Loop through columns, backwards
    For i = intMaxCol To intCurCol + 1 Step -1
    ' Test if label is equal to current
    If rng.Cells(1, i) = rng.Cells(1, intCurCol) Then
    ' If so, add values together
    rng.Cells(2, intCurCol) = rng.Cells(2, i) + rng.Cells(2, intCurCol)
    ' Remove column
    rng.Columns(i).Delete Shift:=xlShiftToLeft
    ' And update column count
    intColCount = intColCount - 1
    End If
    Next i
    intCurCol = intCurCol + 1
    intMaxCol = intColCount
    Loop
    Set rng = Nothing
    End Sub

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro needed (XP)

    Thanks, I'l give it a try

    Jay

Posting Permissions

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