calculate entire row excel vfp automation 2

[mikeisvfp]

Hello Experts

I am hoping I can get an example of how to calculate an entire column? i Just want to add values

 

[JRB-Bldr]

how to calculate an entire column

How to calculate WHAT on the entire column?
* The Sum?
* The Average?
* etc.

Have you done the work in Excel while recording it as a Macro?

Good Luck,
JRB-Bldr

 

[GriffMG]

I think you could do it with something like this:

oexcel.Range("A2").Select
oexcel.ActiveCell.FormulaR1C1 = "=SUM(C[3])"

or

oexcel.Range("A2").Select
oexcel.ActiveCell.FormulaR1C1 = "=SUM('D')"





Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

I was just about to remind him that the best way to find out how to do something like this is to record a macro and then look at the code...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[mikeisvfp]

lnCol = lnCol + 1
oSheet.Range([F3]).Select
oSheet.Cells(lnRow,lnCol).Value = [Duration]
oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
*oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3
oSheet.Cells(lnRow,lnCol).Interior.ColorIndex = 6
oSheet.Cells(lnRow,lnCol).Borders.Linestyle = 1
oSheet.Cells(lnRow,lnCol) = "=SUM(f3:f44)"

Ok, so Ive tried as much as i could, I tried Griffs example and it does not recognize ActiveCell.ForumlaR1C1 it gives me an error message "unknown name". I tried the above and it works only the result apears at the very top of the F column, where as I would like it to apear at the bottom just below the last value.
Also is there a way to =SUM the entire F column and have the result apear at the very bottom of the last value, above i am specifying (f3:f44) ?

 

[Dan Freeman]

A couple of things are happening here.

For starters, you need to learn how to use the right object. The method calls belong to an object, and without the right object reference they won't work. You'll get errors.

To place a Sum() in a specific cell, you need a reference to that specific cell. What part of this did you not understand? You apparently managed to put it in a different cell? Can't you change the address?

I would caution you, though, about not using absolute addresses. What happens if the input suddenly has more rows? You'll probably want to query the .UsedRows property to figure out the last cell and place the result in the next one.

To be honest, though, most of this is an Excel problem and you'll find people who know Excel better in an Excel forum. Aside from getting the right object reference none of this has anything to do with Foxpro.

 

[GriffMG]

If you know that the data you need is NOT the whole column (i.e. just rows 3 to 44) and you want the result in row 45, use a macro to get the general form for your version of excel and then adapt that for the automation for excel.

So perhaps the code you need to follow is like this:

    oexcel.Range("f45").Select
    oexcel.ActiveCell.Formula = "=sum(f3:f44)"

It could be that your version of excel does not accept my previous suggestion of .FormulaR1C1

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

The problem with using the macro recorder to solve these problems is that it first selects a range, and then applies some action to the selection. That's fine if you were doing the task interactively. But, in general, there's no reason to select a range programmatically.

In this case, it's simpler to do something like this:

oWorksheet.Range("F45").Formula = "=SUM(F3:F44)"

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mikeisvfp]

Thank You Griff and Mike you guys are awesome both solutions worked.

Thank You once again