VBA Insert a formula in a cell

  • Hi, I am using VBA to insert a formula in a cell, I want the formula in I6 is


    my VBA is
    Worksheets("data").Range("I6").Formula = "=if(G6="a",vlookup($b6,sheet1!$d$6:$I$344,6,false),"")"

    but when i run it, when come to G6="a", the message is "expected end of statement".

    how can i solve this problem?



  • Re: VBA Insert a formula in a cell


    You need to double up on the quotes inorder to have quotes within the formula.[vba]Worksheets("data").Range("I6").Formula = "=if(G6=""a"",vlookup($b6,sheet1!$d$6:$I$344,6,false),"""")"[/vba]

  • Re: VBA Insert a formula in a cell

    Aaron Blood posted his method of getting a formula into VBA -


  • Re: VBA Insert a formula in a cell

    I'm having difficulty with a similar formula. The formula I want to paste is:

    =IF('BMR Update Sheet'!BK5=0,"",'BMR Update Sheet'!BK5)

    I need the '5' to be iterated from 1 to 1350, but I'm using 1 to 10 right now to minimize run times.

    My current attempt is:

    Sub NewPaste()
    Dim i As Integer
    Dim Index As String

    i = 1
    Do While i < 10
    Index = "P" & i
    Range(Index).Formula = "=if('BMR Update Sheet'!" + Index + "=" + """""" + "," + """""" + ",'BMR Update Sheet'!" + Index

    i = i + 1
    End Sub

    Any suggestions?

  • Re: VBA Insert a formula in a cell

    Welcome to OzGrid, Jason Trudel.

    The policy on this board is you do not post questions in threads started by other members. Please start your own thread, give it a clear and concise title that clearly summarises your problem and explain your issue fully.

    If you think this, or any other, thread can help clarify your problem you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

    Also, if you post code in a message, you are asked to use Code Tags. These format and indent the code making it easier to read. Code tags are added, when editing a message, simply by highlight the code and clicking the '#' button on the tool bar above the edit window.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!