Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing Formula to Excel Using Cells Reference 1

Status
Not open for further replies.

joebb3

Programmer
Feb 27, 2006
87
US
I generate a dynamic spreadsheet from Access. It works perfect except I do my MATH in access and just pass the result to Excel.

Like this:
Code:
wks.Cells(iRow, X) = LColMinor

LColMinor contains a sum of specific data.

What I want to do is pass the formula to make the spreadsheet updateable.

HOWEVER... I use the NUMERIC reference to cells in my code, NOT the Alphanumeric.

Like:
Code:
appexcel.Range(Cells(iRow, X), Cells(iRow, X)).Select

or

wks.Cells(9, 4)

So how do I pass a fomula based on Cells?

Here is an example of a failed attempt of mine:

Code:
wks.Cells(8, 9).Formula = "=Sum(Cells(" & PrevFR & "," & X & "): Cells(" & PrevLR & "," & X & "))"

which works out to 

=Sum(Cells(4,9):Cells(7,9))

Thanks in advance!

Joe
 



hi,

use the Address property of the range object...
Code:
wks.Cells(8, 9).Formula = "=Sum(" & Cells(PrevFR, X).Address & ":" & Cells(PrevLR, X).address & ")"

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Skip... How many ways is there to say... "You da man!"

Thanx Tons! Here's a star! :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top