How to write formulas with vba.

Posted on 2011-04-25
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).
Question by:awesomejohn19
    LVL 9

    Expert Comment

    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 & ")"



    Author Comment

    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 :)


    Author Comment

    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
    LVL 9

    Expert Comment

    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
    LVL 85

    Accepted Solution

    You could also use R1C1 style:

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

    Expert Comment

    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)"

     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
    LVL 10

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Title # Comments Views Activity
    Excel file corrupted. 13 28
    how to link excel with word 6 29
    simple formula but cannot get it work. 5 24
    Excel Formatting test 26 23
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now