Assigning a formula to a range
 

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.

Assigning a formula to a range
 

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.

Assigning a formula to a range
 

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.

Assigning a formula to a range
 

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.

Assigning a formula to a range
 

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

Assigning a formula to a range
 

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("D11:D159")
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

Assigning a formula to a range
 

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("D11:D159")
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