objXL.activeCell.FormulaArray

[Eowyne]

Hello again!

I know, I'm way in over my head with this one.. but I'm rowing with the paddles that I have and gather all the straws I can find.. and this is one of the functions I found dwelling on the internet:

=SUM((Systems!F:F=Totals!B4)*(Systems!G:G=Totals!C 4))



which works bloody brilliant!
However....

The problem is the fact that I'm right now trying to write that to VBA code..
And not that that's the real problem.. the problem is that in the above example i know that B4 is B4, and C4 is C4.. I don't know this in my code.

Here's a piece of my code:

xlWSi.range("A4").select
Set MyRec = CurrentDb.OpenRecordset(SQLSysteem)
If MyRec.RecordCount <> 0 Then
    MyRec.MoveFirst
    
    While Not MyRec.EOF
        For Each Fld In MyRec.Fields
            With objXL.activeCell
                .Value = UCase(Fld.Value)
                PrevCell = objXL.activeCell.Address
                objXL.activeCell.Offset(0, 1).select
            End With
        Next Fld
'This is the trouble..
        objXL.activeCell.FormulaArray = "=SUM((Systems!F:F=Totals!" & PrevCell & ")*(Systems!G:G=Totals![???])"
' 
        objXL.activeCell.Offset(1, -3).select
        MyRec.MoveNext
    Wend

there ya' go!

as you can see, i've found the way to place the {} around an =SUM array in Excel, but I am as of yet unable to find the adress of the cell before the active cell (well, I found that: PrevCell holds that value for me).
However, for the function to work, I need the adress of the cell before that one too!

objXL.activeCell.FormulaArray = "=SUM((Systems!F:F=Totals!" & PrevCell & ")*(Systems!G:G=Totals!)"

This translates too: {=SUM((Systems!F:F=Totals!C4)*(Systems!G:G=Totals![Need_This_Cell_To_Become_B4])

Can you point me into the right direction as to how I can find out that, if the previous cell was C4, the one before that must me B4?

Or, if you can point me into a direction in how to find out the Row in which I currently reside, I can add C and B myself.. for these are static fields

[Eowyne]

Oh bah... solved it :P

The interwebs gave me the following code:

R = objXL.ActiveCell.ROW

so I can create a:

objXL.ActiveCell.FormulaArray = "=SUM((Systems!F:F=Totals!$B$" & R & ")*(Systems!G:G=Totals!$C$" & R & "))"

and that was exactly what I was looking for

is there a website where I can check all the Functions in that Excel.Object?
I am unable to find that :S