Page 1 of 3 123 LastLast
Results 1 to 10 of 23

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

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 Fluff; Aug 13th, 2019 at 09:36 AM. Reason: Added code tags

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

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

    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
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Jul 2019
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 Fluff; Aug 13th, 2019 at 10:31 AM. Reason: added code tags

  5. #5
    Board Regular
    Join Date
    Jul 2019
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    I see the # you referenced now for code. Yes I will use this going forward. Thank you Fluff

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jul 2019
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    Board Regular
    Join Date
    Jul 2019
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    In that case did you try my suggestion?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Jul 2019
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •