VBA No cells were found

Thanks Thanks:  0
Results 1 to 10 of 10

Thread: VBA No cells were found

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking VBA No cells were found

     
    Hello,

    I tries to run a macro and a message pops up "No cells were found".
    The Macro runs fine until the variable dy1 is 5 (Meaning it is day 5 of the month).
    Some days are missing as there are weekends were no data are available.
    The filter returns nothing (no visible data) as there isn't day 5 in the dataset.
    Then the variable visibleTotal cannot be executed. An error pops up: no cells were found.
    Can I please get some help?

    Sub CountLines()
    Dim dy1 As Variant
    Dim hour1 As Variant
    Dim mth1 As Variant
    Dim myRng As Range
    Dim Lrow As Long
    Dim visibleTotal As Long




    'Find the last Row
    Lrow = Cells(Rows.Count, 1).End(xlUp).Row


    Application.ScreenUpdating = False


    For n = 2 To 25 'no of columns for hours
    For m = 3 To 33 'rows of days

    dy1 = Sheets("Lines").Cells(m, 1).Value
    hour1 = Sheets("Lines").Cells(2, n).Value
    mth1 = Sheets("Database").Cells(1, 19).Text


    Sheets("Database").Select


    ActiveSheet.Range("a1:s1").AutoFilter field:=13, Criteria1:=mth1
    ActiveSheet.Range("a1:s1").AutoFilter field:=16, Criteria1:=dy1
    ActiveSheet.Range("a1:s1").AutoFilter field:=14, Criteria1:=hour1



    Cells(2, 17).Select
    Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)
    visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))


    'Paste VisibleTotal in the lines sheet
    'Added screenupdating
    Application.ScreenUpdating = True
    Sheets("Lines").Cells(m, n).Value = visibleTotal
    Application.ScreenUpdating = False


    'Deactivate AutoFilter
    Sheets("Database").Select
    ActiveSheet.AutoFilterMode = False


    Next 'next row
    Next 'next Column


    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,681
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA No cells were found

    quick fix
    Code:
    If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
     visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
    End If
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Aug 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: VBA No cells were found

    I have tried the If statement but still there is a problem. When dy1 is 5 and I try to execute the If myRng.specialcells (xlCellTypeVisible).Rows.Count > 1 Then a message pops up; No cells were found. Any ideas?

    Cells(2, 17).Select
    Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)
    If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
    visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
    End If

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,383
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA No cells were found

    Quote Originally Posted by sarantip View Post
    On Error Resume Next
    visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
    On Error GoTo 0
    Try wrapping the above line with the error trapping statements shown above in red.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular LockeGarmin's Avatar
    Join Date
    Sep 2015
    Posts
    350
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA No cells were found

    Here is another option that would allow you to not worry about error trapping, though it may be a little hard to remember what it's doing when you are reading the code later hence the comment above the Subtotal line.

    Code:
    'Subtotal Sum Function, Ignore Hidden Cells
    visibleTotal = Excel.WorksheetFunction.Subtotal(109, myRng)
    It can do other functions such as averages, max, min etc. I included the documentation below.
    SUBTOTAL function - Office Support

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,681
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA No cells were found

    Quote Originally Posted by JLGWhiz View Post
    quick fix
    Code:
    If myRng.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
      visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
     End If
    You could modify it to this.
    Code:
    If Sheets("Database").Cells(2, 17) <> "" Then
      visibleTotal = Application.WorksheetFunction.Sum(myRng.SpecialCells(xlCellTypeVisible))
     End If
    On second thought, that probably won't work either. I think Rick probably has the best answer.
    Last edited by JLGWhiz; Aug 13th, 2017 at 05:46 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,383
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA No cells were found

    Quote Originally Posted by JLGWhiz View Post
    I think Rick probably has the best answer.
    I kind of like LockeGarmin's solution myself.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Aug 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: VBA No cells were found

    Thank you all. Problem solved!

  9. #9
    Board Regular LockeGarmin's Avatar
    Join Date
    Sep 2015
    Posts
    350
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA No cells were found

    I realized earlier this evening that if you find yourself using a few subtotal functions in VBA rather than putting comments above them you could include an enum in a new module (perhaps named "xlEnums") like this:

    Code:
    Public Enum xlSubtotal
      xlSubtotalAverage = 1
      xlSubtotalCount
      xlSubtotalCountA
      xlSubtotalMax
      xlSubtotalMin
      xlSubtotalProduct
      xlSubtotalStDev
      xlSubtotalStDevP
      xlSubtotalSum
      xlSubtotalVar
      xlSubtotalVarP
      xlSubtotalIgnoreHiddenCells = 100
    End Enum
    And then use subtotal like this:

    Code:
    visibleTotal = Excel.WorksheetFunction.Subtotal(xlSubtotalSum + xlSubtotalIgnoreHiddenCells, myRng)

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,585
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA No cells were found

      
    Assuming column A determines the extent of your data, try making this change only in your original code: Remove the red text
    Code:
    Set myRng = Cells(2, 17).Resize(Lrow - 1, 1)
    Last edited by Peter_SSs; Aug 14th, 2017 at 12:25 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

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
  •  

 

 
DMCA.com