Stop Macro If Blank

[id10t]

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.

[id10t]

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.

[royUK]

You don't need to select all those ranges

[abousetta]

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.

[id10t]

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.

[abousetta]

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

[id10t]

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!

[abousetta]

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

[id10t]

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.

[abousetta]

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

[id10t]

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!

[abousetta]

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

abousetta