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


    if(G6="a",vlookup($b6,sheet1!$d$6:$i$344,6,false),"")


    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?


    Thanks!


    Linda

  • Re: VBA Insert a formula in a cell


    Hi,


    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 -


    http://www.ozgrid.com/forum/sh…t=42204&highlight=formula

  • 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
    Loop
    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!