By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Access to Exel creating variable formula

P: 2
Hello I am sending data from access 2003 to Excel I then want to create a total sum for the data I have sent I can manage this if the data length is always the same length which it is not this worked when the data is the same

With gm_oExcelSheet.Rows(gCurrentExcelRow)
.Cells(, 7).value = "=SUM(R[-4]C:R[-1]C)"
End With

I then tried adding variables gStartRow as long and gEndRow as long. I have tried men variations on this but it is as if the variables do not work in the sum line

With gm_oExcelSheet.Rows(gCurrentExcelRow)
.Cells(, 7).ActiveCell.value = "=SUM(R[gStartRow]C:R[gEndRow]C)"
.Cells(, 7).value = "=SUM(R[gStartRow]C:R[gEndRow]C)"
End With

also tried it on its own gm_oExcel is my variable
Global gm_oExcel As Excel.Application

gm_oExcel.ActiveCell.FormulaR1C1 = "=SUM(R[gEndRow]C[-1]:R[gStartRow]C[-1])"
n
Any help would be great
John
Oct 25 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,489
Certainly .FormulaR1C1 is the correct property to set in these circumstances.
Another point to look out for is that the Format of these cells must NOT be Text (.NumberFormat = "@").
Last point I noticed, if gEndRow & gStartRow are variables in your Access VBA code then they should be coded something like :-
Expand|Select|Wrap|Line Numbers
  1. gm_oExcel.ActiveCell.FormulaR1C1 = "=SUM(R" & gEndRow & "C[-1]:R" & gStartRow & "C[-1])"
Hope this helps.
Oct 25 '06 #2

P: 2
Certainly .FormulaR1C1 is the correct property to set in these circumstances.
Another point to look out for is that the Format of these cells must NOT be Text (.NumberFormat = "@").
Last point I noticed, if gEndRow & gStartRow are variables in your Access VBA code then they should be coded something like :-
Expand|Select|Wrap|Line Numbers
  1. gm_oExcel.ActiveCell.FormulaR1C1 = "=SUM(R" & gEndRow & "C[-1]:R" & gStartRow & "C[-1])"
Hope this helps.
Thank you for your help I realy apreciate it

John
Oct 27 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.