I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
to a range "B11:K158".

I know that we can do this using loop.

However, Can I do that in VBA without using loop, with appriopriate and
relative cell refrences . for example A11 references B11 cells and D11 cells.

Dim myRange As Range
Set myRange = Range("B11:K158")
myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Vagabound_s" wrote:

> I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
> to a range "B11:K158".
>
> I know that we can do this using loop.
>
> However, Can I do that in VBA without using loop, with appriopriate and
> relative cell refrences . for example A11 references B11 cells and D11 cells.

Thank you! it really helped and saved lot of efforts writing loops.

"Barb Reinhardt" wrote:

> Dim myRange As Range
> Set myRange = Range("B11:K158")
> myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
>
> --
> HTH,
> Barb Reinhardt
>
> If this post was helpful to you, please click YES below.
>
>
>
> "Vagabound_s" wrote:
>
> > I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
> > to a range "B11:K158".
> >
> > I know that we can do this using loop.
> >
> > However, Can I do that in VBA without using loop, with appriopriate and
> > relative cell refrences . for example A11 references B11 cells and D11 cells.

Just one more thing on this:

What if my reference cells happen to be in another sheet. I tried using
below code, but it did not work. Is there any other syntax?

myRange.FormulaR1C1 =
"=IF(Sheet1!RC[-1]=""Void"",""Void"",ROUND(Sheet1!RC[2]*80%,1))"



"Vagabound_s" wrote:

> Thank you! it really helped and saved lot of efforts writing loops.
>
> "Barb Reinhardt" wrote:
>
> > Dim myRange As Range
> > Set myRange = Range("B11:K158")
> > myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
> >
> > --
> > HTH,
> > Barb Reinhardt
> >
> > If this post was helpful to you, please click YES below.
> >
> >
> >
> > "Vagabound_s" wrote:
> >
> > > I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
> > > to a range "B11:K158".
> > >
> > > I know that we can do this using loop.
> > >
> > > However, Can I do that in VBA without using loop, with appriopriate and
> > > relative cell refrences . for example A11 references B11 cells and D11 cells.

What happened when you tried it?

What sheet was myRange on?
What address was myRange?



Vagabound_s wrote:
>
> Just one more thing on this:
>
> What if my reference cells happen to be in another sheet. I tried using
> below code, but it did not work. Is there any other syntax?
>
> myRange.FormulaR1C1 =
> "=IF(Sheet1!RC[-1]=""Void"",""Void"",ROUND(Sheet1!RC[2]*80%,1))"
>
> "Vagabound_s" wrote:
>
> > Thank you! it really helped and saved lot of efforts writing loops.
> >
> > "Barb Reinhardt" wrote:
> >
> > > Dim myRange As Range
> > > Set myRange = Range("B11:K158")
> > > myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
> > >
> > > --
> > > HTH,
> > > Barb Reinhardt
> > >
> > > If this post was helpful to you, please click YES below.
> > >
> > >
> > >
> > > "Vagabound_s" wrote:
> > >
> > > > I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
> > > > to a range "B11:K158".
> > > >
> > > > I know that we can do this using loop.
> > > >
> > > > However, Can I do that in VBA without using loop, with appriopriate and
> > > > relative cell refrences . for example A11 references B11 cells and D11 cells.

--

Dave Peterson

Hi Dave, thanks for your prompt response, here is full code:

Dim myRange As Range
Set myRange = Sheet3.Range("B11:B159")
myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
Set myRange = Sheet3.Range("C11:C159")
myRange.FormulaR1C1 = "=IF(RC[-2]=""Void"",""Void"",ROUND(RC[1]*20%,1))"
Set myRange = Sheet3.Range("D11159")
myRange.FormulaR1C1
"=IF(Jobs!RC[-3]=""Void"",""Void"",IF(ISNUMBER(Jobs!RC[4]),Jobs!RC[4],0))"

when I run this it gives and error "Compiler error: Invalid use of property"

"Dave Peterson" wrote:

> What happened when you tried it?
>
> What sheet was myRange on?
> What address was myRange?
>
>
>
> Vagabound_s wrote:
> >
> > Just one more thing on this:
> >
> > What if my reference cells happen to be in another sheet. I tried using
> > below code, but it did not work. Is there any other syntax?
> >
> > myRange.FormulaR1C1 =
> > "=IF(Sheet1!RC[-1]=""Void"",""Void"",ROUND(Sheet1!RC[2]*80%,1))"
> >
> > "Vagabound_s" wrote:
> >
> > > Thank you! it really helped and saved lot of efforts writing loops.
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > Dim myRange As Range
> > > > Set myRange = Range("B11:K158")
> > > > myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
> > > >
> > > > --
> > > > HTH,
> > > > Barb Reinhardt
> > > >
> > > > If this post was helpful to you, please click YES below.
> > > >
> > > >
> > > >
> > > > "Vagabound_s" wrote:
> > > >
> > > > > I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
> > > > > to a range "B11:K158".
> > > > >
> > > > > I know that we can do this using loop.
> > > > >
> > > > > However, Can I do that in VBA without using loop, with appriopriate and
> > > > > relative cell refrences . for example A11 references B11 cells and D11 cells.
>
> --
>
> Dave Peterson
>

Dave,

Thanks raising doubt, I revisited the code and found that I missed the "="
sign in my origional code.

"Vagabound_s" wrote:

> Hi Dave, thanks for your prompt response, here is full code:
>
> Dim myRange As Range
> Set myRange = Sheet3.Range("B11:B159")
> myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
> Set myRange = Sheet3.Range("C11:C159")
> myRange.FormulaR1C1 = "=IF(RC[-2]=""Void"",""Void"",ROUND(RC[1]*20%,1))"
> Set myRange = Sheet3.Range("D11159")
> myRange.FormulaR1C1
> "=IF(Jobs!RC[-3]=""Void"",""Void"",IF(ISNUMBER(Jobs!RC[4]),Jobs!RC[4],0))"
>
> when I run this it gives and error "Compiler error: Invalid use of property"
>
> "Dave Peterson" wrote:
>
> > What happened when you tried it?
> >
> > What sheet was myRange on?
> > What address was myRange?
> >
> >
> >
> > Vagabound_s wrote:
> > >
> > > Just one more thing on this:
> > >
> > > What if my reference cells happen to be in another sheet. I tried using
> > > below code, but it did not work. Is there any other syntax?
> > >
> > > myRange.FormulaR1C1 =
> > > "=IF(Sheet1!RC[-1]=""Void"",""Void"",ROUND(Sheet1!RC[2]*80%,1))"
> > >
> > > "Vagabound_s" wrote:
> > >
> > > > Thank you! it really helped and saved lot of efforts writing loops.
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > Dim myRange As Range
> > > > > Set myRange = Range("B11:K158")
> > > > > myRange.FormulaR1C1 = "=IF(RC[-1]=""Void"",""Void"",ROUND(RC[2]*80%,1))"
> > > > >
> > > > > --
> > > > > HTH,
> > > > > Barb Reinhardt
> > > > >
> > > > > If this post was helpful to you, please click YES below.
> > > > >
> > > > >
> > > > >
> > > > > "Vagabound_s" wrote:
> > > > >
> > > > > > I want to assign a formula say, "=IF(A11="Void","Void",ROUND(D11*80%,1))"
> > > > > > to a range "B11:K158".
> > > > > >
> > > > > > I know that we can do this using loop.
> > > > > >
> > > > > > However, Can I do that in VBA without using loop, with appriopriate and
> > > > > > relative cell refrences . for example A11 references B11 cells and D11 cells.
> >
> > --
> >
> > Dave Peterson
> >