+ Reply to Thread
Results 1 to 12 of 12

Stop Macro If Blank

  1. #1
    Registered User
    Join Date
    09-18-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Stop Macro If Blank

    Hi.
    I'm finally nearing the end of what has been a difficult and frustrating project for me.

    One remaining issue is that I need the macro below to only copy values in column N that are nonblank.
    The macro then takes the values in column N and performs a Text to Columns (this works properly, but blanks return an error).

    Sorry if this is confusing, this is a pretty advanced project for me. The code below is probably inefficient, but it has been getting the job done.

    Please Login or Register  to view this content.
    Last edited by id10t; 09-18-2011 at 09:48 PM.

  2. #2
    Registered User
    Join Date
    09-18-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stop Macro If Blank

    If possible, I'd love to be able to have the macro ignore blanks and proceed to the next cell, rather than stopping entirely due to blanks...but anything is better than erroring out.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Stop Macro If Blank

    You don't need to select all those ranges
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Stop Macro If Blank

    Hi,

    This maybe a shorter version of your code. I began with "On Error Resume Next" so it can skip the problematic parts of your code. This seems to only happen if the whole column is empty and you are trying to use TextToColumns.

    Hope this helps.

    abousetta

    Please Login or Register  to view this content.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  5. #5
    Registered User
    Join Date
    09-18-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stop Macro If Blank

    Quote Originally Posted by abousetta View Post
    Hi,

    This maybe a shorter version of your code. I began with "On Error Resume Next" so it can skip the problematic parts of your code. This seems to only happen if the whole column is empty and you are trying to use TextToColumns.

    Hope this helps.

    abousetta

    Please Login or Register  to view this content.
    The issue with this is that it seems to post my formulas, and break them during the Text to Columns.
    My original code copied the formula results and re-posted the values to a separate column.

    I've attached my entire workbook.
    It is set up to automate scoring for an NFL Pick Em contest. I have this code above set for my Calculate macro and my original code as Original.

    As for the tabs...
    1 is completed and works as desired with the original code.
    2 is in progress.
    3 is set up for an example of scores, with info missing.

    I want the code to be able to run without column N (tied to column B) necessarily being completed. Thus, being able to run the calculations of scores without the week necessarily being completed.

    I hope this makes at least a little sense.
    Any help is always greatly appreciated.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Stop Macro If Blank

    I'm not completely following what is going on in the workbook, but maybe this will resolve the issue:

    Please Login or Register  to view this content.
    If its not, could you please simplify the workbook for me and just leave the needed sheets and a before/ after sheet so I can see what the expected results should look like.

    abousetta

  7. #7
    Registered User
    Join Date
    09-18-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stop Macro If Blank

    Here's another attempt at an explanation.

    Columns:
    A = Teams that are playing each other
    B = Final score of the game
    C = Ryan's prediction
    D = Points awarded for pick to Ryan (formula)
    E = Bill's Prediction
    F = Points awarded for pick to Bill (formula)
    G = Hawk's Prediction
    H = Points awarded for pick to Hawk (formula)
    I = Winning team (formula)
    J = Reference to teams that are playing each other (A), with the @ symbol substituted for a space to enable easy Text to Column function (formula)
    K:L = Teams split by Text to Column (VBA, Calculation macro)
    M = Reference to final score (B) with the - substituted for a space to enable easy Text to Column function (formula)
    N:O = Score split by Text to Column (VBA, Calculate macro)
    P = Absolute value of the difference in Final Score (N-O), set up with an ISBLANK to prevent 0's from appearing when data has yet to be entered
    Q:AB are all populated during the Calculate macro
    Q:R = Text to Column result from W, splits Ryan's Team and Score prediction into 2 separate cells
    S:T = Text to Column result from X, splits Bill's Team and Score prediction into 2 separate cells
    U:V = Text to Column result from Y, splits Hawk's Team and Score prediction into 2 separate cells
    W = Ryan's prediction (X for Bill, Y for Hawk) - During the Calculate macro the values are copied/pasted from Column C (E for Bill, G for Hawk) and pasted into column Q (S and U) then split via Text to Column (VBA, Calculate macro)
    W:AB are populated during the macro process and then some are condensed, as a workaround due to merged cells in A:H

    I've attached a workbook with Before, In Progress and Completed tabs.
    The formulas for some of the Pts columns have been replaced on the In Progress tab in order to show my desired results.

    Basically, I use the Calculate macro to populate columns K:O, and Q:AB...and then my formulas in D, F, H, I, J, M and P complete the rest of what I need.
    The formulas in C/E/G 21/22/23 are not impacted by the macro.

    The Calculate macro works fine, if all of the relevant cells in column B are completed prior to running it. It has been run on the Completed tab.

    Thanks again!
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Stop Macro If Blank

    Which sheet do you run the 'calculate' macro on to get the results in the 'completed' sheet. I ran it on both the 'Before' and the 'In Progress' and both are not giving me the same results as the 'Completed'. Therefore I'm not sure how to get from the start (assuming its one of the first two sheets) to the finish (the 'Completed' Sheet).

    abousetta

  9. #9
    Registered User
    Join Date
    09-18-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stop Macro If Blank

    Quote Originally Posted by abousetta View Post
    Which sheet do you run the 'calculate' macro on to get the results in the 'completed' sheet. I ran it on both the 'Before' and the 'In Progress' and both are not giving me the same results as the 'Completed'. Therefore I'm not sure how to get from the start (assuming its one of the first two sheets) to the finish (the 'Completed' Sheet).

    abousetta
    You know what...I'm not sure what my thought process was last night on this.
    The macro only seems to return an error if ALL of the values for the Scores fields under column B are blank.
    If one or more are completed, it works. I did not test that scenario. It works fine under the In Progress tab and fails under the Before tab.
    So, I guess all that I need is coding to stop the macro if ALL of the Scores fields are blank...so if the macro were to run under the Before tab, essentially nothing would happen.

  10. #10
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Stop Macro If Blank

    Maybe this will give an idea on how you want to proceed with:

    Please Login or Register  to view this content.
    If there is at least one cell in column A that is populated then you get a Msgbox. If not, then nothing happens.

    abousetta

  11. #11
    Registered User
    Join Date
    09-18-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stop Macro If Blank

    Quote Originally Posted by abousetta View Post
    Maybe this will give an idea on how you want to proceed with:

    Please Login or Register  to view this content.
    If there is at least one cell in column A that is populated then you get a Msgbox. If not, then nothing happens.

    abousetta
    I modified the code to
    Please Login or Register  to view this content.
    And it seems to work like a charm. (B1 always says "Score" and B25 is the bottom-most cell through the workbook.

    Thanks!
    Thanks!

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    re: Stop Macro If Blank

    Glad it all worked out. Thanks for marking the thread as Solved.

    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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