+ Reply to Thread
Results 1 to 9 of 9

Issue with setting a cell to a formula

  1. #1
    Forum Contributor
    Join Date
    10-13-2015
    Location
    California
    MS-Off Ver
    Office 2013
    Posts
    138

    Issue with setting a cell to a formula

    Okay,

    So i have a very log IF formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula works as intended.

    However when i try to work it into my vba script it give me an error "expected end to IF statement" I did not have this issue before I modified the formula to include if E2=4 statements.

    I have tried this as a R1C1 formula and as just formula and i get the same error either way. Did i botch something in the formula or is there an issue with the VBA. Its probably something very simple ive just been looking at this and similar code for too long right and i feel like i am just overlooking it. A second set of eyes might help.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Issue with setting a cell to a formula

    Try this.
    Please Login or Register  to view this content.
    By the way, I think you might want to consider creating some named ranges because there's a lot of iteration in that code.

    For example if you created a named ranges called LOG_W, LOG_Z and LOG_AK for 'WIP LOG'!$W:$W, 'WIP LOG'!$Z:$Z and 'WIP LOG'!$AK:$AK respectively the code/formula would look something like this.
    Please Login or Register  to view this content.
    Last edited by Norie; 03-23-2017 at 12:19 PM.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-13-2015
    Location
    California
    MS-Off Ver
    Office 2013
    Posts
    138

    Re: Issue with setting a cell to a formula

    That instantly sets my cells in H2 to false and doesn't set them to the formula.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Issue with setting a cell to a formula

    It's too long, VBA has a limit on the number of characters it can enter into a cell.

    Can you turn that formula into a NAMED FORMULA instead?

    1) Click on the cell that exact dynamic formula would go into
    2) CTRL+F3 to open the name wizard
    3) Create a new name called MyFormula and put that formula in the RefersTo: field

    4) Now, back in the original cell you can call this lengthy formula with a simple =MyFormula

    Obviously that very short formula would work fine being entered by VBA.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Issue with setting a cell to a formula

    The code/formula works fine for me.

  6. #6
    Forum Contributor
    Join Date
    10-13-2015
    Location
    California
    MS-Off Ver
    Office 2013
    Posts
    138

    Re: Issue with setting a cell to a formula

    @JBeaucaire That worked perfectly

    I have never used the name wizard like that, its nifty.

    Thank you!

    @Norie thats odd i am not sure why mine didnt, i copied it directly in.

  7. #7
    Forum Contributor
    Join Date
    10-13-2015
    Location
    California
    MS-Off Ver
    Office 2013
    Posts
    138

    Re: Issue with setting a cell to a formula

    I just noticed an issue @JBeaucaire when I do that the formula becomes sheet specific. if i look at the formula it becomes
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which for one didnt keep the whole formula and two if i try to use it on another sheet it still refers back to this sheet M27532-1.

  8. #8
    Forum Contributor
    Join Date
    10-13-2015
    Location
    California
    MS-Off Ver
    Office 2013
    Posts
    138

    Re: Issue with setting a cell to a formula

    @Norie you code worked i am not sure what i did the first time i tried but I added one line to it and its not working again take a look below. (application-defined or object-defined error)

    I will take your suggestion and name the ranges and change all of my code tomorrow as it does look a lot cleaner.

    Please Login or Register  to view this content.
    Last edited by Lsxtrkiller; 03-23-2017 at 03:39 PM.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,619

    Re: Issue with setting a cell to a formula

    Can't immediately see what you changed/added but I'll maybe have a closer look later.

    By the way, I think you might be able to simplify your formula some.

    For example, as far as I can see anyway, something like =IF($E2=0,IF($D2=0, ... is basically the same as IF (AND($E2=0, $D2=0), ...

    You have a lot of these 'double IFs' in the formula and I think if you were able to replace them with ANDs it might tidy the code up a little further, and make it more readable.
    Last edited by Norie; 03-23-2017 at 04:00 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Small Issue with Type Mismatch and Invalid Qualifier when setting variable value from cell
    By TomToms in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2014, 08:03 AM
  2. [SOLVED] An issue with setting up hyperlinks within the workbook
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-16-2014, 03:08 AM
  3. WinAPI Printer Setting Issue
    By tom.hogan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2013, 06:19 PM
  4. Issue with Setting target font name
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-28-2012, 11:19 AM
  5. [SOLVED] Combobox userform issue with setting WS
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-15-2012, 07:02 AM
  6. Replies: 0
    Last Post: 10-26-2011, 06:18 AM
  7. Setting time issue?
    By tompich in forum Excel General
    Replies: 2
    Last Post: 10-10-2007, 10:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1