Excel Automation: Varying cell range in formula 1

[torturedmind]

Hello all,

I'm stumped on this particular MS Excel automation wherein I was trying to put a formula in a cell whose formula range varies depending on the user's input.

oSheet.Range("C13").Formula = [=IFERROR(ROUND(AVERAGE(C10:AG10),0),"-")]

That is, the "AVERAGE(C10:AG10)" part may be "AVERAGE(C10:Z10)" only or it may be "AVERAGE(C10:XFD10)" and so on. I'm thinking of using the CHR() function but really don't know where to grasp the idea on how to approach this. Will someone please guide me to the right direction? Thanks in advanced.

Regards,

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[vgulielmus]

I propose two solutions.
First is the function you seek.

?getexcelcolname(16384) && XFD

FUNCTION getexcelcolname
* Parameter
* - lnCol		the number of column
LPARAMETERS lnCol
IF BETWEEN(lnCol,1,16384)
	RETURN IIF(m.lnCol <=26,[],;
			IIF(m.lnCol <=702,CHR(64 + FLOOR((m.lnCol - 1) / 26)),;
					CHR(64 + FLOOR((m.lnCol - 1 - 26) / 676))+CHR(65 + FLOOR(MOD(m.lnCol - 1 - 26,676) / 26))))+;
		CHR(65 + MOD(m.lnCol - 1,26))
ELSE
	RETURN -1 && error
ENDIF

Second is to use formular1c1 instead of formula.
The following pairs are equivalent :

oSheet.cells(1,1).formula = "=$XFD$10"
oSheet.cells(1,1).formular1c1 = "=R10C16384"

oSheet.cells(1,1).formula = "=XFD10"
oSheet.cells(1,1).formular1c1 = "=R[9]C[16383]" && current row (1) + 9 and current column (1) + 16383

oSheet.cells(3,2).formula = "=XFD10"
oSheet.cells(3,2).formular1c1 = "=R[7]C[16382]" && current row (3) + 7 and current column (2) + 16382

oSheet.cells(13,2).formula = "=XFD10"
oSheet.cells(13,3).formular1c1 = "=R[-3]C[16381]" && current row (13) + 7 and current column (3) + 16381

oSheet.Range("C13").Formula = "=AVERAGE(C10:XFD10)"
oSheet.Range("C13").FormulaR1C1 = "=AVERAGE(R[-3]C:R[-3]C[16381])" && C10 is the same column and current row - 3

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[torturedmind]

Thank you good sir for your suggestions. I'll try to experiment with them. A star for you.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Olaf Doschke]

You forgot to tell us what you know, if you don't know the cell names. Most probably you know the amount of data and its width and height.
Then the last line of Vilhelm-Ions second code sample is the most important one, as you can specify start and end cell in numeric row and column coordinates relative to the cell, in which you set FormulaR1C1, so here relative to C13. It wouldn't be wrong to specify C10 relative from C13 as R[-3]C[0].

If the position of the cell with the formula would need to be determined from row and column coordinates, you also better not work with cell names but the Cells(row,col) collection of oSheet, no need to use a range for a single cell.

So in general:

* with known row/col offset values (both relative to the cell with the formula)
nrowoffset1 = -3
ncolumnoffset1 = 0
nrowoffset2 = -3
ncolumnoffset2 = 10
cRange = TextMerge("R[<<nrowoffset1>>]C[<<ncolumnoffset1>>]:R[<<nrowoffset2>>]C[<<ncolumnoffset2>>]")
* then
oSheet.Cells(13,3).FormulaR1C2="AVERAGE("+cRange+")"

Bye, Olaf.

 

[torturedmind]

Hi Sir Olaf.

I ended up using Sir Vilhelm's first suggestion. I also tried your suggestion and it's also good. But it's easier to use cell names since there are a lot going on in the automation that keeping track of the cursor's current position is a bit hard, at least for me, that is. Using cell name like "UG25" makes me confident that am really referring to cell "UG25" though I also use Cell(nRow, nCol) a lot. Anyway, am sure I'm gonna be using FormulaR1C1 in the future but not at the moment.

Thanks again.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."