Results 1 to 6 of 6

Thread: Need help with VBA

  1. #1
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Need help with VBA

    Hi All
    I am using the code below to paste the formula in a cell. I am so very close. I can't get ="" to paste.

    The pasted formula that I'm trying to achieve: =IF(C4="",F3+CK3,IF(C3="",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,"")))

    I need C4="" and C3=""

    Code:
     
    Target.Formula = "=IF(C" & ActiveCell.Row & "="",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"
    The above code results: =IF(C4=",F3+ CK3,If(C3=",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

    Code:
     
    Target.Formula = "=IF(C" & ActiveCell.Row & "="""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"
    The above code with double "" results in a runtime error "Method formula of object range failed.



    I thought for sure this would work:
    Code:
    Target.Formula = "=IF(C" & ActiveCell.Row & "=""" & ",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "=""" & ",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"
    The above code results: =IF(C4=",F3+ CK3,If(C3=",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

    Again, I need C4="" and C3=""

    Spent 2 hrs trying to figure this out.

    Thanks in advance! Taking a long break.
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  2. #2
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    900
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with VBA

    You should double each " that is to be visible in the formula. So if the formula contains "", your VBA should show """" at that location. This does not include the starting and ending quotes of the string expression itself. TO display a messagebox showing "", you need to add two additional "'s like so:
    Code:
    MsgBox """"""
    So your formula becomes:
    Code:
    Target.Formula = "=IF(C" & ActiveCell.Row & "="""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with VBA

    Ah!
    I thought the double quotes needed quotes. I get it now!

    Thank you very much!!

    Russ
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  4. #4
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with VBA

    Oops!
    I thought it worked but its giving meC4= """

    Code:
    Target.Formula = "=IF(C" & ActiveCell.Row & "="""""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"


    Result:=IF(C4=""",F3+ CK3,If(C3=""",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

    Thanks!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  5. #5
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    900
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with VBA

    You've added too many " and one ) too little this is correct:
    Code:
    Target.Formula = "=IF(C" & ActiveCell.Row & "="""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ","""")))"
    Last edited by jkpieterse; Jul 31st, 2019 at 05:25 AM.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  6. #6
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    422
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with VBA

    I did notice that I was missing a ). I thought that was strange. I thought each " needed "" so I was doing """""" and then added the (.
    Your fix is works!


    I thought this was weird.
    As a result from the old code, this is the formula that was in the cell with a missing ) and it didn't give me an error.
    IF(C4=""",F3+ CK3,If(C3=""",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

    Thanks again!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •