Stop Macro If Blank

**id10t** · 09-18-2011, 11:41 AM

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.

Sub Calculate()
    Range("K:L,N:O,Q:V,X:AB").Select
    Selection.ClearContents
    Columns("A:H").Select
    Selection.Copy
    Columns("W:W").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("W:X").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("X:X,Z:Z,AB:AB").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Copy
    Columns("K:K").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("M:M").Select
    Selection.Copy
    Columns("N:N").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("A1").Select
    Columns("W:W").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("X:X").Select
    Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
    Columns("Y:Y").Select
    Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
    Range("A1").Select
End Sub

**id10t** · 09-18-2011, 11:55 AM

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** · 09-18-2011, 12:59 PM

You don't need to select all those ranges

**abousetta** · 09-18-2011, 01:02 PM

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

Option Explicit

Sub Calculate()

On Error Resume Next
    Range("K:L,N:O,Q:V,X:AB").ClearContents
    Columns("A:H").Copy Destination:=Columns("W:AD")
    Columns("W:X").Delete Shift:=xlToLeft
    Range("X:X,Z:Z,AB:AB").Delete Shift:=xlToLeft
    Columns("J:J").Copy Destination:=Columns("K:K")
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("M:M").Copy Destination:=Columns("N:N")
    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("W:W").TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("X:X").TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
    Columns("Y:Y").TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
End Sub

**id10t** · 09-18-2011, 02:23 PM

Originally Posted by 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

Option Explicit

Sub Calculate()

On Error Resume Next
    Range("K:L,N:O,Q:V,X:AB").ClearContents
    Columns("A:H").Copy Destination:=Columns("W:AD")
    Columns("W:X").Delete Shift:=xlToLeft
    Range("X:X,Z:Z,AB:AB").Delete Shift:=xlToLeft
    Columns("J:J").Copy Destination:=Columns("K:K")
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("M:M").Copy Destination:=Columns("N:N")
    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("W:W").TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("X:X").TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
    Columns("Y:Y").TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
End Sub

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** · 09-18-2011, 04:15 PM

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

Option Explicit

Sub Calculate()

On Error Resume Next
    Range("K:L,N:O,Q:V,X:AB").ClearContents
    Columns("A:H").Value.Copy Destination:=Columns("W:AD")
    Columns("W:X").Delete Shift:=xlToLeft
    Range("X:X,Z:Z,AB:AB").Delete Shift:=xlToLeft
    Columns("J:J").Value.Copy Destination:=Columns("K:K")
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("M:M").Value.Copy Destination:=Columns("N:N")
    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("W:W").TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("X:X").TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
    Columns("Y:Y").TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(1, 1 _
        ), TrailingMinusNumbers:=True
End Sub

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** · 09-18-2011, 06:54 PM

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!