Defining a formula
 

HI,
I have a block of data in one worksheet and I am trying to have VBA write a
formula in a different worksheet. The range to be used in the formula is
not static. Based on some other VBA coding I find the rows bounding the
range to be used in the formula. When the macro is run I get an error in the
cell that contains the formula.

In this example the data is in the worksheet "EstimatedNEEDaylight"
From the other coding I have found that the range should be from E2 to E74.
The column E and row 2 are constants, but the row 74 is the real dynamic part
so was stored in the variable greenuprow

The VBA code I am trying run looks like the following

ActiveCell.FormulaR1C1 = "=Sum(EstimatedNEEDaylight!E2:E" & greenuprow & ")"

The resulting formula that is placed in the cell that gives the error is
=Sum(EstimatedNEEDaylight!'E2':'E74')

I have tried a couple variation on the theme like creating a variable for
the sum parameter, but keep getting the same type of error. Also running the
VBA in Debug mode I can see the resulting formula looks correct
there.....that is should be
=Sum(EstimatedNEEDaylight!E2:E74)

Any ideas????
Thanks

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

Defining a formula
 

Try changing the .formular1c1 to just plain old .formula.



MMesarch wrote:

HI,
I have a block of data in one worksheet and I am trying to have VBA write a
formula in a different worksheet. The range to be used in the formula is
not static. Based on some other VBA coding I find the rows bounding the
range to be used in the formula. When the macro is run I get an error in the
cell that contains the formula.

In this example the data is in the worksheet "EstimatedNEEDaylight"
From the other coding I have found that the range should be from E2 to E74.
The column E and row 2 are constants, but the row 74 is the real dynamic part
so was stored in the variable greenuprow

The VBA code I am trying run looks like the following

ActiveCell.FormulaR1C1 = "=Sum(EstimatedNEEDaylight!E2:E" & greenuprow & ")"

The resulting formula that is placed in the cell that gives the error is
=Sum(EstimatedNEEDaylight!'E2':'E74')

I have tried a couple variation on the theme like creating a variable for
the sum parameter, but keep getting the same type of error. Also running the
VBA in Debug mode I can see the resulting formula looks correct
there.....that is should be
=Sum(EstimatedNEEDaylight!E2:E74)

Any ideas????
Thanks

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

--

Dave Peterson

Defining a formula
 

Sub gsnu()
Dim greenuprow As Integer, s As String
greenuprow = 74
s = "EstimatedNEEDaylight!E2:E" & greenuprow
ActiveCell.Formula = "=sum(" & s & ")"
End Sub

--
Gary''s Student


"MMesarch" wrote:

HI,
I have a block of data in one worksheet and I am trying to have VBA write a
formula in a different worksheet. The range to be used in the formula is
not static. Based on some other VBA coding I find the rows bounding the
range to be used in the formula. When the macro is run I get an error in the
cell that contains the formula.

In this example the data is in the worksheet "EstimatedNEEDaylight"
From the other coding I have found that the range should be from E2 to E74.
The column E and row 2 are constants, but the row 74 is the real dynamic part
so was stored in the variable greenuprow

The VBA code I am trying run looks like the following

ActiveCell.FormulaR1C1 = "=Sum(EstimatedNEEDaylight!E2:E" & greenuprow & ")"

The resulting formula that is placed in the cell that gives the error is
=Sum(EstimatedNEEDaylight!'E2':'E74')

I have tried a couple variation on the theme like creating a variable for
the sum parameter, but keep getting the same type of error. Also running the
VBA in Debug mode I can see the resulting formula looks correct
there.....that is should be
=Sum(EstimatedNEEDaylight!E2:E74)

Any ideas????
Thanks

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

Defining a formula
 

Thanks. that did the trick
--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln


"MMesarch" wrote:

HI,
I have a block of data in one worksheet and I am trying to have VBA write a
formula in a different worksheet. The range to be used in the formula is
not static. Based on some other VBA coding I find the rows bounding the
range to be used in the formula. When the macro is run I get an error in the
cell that contains the formula.

In this example the data is in the worksheet "EstimatedNEEDaylight"
From the other coding I have found that the range should be from E2 to E74.
The column E and row 2 are constants, but the row 74 is the real dynamic part
so was stored in the variable greenuprow

The VBA code I am trying run looks like the following

ActiveCell.FormulaR1C1 = "=Sum(EstimatedNEEDaylight!E2:E" & greenuprow & ")"

The resulting formula that is placed in the cell that gives the error is
=Sum(EstimatedNEEDaylight!'E2':'E74')

I have tried a couple variation on the theme like creating a variable for
the sum parameter, but keep getting the same type of error. Also running the
VBA in Debug mode I can see the resulting formula looks correct
there.....that is should be
=Sum(EstimatedNEEDaylight!E2:E74)

Any ideas????
Thanks

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

Defining a formula
 

You are very welcome Mark.
--
Gary''s Student


"MMesarch" wrote:

Thanks. that did the trick
--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln


"MMesarch" wrote:

HI,
I have a block of data in one worksheet and I am trying to have VBA write a
formula in a different worksheet. The range to be used in the formula is
not static. Based on some other VBA coding I find the rows bounding the
range to be used in the formula. When the macro is run I get an error in the
cell that contains the formula.

In this example the data is in the worksheet "EstimatedNEEDaylight"
From the other coding I have found that the range should be from E2 to E74.
The column E and row 2 are constants, but the row 74 is the real dynamic part
so was stored in the variable greenuprow

The VBA code I am trying run looks like the following

ActiveCell.FormulaR1C1 = "=Sum(EstimatedNEEDaylight!E2:E" & greenuprow & ")"

The resulting formula that is placed in the cell that gives the error is
=Sum(EstimatedNEEDaylight!'E2':'E74')

I have tried a couple variation on the theme like creating a variable for
the sum parameter, but keep getting the same type of error. Also running the
VBA in Debug mode I can see the resulting formula looks correct
there.....that is should be
=Sum(EstimatedNEEDaylight!E2:E74)

Any ideas????
Thanks

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln