VBA formula in macro not working
 

I am using Excel 2003. I am using variables to insert formulas into an excel
worksheet. I am using Row Count to flexibly describe the range in a sum,
median, & sum of the product. The sum of the product is working when I hard
code the column for both parts of the formula (feeding it the columns letter)
However, when I try to use one hard coded column part & the other relative
column, the formula returns with the hard coded part with surrounded single
quotes.
Here is the actual code:

Doesnt work €“ returns =SUMPRODUCT('O14':'O22',(X15:X23))/SUM('O14':'O22')
This is the code:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Value = "=sumproduct(" &
SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

Variables
SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = "(R[" & -SemisRowCount + 4 & "]C:R[" & -1 & "]C)"


Works
This is the code:
ActiveCell.Offset(i + SemisRowCount + CompRowCount, 0).Value =
"=sumproduct(" & CompBenchWgt & "," & TgtComp & ")/sum(" & CompBenchWgt & ")"

Variables
CompBenchWgt = "O" & SemisRowCount + i + 5 & ":O" & SemisRowCount +
CompRowCount + i
TgtComp = "X" & SemisRowCount + i + 5 & ":X" & SemisRowCount + CompRowCount
+ i

Any help would be appreciated.
Thanks!

VBA formula in macro not working
 

Chloe,

You are mixing R1C1 with A1 style referencing. Try something like

SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = Range(ActiveCell.Offset(-SemisRowCount + 4), ActiveCell(0)).Address(False, False)

'This should work:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Formula = _
"=sumproduct(" & SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
I am using Excel 2003. I am using variables to insert formulas into an excel
worksheet. I am using Row Count to flexibly describe the range in a sum,
median, & sum of the product. The sum of the product is working when I hard
code the column for both parts of the formula (feeding it the columns letter)
However, when I try to use one hard coded column part & the other relative
column, the formula returns with the hard coded part with surrounded single
quotes.
Here is the actual code:

Doesn't work - returns =SUMPRODUCT('O14':'O22',(X15:X23))/SUM('O14':'O22')
This is the code:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Value = "=sumproduct(" &
SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

Variables
SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = "(R[" & -SemisRowCount + 4 & "]C:R[" & -1 & "]C)"


Works
This is the code:
ActiveCell.Offset(i + SemisRowCount + CompRowCount, 0).Value =
"=sumproduct(" & CompBenchWgt & "," & TgtComp & ")/sum(" & CompBenchWgt & ")"

Variables
CompBenchWgt = "O" & SemisRowCount + i + 5 & ":O" & SemisRowCount +
CompRowCount + i
TgtComp = "X" & SemisRowCount + i + 5 & ":X" & SemisRowCount + CompRowCount
+ i

Any help would be appreciated.
Thanks!

VBA formula in macro not working
 

That formula does get rid of the single quote problem I was having. However,
I need to be in the exact cell before my variable is assigned. This is what
it is returning: SUMPRODUCT(O14:O22,A272:A280)/SUM(O14:O22) The A272:A280 is
not correct.

And I need to the column letter to move across the spreadsheet with each
placement of the formula.
I am looking for something flexible like: "(R[-9]C:R[-1]C)" & not this
"A272:A280".
Is that possible?


"Bernie Deitrick" wrote:

Chloe,

You are mixing R1C1 with A1 style referencing. Try something like

SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = Range(ActiveCell.Offset(-SemisRowCount + 4), ActiveCell(0)).Address(False, False)

'This should work:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Formula = _
"=sumproduct(" & SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
I am using Excel 2003. I am using variables to insert formulas into an excel
worksheet. I am using Row Count to flexibly describe the range in a sum,
median, & sum of the product. The sum of the product is working when I hard
code the column for both parts of the formula (feeding it the columns letter)
However, when I try to use one hard coded column part & the other relative
column, the formula returns with the hard coded part with surrounded single
quotes.
Here is the actual code:

Doesn't work - returns =SUMPRODUCT('O14':'O22',(X15:X23))/SUM('O14':'O22')
This is the code:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Value = "=sumproduct(" &
SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

Variables
SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = "(R[" & -SemisRowCount + 4 & "]C:R[" & -1 & "]C)"


Works
This is the code:
ActiveCell.Offset(i + SemisRowCount + CompRowCount, 0).Value =
"=sumproduct(" & CompBenchWgt & "," & TgtComp & ")/sum(" & CompBenchWgt & ")"

Variables
CompBenchWgt = "O" & SemisRowCount + i + 5 & ":O" & SemisRowCount +
CompRowCount + i
TgtComp = "X" & SemisRowCount + i + 5 & ":X" & SemisRowCount + CompRowCount
+ i

Any help would be appreciated.
Thanks!

VBA formula in macro not working
 

Chloe,

Your posted code was referenced to the activecell, so I just duplicated that. What cell(s) should
contain the formula, and how do you determine the range that should be in the place of A272:A280,
and which columns should increment?

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
That formula does get rid of the single quote problem I was having. However,
I need to be in the exact cell before my variable is assigned. This is what
it is returning: SUMPRODUCT(O14:O22,A272:A280)/SUM(O14:O22) The A272:A280 is
not correct.

And I need to the column letter to move across the spreadsheet with each
placement of the formula.
I am looking for something flexible like: "(R[-9]C:R[-1]C)" & not this
"A272:A280".
Is that possible?


"Bernie Deitrick" wrote:

Chloe,

You are mixing R1C1 with A1 style referencing. Try something like

SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = Range(ActiveCell.Offset(-SemisRowCount + 4), ActiveCell(0)).Address(False, False)

'This should work:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Formula = _
"=sumproduct(" & SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
I am using Excel 2003. I am using variables to insert formulas into an excel
worksheet. I am using Row Count to flexibly describe the range in a sum,
median, & sum of the product. The sum of the product is working when I hard
code the column for both parts of the formula (feeding it the columns letter)
However, when I try to use one hard coded column part & the other relative
column, the formula returns with the hard coded part with surrounded single
quotes.
Here is the actual code:

Doesn't work - returns =SUMPRODUCT('O14':'O22',(X15:X23))/SUM('O14':'O22')
This is the code:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Value = "=sumproduct(" &
SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

Variables
SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = "(R[" & -SemisRowCount + 4 & "]C:R[" & -1 & "]C)"


Works
This is the code:
ActiveCell.Offset(i + SemisRowCount + CompRowCount, 0).Value =
"=sumproduct(" & CompBenchWgt & "," & TgtComp & ")/sum(" & CompBenchWgt & ")"

Variables
CompBenchWgt = "O" & SemisRowCount + i + 5 & ":O" & SemisRowCount +
CompRowCount + i
TgtComp = "X" & SemisRowCount + i + 5 & ":X" & SemisRowCount + CompRowCount
+ i

Any help would be appreciated.
Thanks!

VBA formula in macro not working
 

I have 20 groupings that the number of rows in each can increase & decrease,
so I am using the RowCount to determine the size of each. At the bottom of
each grouping, I am inserting Sum & Sum of Product formulas. For the Sum of
Product, I will always weight the current column by the weight in the
Benchmark (column O). I need to do this for 15-20 columns. I wanted to use
a flexible code like "(R[-9]C:R[-1]C)" instead of using a different variable
for every column times every grouping.
I would like the formula to look like the following, with the X changing
with each column I place it in.
SUMPRODUCT(O14:O22,X14:X22)/SUM(O14:O22)
Thank you. Sorry if I am not very clear in my description.


"Bernie Deitrick" wrote:

Chloe,

Your posted code was referenced to the activecell, so I just duplicated that. What cell(s) should
contain the formula, and how do you determine the range that should be in the place of A272:A280,
and which columns should increment?

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
That formula does get rid of the single quote problem I was having. However,
I need to be in the exact cell before my variable is assigned. This is what
it is returning: SUMPRODUCT(O14:O22,A272:A280)/SUM(O14:O22) The A272:A280 is
not correct.

And I need to the column letter to move across the spreadsheet with each
placement of the formula.
I am looking for something flexible like: "(R[-9]C:R[-1]C)" & not this
"A272:A280".
Is that possible?


"Bernie Deitrick" wrote:

Chloe,

You are mixing R1C1 with A1 style referencing. Try something like

SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = Range(ActiveCell.Offset(-SemisRowCount + 4), ActiveCell(0)).Address(False, False)

'This should work:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Formula = _
"=sumproduct(" & SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
I am using Excel 2003. I am using variables to insert formulas into an excel
worksheet. I am using Row Count to flexibly describe the range in a sum,
median, & sum of the product. The sum of the product is working when I hard
code the column for both parts of the formula (feeding it the columns letter)
However, when I try to use one hard coded column part & the other relative
column, the formula returns with the hard coded part with surrounded single
quotes.
Here is the actual code:

Doesn't work - returns =SUMPRODUCT('O14':'O22',(X15:X23))/SUM('O14':'O22')
This is the code:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Value = "=sumproduct(" &
SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

Variables
SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = "(R[" & -SemisRowCount + 4 & "]C:R[" & -1 & "]C)"


Works
This is the code:
ActiveCell.Offset(i + SemisRowCount + CompRowCount, 0).Value =
"=sumproduct(" & CompBenchWgt & "," & TgtComp & ")/sum(" & CompBenchWgt & ")"

Variables
CompBenchWgt = "O" & SemisRowCount + i + 5 & ":O" & SemisRowCount +
CompRowCount + i
TgtComp = "X" & SemisRowCount + i + 5 & ":X" & SemisRowCount + CompRowCount
+ i

Any help would be appreciated.
Thanks!

VBA formula in macro not working
 

Chloe,

Select all of your groups of data (including blank cells in between) and run one of the macros
below.

If the groupings have constants, use the first macro. If they are formulas, try the second.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim RStart As Long
Dim REnd As Long
Dim myA As Range
For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas
RStart = myA.Cells(1).Row
REnd = myA.Cells(myA.Cells.Count).Row
myA.Rows(myA.Rows.Count + 1).Cells.FormulaR1C1 = _
"=SUMPRODUCT(R" & RStart & "C15:R" & REnd & "C15,R[-" & _
(REnd - RStart + 1) & "]C:R[-1]C)/SUM(R" & RStart & "C15:R" & REnd & "C15)"
Next myA
End Sub

Sub Macro2()
Dim RStart As Long
Dim REnd As Long
Dim myA As Range
For Each myA In Selection.SpecialCells(xlCellTypeFormulas, 23).Areas
RStart = myA.Cells(1).Row
REnd = myA.Cells(myA.Cells.Count).Row
myA.Rows(myA.Rows.Count + 1).Cells.FormulaR1C1 = _
"=SUMPRODUCT(R" & RStart & "C15:R" & REnd & "C15,R[-" & _
(REnd - RStart + 1) & "]C:R[-1]C)/SUM(R" & RStart & "C15:R" & REnd & "C15)"
Next myA
End Sub

"Chloe" wrote in message
...
I have 20 groupings that the number of rows in each can increase & decrease,
so I am using the RowCount to determine the size of each. At the bottom of
each grouping, I am inserting Sum & Sum of Product formulas. For the Sum of
Product, I will always weight the current column by the weight in the
Benchmark (column O). I need to do this for 15-20 columns. I wanted to use
a flexible code like "(R[-9]C:R[-1]C)" instead of using a different variable
for every column times every grouping.
I would like the formula to look like the following, with the X changing
with each column I place it in.
SUMPRODUCT(O14:O22,X14:X22)/SUM(O14:O22)
Thank you. Sorry if I am not very clear in my description.


"Bernie Deitrick" wrote:

Chloe,

Your posted code was referenced to the activecell, so I just duplicated that. What cell(s)
should
contain the formula, and how do you determine the range that should be in the place of A272:A280,
and which columns should increment?

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
That formula does get rid of the single quote problem I was having. However,
I need to be in the exact cell before my variable is assigned. This is what
it is returning: SUMPRODUCT(O14:O22,A272:A280)/SUM(O14:O22) The A272:A280 is
not correct.

And I need to the column letter to move across the spreadsheet with each
placement of the formula.
I am looking for something flexible like: "(R[-9]C:R[-1]C)" & not this
"A272:A280".
Is that possible?


"Bernie Deitrick" wrote:

Chloe,

You are mixing R1C1 with A1 style referencing. Try something like

SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = Range(ActiveCell.Offset(-SemisRowCount + 4), ActiveCell(0)).Address(False,
False)

'This should work:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Formula = _
"=sumproduct(" & SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

HTH,
Bernie
MS Excel MVP


"Chloe" wrote in message
...
I am using Excel 2003. I am using variables to insert formulas into an excel
worksheet. I am using Row Count to flexibly describe the range in a sum,
median, & sum of the product. The sum of the product is working when I hard
code the column for both parts of the formula (feeding it the columns letter)
However, when I try to use one hard coded column part & the other relative
column, the formula returns with the hard coded part with surrounded single
quotes.
Here is the actual code:

Doesn't work - returns =SUMPRODUCT('O14':'O22',(X15:X23))/SUM('O14':'O22')
This is the code:
ActiveCell.Offset(i + SemisRowCount - 1, 0).Value = "=sumproduct(" &
SemisBenchWgt & "," & SemisFormula & ")/sum(" & SemisBenchWgt & ")"

Variables
SemisBenchWgt = "O" & i + 4 & ":O" & i + SemisRowCount - 1
SemisFormula = "(R[" & -SemisRowCount + 4 & "]C:R[" & -1 & "]C)"


Works
This is the code:
ActiveCell.Offset(i + SemisRowCount + CompRowCount, 0).Value =
"=sumproduct(" & CompBenchWgt & "," & TgtComp & ")/sum(" & CompBenchWgt & ")"

Variables
CompBenchWgt = "O" & SemisRowCount + i + 5 & ":O" & SemisRowCount +
CompRowCount + i
TgtComp = "X" & SemisRowCount + i + 5 & ":X" & SemisRowCount + CompRowCount
+ i

Any help would be appreciated.
Thanks!