| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Name Defining | Excel Discussion (Misc queries) | |||
| defining a formula | Excel Programming | |||
| formula for defining axis maximum and increments | Charts and Charting in Excel | |||
| Defining Cell Row Value Remotely in Formula not Code | Excel Programming | |||
| Defining a formula | Excel Programming | |||
Linear Mode
