Use variable in macro coding

[bmurlidhar]

I want to give sum command i.e. =SUM(H1:H10) and my variable for
mFrow = 1 and mLrow = 10 how can i use variable in function

 

[Per Jessen]

MyFormula="Sum("H" & mFrow & ":H" & mLrow & ")"

 

[JE McGimpsey]

Not sure exactly what you mean by "give the sum command"...

If you want to sum a range, then one way:

Dim dSum As Double
Dim mFrow As Long
Dim mLrow As Long

mFrow = 1
mLrow = 10

dSum = Application.Sum(Range(Cells(mFrow, 8), Cells(mLrow, 8)))


If you want to put a formula in a cell, then one way:

Dim mFrow As Long
Dim mLrow As Long

mFrow = 1
mLrow = 10

Cells(1, 1).Formula = "=SUM(H" & mFrow & ":H" & mLrow & ")"

 

[Chirag]

If your variables are stored in cells, you can do something like the
following:
1. Assuming the value of mFrow is stored in A1 and the value of mLrow is
stored in A2.
2. In cell A3, write down the following:
=TEXT("H" & A1 & ":H" & A2, "")
3. In cell A4, you can get your sum with the following formula:
=SUM(INDIRECT(A3))

Basically, in cell A3, we are building up the expression "H1:H10" and in
cell A4, we are using the string in A3 to build the range using the
INDIRECT() function.

- Chirag

Shortcut Manager - Assign keyboard shortcuts to Excel menu items and
macros.
http://officeone.mvps.org/xlsctmgr/xlsctmgr.html