Algorithm Question

[jeffcravener]

I am writing some code inside a module attached to an excel workbook.

Let me give you some background on what I am having them do:

In consecutive cells, the user will enter a number indicating a column in a data file.

Now, this column in the data file correlates to a column in the excel report. But the file it is getting the information from might consist of more than 1 column to get the value.

So if you are looking for total calls offered, you may need to get the data from two different columns in the file, a calls answered column and a calls abandoned column. We'll say column 3 is calls answered and column 6 is calls abandoned.

In the example above, the addition of the values in those two columns would equal to calls offered. Now, using the paramters I have come up with, the user would enter in the column numbers like this:

Cell1: 3 Cell2: 6 Cell3: +

So then ,you would read that as add the values of column 3 and column 6 to get the total data.

Another example:

Cell1: 2 Cell2: 8 Cell3: / Cell4: 5 Cell5: 6 Cell6: 10 Cell7: + Cell8: -

The above would tell you that the total you are looking for is made up of columns 2,8,5,6, and 10

And to get the total you would divide column 8 by column 2, then add columns 5, 6, and 10, then subtract that total from the value of the first two.

First off, I apologize if I am not clearly describing what I am trying to do.

But if you understand what I am trying to do, then continue : )

This is what I have so far as my algorithm:

 Dim arr() As String
    Dim arr2() As Double
    Dim value As Double
    Dim col As Long
    Dim i As Long
    Dim ii As Long
    Dim iii As Long
    
    ReDim arr(10000)
    i = 0
    With Sheet("DATA")
        For col = Asc("B") To Asc("K")
            If Not (.Range(Chr(col) & Row & ":" & Chr(col) & Row).value = "") Then
                arr(i) = .Range(Chr(col) & Row & ":" & Chr(col) & Row).value
                i = i + 1
            Else
                Exit For
            End If
        Next
        ReDim Preserve arr(i - 1)
    End With
    
    If UBound(arr) = 0 Then
        RetValue = arr(0)
        CalculateField = True
        Exit Function
    ElseIf UBound(arr) = 1 Then
        MsgBox "Wrong number of arguments to run the report. Check Data Tab. Report aborting."
        CalculateField = False
        Exit Function
    End If
    
    Dim arr2(10000)
    ReDim arr3(10000)
    ii = 0
    For i = 0 To UBound(arr)
                
        If IsNumeric(arr(i)) Then
            arr2(ii) = arr(i)
            ii = ii + 1
        Else
            ReDim Preserve arr2(ii - 1)
            Select Case arr(i)
                Case "*"
                    
                Case "+"
                    
                Case "-"
                    
                Case "/"
                    
                Default
                    MsgBox "The expression " & arr(i) & " was not recognized. Check Data Tab. Report aborting"
                    CalculateField = False
                    Exit Function
            End Select

Having trouble putting together the values once a +,-,/, or * is found.

Any help would be greatly appreciated.

IF I have confused you, I apolgize!

[Shaggy Hiker]

So an equation is being entered where the first n entries are data, and n+1 will be one of the four operators [+,-,*,/].

If that is the case, it seems to me that you would want to do one of two things:

1) Scan forward to see what the operator is, then go back and process the data values.

2) Move data values into a dynamic array until you find an operator, then process the array.

But you're talking about a column. Do you intend to use the function created on each cell within the column? If so, I think you want to take the input and turn it into a function such that x,y,+ becomes x+y.

In excel, it seems like you could write the equation in a cell to reference several unused cells that will hold the data. Then transfer the info from column x to <equation cell x> and let excel evaluate the equation, take the value produced and put it wherever it needs to go.