?
Solved

How to write formulas with vba.

Posted on 2011-04-25
8
Medium Priority
?
494 Views
Last Modified: 2012-05-11
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).
0
Comment
Question by:awesomejohn19
7 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35461121
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
 

Author Comment

by:awesomejohn19
ID: 35461606
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
 

Author Comment

by:awesomejohn19
ID: 35461729
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35461814
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35464923
You could also use R1C1 style:

Sheets("Sheet3").Range("C10").Offset(0, j).FormulaR1C1 = "=R[1]C/R[-3]C"
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35700855
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
 
LVL 10

Expert Comment

by:bromy2004
ID: 35929417
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question