Export a formula to excel - is it possible?

[#1]

Hi,

Is it possible to export (via VBS) a formula to excel? We export now row by row (A, B, C) the cell values via a macro, and in a certain row (e.g. D) we want to export a formula (e.g. =Ci/Bi*F1), so that it is preprogrammed for the end user who can type a different value in the cell F1 and see the changed result in column D.

What is the VBS function for formula export if it is possible?

Thanks,

B.

[Gysbert_Wassenaar]

That should be possible.

set xl = createobject("excel.application")

set wb = xl.workbooks.add

set ws = wb.worksheets(1)

xl.visible = true

ws.range("D1:D100").formula = "C1/B1*F$1"

this should result in D2 having the formula C2/B2*F$1 and not C1/B1*F$1

[#3]

Thank you, I'll test it.

[Clever_Anjos]

You can create a column at your table that contains your formula

='=C'&rowno()&'B'&rowno()&'*F$1'

When this table is copied to excel, the formula will be translated.

Important: When you export, this trick does not work, only when you copy/paste