Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What Does FormulaR1C1 Do? | Excel Programming | |||
What Does FormulaR1C1 Do? | Excel Programming | |||
What Does FormulaR1C1 Do? | Excel Programming | |||
Formular1c1 | Excel Programming | |||
FormulaR1C1 | Excel Programming |