How to write formulas with vba.

I want to write formulas with vba on a spreadsheet. I couldnt make the following work.

So there is a for statement which is supposed to from j=0 to 10. So on each ("C10"), I would like to write the formula ("C11").offset(0,j)/("C7").offset(0,j). So again, I dont want the exact number but I would like to have the formula on that cell ("C10")


I tried the following but it doesnt work.
Sheets("Sheet3").Range("C10").Offset(0, j).Formula = "=(Sheet3!C"&," & Sheets("Sheet3").Range("C11").Offset(0, j).Value & "/" & Sheets("Sheet3").Range("C7").Offset(0, j).
awesomejohn19Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
You could also use R1C1 style:

Sheets("Sheet3").Range("C10").Offset(0, j).FormulaR1C1 = "=R[1]C/R[-3]C"
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi,
You have the right idea with the formula property. However, you need to set it as a string, which means that if your string contains double quotes, you need to double them up like this ("") so that they end up as double quotes in your string. Something like this:

Formula = "(""C11"").offset(0," & j & ")/(""C7"").offset(0," & j & ")"

HTH

Philippe
0
 
awesomejohn19Author Commented:
Thanks for your response. Are you sure this is correct? When I do this I will get someting like C11.offset(0,J) which doesnt seem to be a valid worksheet function. When I type this I get #Name (I use C11.offset(0,2) for example. But what I need is just C11/C7. then D11/D7 for example.

Maybe I didnt understand what you mean completely. I am little confused :)

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
awesomejohn19Author Commented:
Ok, this worked

Sheets("Sheet3").Range("C10").Offset(0, j).Formula = "=" & Sheets("Sheet3").Range("C11").Offset(0, j).Address & "/" & Sheets("Sheet3").Range("C7").Offset(0, j).Address
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
You write "on each C10". There is only one C10. Am I understanding correctly that you want to set formulae horizontally from left to right, updating the column letter as you move to the right? It is easiest to manually drag the formula on the far left , excel will automatically fill cells to the right accordingly, updating the formula as it goes. To do this in code, try something like this (using the integer values for the column letters):

  For Col = Asc("C") To Asc("D")
    Sheets("Sheet1").Range(Chr(Col) & "10").Formula = "=" & Chr(Col) & "7/" & Chr(Col) & "11"
  Next Row
0
 
duncanb7Commented:
VBA Example-1
Set rg = Cells(1, m.Column)
 Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"

Example-2
 range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
  range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"

You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
0
 
bromy2004Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.