Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > VBA formula in macro not working
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3
Default 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!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default 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!



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3
Default 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!




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default 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!






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3
Default 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!







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default 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!









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
macro not working with new sheet Eqa Excel Discussion (Misc queries) 10 July 8th 07 05:10 PM
MACRO IS NOT WORKING AS IT SHOULD jeannie v Excel Worksheet Functions 3 June 21st 07 08:19 AM
macro not working dpolston Excel Discussion (Misc queries) 2 April 19th 07 09:16 PM
Formula within a Macro not working as expected. Pank New Users to Excel 2 March 7th 07 10:31 AM
macro not working Jonathan Cooper Excel Discussion (Misc queries) 1 February 1st 06 10:06 PM


All times are GMT +1. The time now is 02:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement