VBA skip to certain section in Macro if cell is blank and issue different msg box

willow1985

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
929
Office Version
  1. 365
Platform
  1. Windows
Hello I have the below code (I am only providing the bottom half of the code as it is long). What I would like to do is where I left the first gap in the code I want the macro to check if Cell B16 is blank on sheet: "weekly process", if yes I want it to skip to the next blank and run from there but issue a different message box at the end (instread of update complete it would say no results). Can this be done?

Code: 
Cells.Select
    lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    Lastrow = ActiveSheet.Cells(1, 1).End(xlDown).Row
    Selection.AutoFilter
 ActiveSheet.Range("A1", ActiveSheet.Cells(Lastrow, lastCol)).AutoFilter Field:=12, Criteria1:="No"
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Sheets("Headers").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("CY").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Sheets("CY").Select
    Range("A2:H1000000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Reprocess").Select
    Range("B16").Select
    ActiveSheet.Paste






****check if B16 is blank, if no continue, if yes skip ahead*******






        Range("A15:I1000000").Select
    ActiveWorkbook.Worksheets("Weekly Reprocess").Sort.SortFields.Add Key:=Range( _
        "B16:B1000000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Weekly Reprocess").Sort
        .SetRange Range("A15:I1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Range("A16").Select
    Application.CutCopyMode = False
    
ActiveCell.FormulaR1C1 = "1"
If Cells(Rows.Count, "B").End(xlUp).Row > 16 Then
    Range("A16").AutoFill Destination:=Range("A16:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillSeries
End If
    Range("A16").Select
Range("A16", Range("A16").End(xlDown).End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   


******Cell B16 was blank, continue from here*******




Range("A15").Select
    Application.DisplayAlerts = False
Sheets("Report").Select
 ActiveWindow.SelectedSheets.Delete
Sheets.Add().Name = "Report"
Application.DisplayAlerts = True
    
Sheets("Report").Sort.SortFields.Clear
Sheets("CY").Sort.SortFields.Clear
Sheets("DATA").Sort.SortFields.Clear
Sheets("Weekly Reprocess").Sort.SortFields.Clear
Sheets("wk 53").Sort.SortFields.Clear
Sheets("Headers").Sort.SortFields.Clear


Sheets("Headers").Visible = False
Sheets("CY").Visible = False
Sheets("Report").Visible = False
Sheets("DATA").Visible = False
        
        Sheets("Weekly Reprocess").Select
        
        Range("A1").Select




MsgBox "Update Complete"      *********Different message box "No Results"************


End Sub

Thank you for all of your help

Carla
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Put this at the start of that section
Code: 
If Range("B16") <> "" Then
   Msg = "Update Complete"
and this at the end
Code: 
End If
Then for the messagebox use
Code: 
If Msg = "" Then Msg = "No result"
MsgBox Msg

Also in future when posting code please use code tags, the # icon in the reply window.
 
Upvote 0
Try the following, I cleaned your code a bit.

Code: 
Sub Macro()
  Dim LastCol As Long, LastRow As Long
  LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
  LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  ActiveSheet.Range("A1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=12, Criteria1:="No"
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.ClearContents
  Sheets("Headers").Rows("1:1").Copy
  Range("A1").Select
  ActiveSheet.Paste
  Columns("A:A").Select
  Selection.SpecialCells(xlCellTypeBlanks).Select
  Application.CutCopyMode = False
  Selection.EntireRow.Delete
  Range("A2:H1000000").Copy
  Sheets("Weekly Reprocess").Select
  Range("B16").Select
  ActiveSheet.Paste


  If Range("B16").Value <> "" Then
    '****check if B16 is blank, if no continue, if yes skip ahead*******
    Range("A15:I1000000").Select
    ActiveWorkbook.Worksheets("Weekly Reprocess").Sort.SortFields.Add Key:=Range( _
    "B16:B1000000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Weekly Reprocess").Sort
      .SetRange Range("A15:I1000000")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    
    Range("A16").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1"
    If Cells(Rows.Count, "B").End(xlUp).Row > 16 Then
    Range("A16").AutoFill Destination:=Range("A16:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillSeries
    End If
    Range("A16", Range("A16").End(xlDown).End(xlToRight)).Select
    With Selection
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
    End With
    With Selection.Borders
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .Weight = xlThin
    End With
    With Selection.Font
      .Name = "Calibri"
      .Size = 11
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .ThemeColor = xlThemeColorLight1
      .TintAndShade = 0
      .ThemeFont = xlThemeFontMinor
    End With
    
    MsgBox "No Results"
  Else
    '******Cell B16 was blank, continue from here*******
    Range("A15").Select
    Application.DisplayAlerts = False
    Sheets("Report").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets.Add().Name = "Report"
    Application.DisplayAlerts = True
    
    Sheets("Report").Sort.SortFields.Clear
    Sheets("CY").Sort.SortFields.Clear
    Sheets("DATA").Sort.SortFields.Clear
    Sheets("Weekly Reprocess").Sort.SortFields.Clear
    Sheets("wk 53").Sort.SortFields.Clear
    Sheets("Headers").Sort.SortFields.Clear
    
    Sheets("Headers").Visible = False
    Sheets("CY").Visible = False
    Sheets("Report").Visible = False
    Sheets("DATA").Visible = False
    Sheets("Weekly Reprocess").Select
    Range("A1").Select
    
    MsgBox "Update Complete"
  End If
End Sub
 
Upvote 0
This doesnt work.... I am going to look at this differently and I just need help with one part.
Macro will run normally until it hits this condition:

Code: 
If Range(“B16”).Value <> “” Then

IF TRUE, it will run:

Range("A15").Select
    Application.DisplayAlerts = False
Sheets("Report").Select
 ActiveWindow.SelectedSheets.Delete
Sheets.Add().Name = "Report"
Application.DisplayAlerts = True
    
Sheets("Report").Sort.SortFields.Clear
Sheets("CY").Sort.SortFields.Clear
Sheets("DATA").Sort.SortFields.Clear
Sheets("Weekly Reprocess").Sort.SortFields.Clear
Sheets("wk 53").Sort.SortFields.Clear
Sheets("Headers").Sort.SortFields.Clear
 
Sheets("Headers").Visible = False
Sheets("CY").Visible = False
Sheets("Report").Visible = False
Sheets("DATA").Visible = False
        
        Sheets("Weekly Reprocess").Select
        
        Range("A1").Select
MsgBox "No Results" 

AND EXIT SUB 

IF FALSE (B16 is not blank) it will run:

        Range("A15:I1000000").Select
    ActiveWorkbook.Worksheets("Weekly Reprocess").Sort.SortFields.Add Key:=Range( _
        "B16:B1000000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Weekly Reprocess").Sort
        .SetRange Range("A15:I1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Range("A16").Select
    Application.CutCopyMode = False
    
ActiveCell.FormulaR1C1 = "1"
If Cells(Rows.Count, "B").End(xlUp).Row > 16 Then
    Range("A16").AutoFill Destination:=Range("A16:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillSeries
End If
    Range("A16").Select
Range("A16", Range("A16").End(xlDown).End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   



Range("A15").Select
    Application.DisplayAlerts = False
Sheets("Report").Select
 ActiveWindow.SelectedSheets.Delete
Sheets.Add().Name = "Report"
Application.DisplayAlerts = True
    
Sheets("Report").Sort.SortFields.Clear
Sheets("CY").Sort.SortFields.Clear
Sheets("DATA").Sort.SortFields.Clear
Sheets("Weekly Reprocess").Sort.SortFields.Clear
Sheets("wk 53").Sort.SortFields.Clear
Sheets("Headers").Sort.SortFields.Clear
 
Sheets("Headers").Visible = False
Sheets("CY").Visible = False
Sheets("Report").Visible = False
Sheets("DATA").Visible = False
        
        Sheets("Weekly Reprocess").Select
        
        Range("A1").Select
 
 
MsgBox "Update Complete" 
 
End Sub


How would I structure this?


I do not know if I am using your code tags properly. I am very new to using message boards. I usually stick to mostly excel/office programs so communicating via message board is a new experience. Thank you for your patience.
 
Last edited by a moderator:
Upvote 0
I see the # you referenced now for code. Yes I will use this going forward. Thank you Fluff
 
Upvote 0
To insert code tags click the # icon in the reply (not the # key on you keyboard) & you will see tags like
Code: 
[/ CODE] then paste your code between them.
Also were you talking to me or to Dante? If me, did you try Dante's code?
 
Upvote 0
I tried Dante's code and it was not what I was looking for. I provided a new way of looking at it in hopes this helps explain what exactly I am trying to do. If either one of you could help me finish it off it would be greatly appreciated.
I am unsure of how to do conditions, if this then do that etc etc (the proper statements).

Thank you

Carla
 
Upvote 0
Code:
If Range(“B16”).Value <> “” Then

IF B16 IS BLANK

CODE
MsgBox "No Results"
Exit Sub

IF B16 IS NOT BLANK

CODE
MsgBox "Update Complete"
End Sub
Code:
 
Upvote 0
In that case did you try my suggestion?
 
Upvote 0
I do not understand your previous suggestion, If Msg = "" ?? there would be no Msg that is blank....

Anyway is there a way to do the above structure I mentioned?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
324
Micron
M
A
  • Question Question
Save as dialogue box for excel, but pre populating filename from source word document
Replies
8
Views
282
RoryA
RoryA
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top