FormulaR1C1 ?
 

Hi, I'm trying to use VBA to do some automation and was wondering if someone
might be able to help me figure out what Im doing wrong?


In Column O I have a list of #'s with a total at the bottom of the list but
it can always end on a different row

So, I'm trying to take the 1st # in Row O and % it by the total of that
column? and then proceed down the list allocating each from the total -- Can
anyone help?

Thank you


'Sets the %Allocation
With .Range("Q2:Q" & target.Row - 1)
.FormulaR1C1 = "=RC15/R" & target.Row & "C15 * Fees"

.Offset(, 0).FormulaR1C1 = "=ROUND(RC/R[3]C,8)"
End With

FormulaR1C1 ?
 

Try

LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

With .Range("Q1:Q" & LastRow - 1)
.FormulaR1C1 = "=RC15/SUM(R1C15:R" & LastRow & "C15)*Fees"
End With


--
__________________________________
HTH

Bob

"Heather" wrote in message
...
Hi, I'm trying to use VBA to do some automation and was wondering if
someone
might be able to help me figure out what Im doing wrong?


In Column O I have a list of #'s with a total at the bottom of the list
but
it can always end on a different row

So, I'm trying to take the 1st # in Row O and % it by the total of that
column? and then proceed down the list allocating each from the total --
Can
anyone help?

Thank you


'Sets the %Allocation
With .Range("Q2:Q" & target.Row - 1)
.FormulaR1C1 = "=RC15/R" & target.Row & "C15 * Fees"

.Offset(, 0).FormulaR1C1 = "=ROUND(RC/R[3]C,8)"
End With

FormulaR1C1 ?
 

Hi Bob, thanks I think I'm close but it's saying O2/Sum(O2:O7) and really it
should show O2/O7 (because in this example that is the total it's allocating
from)?? How can I tweak it?

"Bob Phillips" wrote:

Try

LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

With .Range("Q1:Q" & LastRow - 1)
.FormulaR1C1 = "=RC15/SUM(R1C15:R" & LastRow & "C15)*Fees"
End With


--
__________________________________
HTH

Bob

"Heather" wrote in message
...
Hi, I'm trying to use VBA to do some automation and was wondering if
someone
might be able to help me figure out what Im doing wrong?


In Column O I have a list of #'s with a total at the bottom of the list
but
it can always end on a different row

So, I'm trying to take the 1st # in Row O and % it by the total of that
column? and then proceed down the list allocating each from the total --
Can
anyone help?

Thank you


'Sets the %Allocation
With .Range("Q2:Q" & target.Row - 1)
.FormulaR1C1 = "=RC15/R" & target.Row & "C15 * Fees"

.Offset(, 0).FormulaR1C1 = "=ROUND(RC/R[3]C,8)"
End With

FormulaR1C1 ?
 

I got it to work :) by tweaking it to:
LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row
With .Range("Q2:Q" & LastRow - 1)
.FormulaR1C1 = "=Round(RC15/R" & LastRow & "C15,8)"
End With

But, now when I go to the next column to multiply Column Q by Fees it's not
ending in the right place and trails 4 extra rows down? Any ideas?


With .Range("R2:R" & target.Row - 1)
.FormulaR1C1 = "=Round(RC17 * Fees,0)"
End With

"Bob Phillips" wrote:

Try

LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

With .Range("Q1:Q" & LastRow - 1)
.FormulaR1C1 = "=RC15/SUM(R1C15:R" & LastRow & "C15)*Fees"
End With


--
__________________________________
HTH

Bob

"Heather" wrote in message
...
Hi, I'm trying to use VBA to do some automation and was wondering if
someone
might be able to help me figure out what Im doing wrong?


In Column O I have a list of #'s with a total at the bottom of the list
but
it can always end on a different row

So, I'm trying to take the 1st # in Row O and % it by the total of that
column? and then proceed down the list allocating each from the total --
Can
anyone help?

Thank you


'Sets the %Allocation
With .Range("Q2:Q" & target.Row - 1)
.FormulaR1C1 = "=RC15/R" & target.Row & "C15 * Fees"

.Offset(, 0).FormulaR1C1 = "=ROUND(RC/R[3]C,8)"
End With

FormulaR1C1 ?
 

you must be Heather S* ? yes :)

in the first block of code you're using lastrow-1 while in the 2nd block
you're using Target.Row-1
I suspect that target isn't set to your total row, hence the mismatch

try

With .Range("R2:R" & LastRow - 1)
.FormulaR1C1 = "=Round(RC17 * Fees,0)"
End With

instead



"Heather" wrote:

I got it to work :) by tweaking it to:
LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row
With .Range("Q2:Q" & LastRow - 1)
.FormulaR1C1 = "=Round(RC15/R" & LastRow & "C15,8)"
End With

But, now when I go to the next column to multiply Column Q by Fees it's not
ending in the right place and trails 4 extra rows down? Any ideas?


With .Range("R2:R" & target.Row - 1)
.FormulaR1C1 = "=Round(RC17 * Fees,0)"
End With

"Bob Phillips" wrote:

Try

LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

With .Range("Q1:Q" & LastRow - 1)
.FormulaR1C1 = "=RC15/SUM(R1C15:R" & LastRow & "C15)*Fees"
End With


--
__________________________________
HTH

Bob

"Heather" wrote in message
...
Hi, I'm trying to use VBA to do some automation and was wondering if
someone
might be able to help me figure out what Im doing wrong?


In Column O I have a list of #'s with a total at the bottom of the list
but
it can always end on a different row

So, I'm trying to take the 1st # in Row O and % it by the total of that
column? and then proceed down the list allocating each from the total --
Can
anyone help?

Thank you


'Sets the %Allocation
With .Range("Q2:Q" & target.Row - 1)
.FormulaR1C1 = "=RC15/R" & target.Row & "C15 * Fees"

.Offset(, 0).FormulaR1C1 = "=ROUND(RC/R[3]C,8)"
End With