VBA formula in macro not working

  • Thread starter Thread starter Chloe
  • Start date Start date
C

Chloe

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!
 
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
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?


:

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


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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying Formulas Between Worksheets 2
Sumproduct formula needed 2
Please help with Median formula 1
complex count formulas using multiple criteria in different ranges 4
Increment value in a Formula 7
SUMPRODUCT formula using hours:minutes 12
Sum to the last row in vba 2
2 Columns total division 4

Back
Top