Apply a R1C1 function to a specific range

[Guest]

Hi!
I use the following function:
Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 = "=RC[-2]/RC[10]"
I would to modify the formula to apply it only when RC[-2] is greater than 0.
Thank you!
Alex

 

[Bob Phillips]

Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]/RC[10],"""")"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

It works well but I'm wondering if there is way to put the condition inside
the selection of the Range. By this way, there will not have formula when not
needed.
Thank you!
Alex
--
Alex St-Pierre

Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]/RC[10],"""")"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi!
I use the following function:
Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 = "=RC[-2]/RC[10]"
I would to modify the formula to apply it only when RC[-2] is greater than
0.
Thank you!
Alex

 

[Bob Phillips]

Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]/RC[10],"""")"
Range(Cells(i, 13), Cells(i, 500)).Value = Range(Cells(i, 13), Cells(i,
500)).Value

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

It works well but I'm wondering if there is way to put the condition
inside
the selection of the Range. By this way, there will not have formula when
not
needed.
Thank you!
Alex
--
Alex St-Pierre

Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]/RC[10],"""")"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

message

Hi!
I use the following function:
Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 = "=RC[-2]/RC[10]"
I would to modify the formula to apply it only when RC[-2] is greater
than
0.
Thank you!
Alex

 

[Guest]

Hi Bob,
I would like to let the formulas in the cells when RC[-2] is greater than 0.
So, when RC[-2] is empty, I don't want to show a formula. The result of
RC[-2]/RC[10] = #DIV/0 when RC[-2] equal to 0.

Is it possible to reach the non empty cells of the column RC[-2] using
something like:
Set rng = wks2.Rows(1).Find(What:="SSN", LookAt:=xlWhole)
Then, I could offset the range to RC[2] and apply the formula on the
selected cells range only.
Or is there a SpecialCells function that allow the selection of non empty
cells only?
like: Selection.SpecialCells(xlCellTypeBlanks).Select
Thanks a lot!
Alex
--
Alex St-Pierre

Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]/RC[10],"""")"
Range(Cells(i, 13), Cells(i, 500)).Value = Range(Cells(i, 13), Cells(i,
500)).Value

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

It works well but I'm wondering if there is way to put the condition
inside
the selection of the Range. By this way, there will not have formula when
not
needed.
Thank you!
Alex
--
Alex St-Pierre

Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]/RC[10],"""")"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

message
Hi!
I use the following function:
Range(Cells(i, 13), Cells(i, 500)).FormulaR1C1 = "=RC[-2]/RC[10]"
I would to modify the formula to apply it only when RC[-2] is greater
than
0.
Thank you!
Alex