| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Assigning a constant to a range name | Excel Programming | |||
| Assigning a hotkey to run only from a given range | Excel Programming | |||
| Assigning Formula to a Range | Excel Programming | |||
| Assigning Range to Array | Excel Programming | |||
| Assigning a Range to | Excel Programming | |||
![Vagabound_s[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
