+ Reply to Thread
Results 1 to 5 of 5

Copying cell formula with "if blank" not correct, and not coding the cell above

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Copying cell formula with "if blank" not correct, and not coding the cell above

    Following attempt to create a formula in VBA is throwing a syntax error, but sure someone out there can do this.

    PLANNER sheet has weekly sales values for products, with each week eight columns apart.

    CUMULATIVES sheet needs the following Formula in Col E that will take each weekly value, and add the previous week's total to it.

    So for row 3:

    =IF('PLANNER'!K4="",'PLANNER'!L4,'PLANNER'!K4)+E2

    (E2 being the previous week, so E2 will become E3, E4, etc with each iteration).

    Problem 1: Code converts Col 'x' to "Col K", but can't work out how to also set Col 'y' as "Col L"?

    Problem 2: Have added four double quotes for "blank" but it's not working.

    Problem 3: Not sure how to add the "+E2" in the code?

    Please Login or Register  to view this content.
    To be clear, I do not want the Code to post the actual value from the Planner, I want it to post in each cell the Formulae that finds it.

    All solutions, suggestions and recommendations welcome as ever.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 08-17-2020 at 08:27 PM.

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Copying cell formula with "if blank" not correct, and not coding the cell above

    Hi,

    Here's a proposal for you...

    A few notes:
    1. you were using "x" and "y" in the formula. Those are numbers while you need letters
    2. you need 5 (five) """"" for the blank (4) and one (1) to "close" the string
    3. to add the "E2", I would use the .FormulaR1c1 trick...

    Let me know if you have any questions.

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copying cell formula with "if blank" not correct, and not coding the cell above

    You could use the OFFSET function. It's volatile, but if you only use it in a couple hundred rows you should be fine.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 08-17-2020 at 09:57 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: Copying cell formula with "if blank" not correct, and not coding the cell above

    Many thanks to both for the prompt suggestions, which helped solved the problem.

    One question for GC, however. Why does the ColumnLetter need two letter (e.g. 'yy = ColumnLetter')?

    I'm intrigued because I've never come across that before.

    Ochimus.

  5. #5
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Copying cell formula with "if blank" not correct, and not coding the cell above

    Hi Ochimus,

    it doesn't matter.... "yy" is a variable to store the letter.
    I could have written:

    Please Login or Register  to view this content.
    It's just that in you code your use the variables "x" and "y" and to make it simpler (and faster), I simply used "xx" and "yy" instead.


    If you look at your code:
    1. you set numerical values forx and y
    2. you use x to set the value of the variable "ColumnNumber"
    3. you then use "ColumnNumber" to calculate the "ColumnLetter"
    4. you did the same for Y. By doing so "ColumnLetter" has changed.
    5. but it your formula you used "x" and "y" which are numbers... So basically steps 2-4 were useless...

+ 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. I need my formula to post a "blank" cell instead of a "0"
    By 1losthuman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2016, 09:32 PM
  2. [SOLVED] Use of "blank" cell, "0" and "N/A" in a Formula
    By Laurent53 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-10-2015, 10:49 AM
  3. [SOLVED] Keeping particular cell blank if the related cells show "0" or "blank"
    By Anuru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-24-2014, 05:30 AM
  4. Replies: 11
    Last Post: 10-07-2014, 10:06 AM
  5. Replies: 6
    Last Post: 09-25-2013, 01:51 PM
  6. solution for the blank cell using the IF function
    By ragnaedge in forum Excel General
    Replies: 4
    Last Post: 08-23-2013, 11:03 AM
  7. Replies: 0
    Last Post: 11-20-2012, 10:22 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