Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   VBA If Statement (https://www.askmehelpdesk.com/showthread.php?t=581166)

  • Jun 10, 2011, 12:11 PM
    jakester
    VBA If Statement
    Ok. Here's my problem. I have a spreadsheet that I use which filters on 6 different filter criteria and copies the result set of each filter criteria to a separate worksheet. However, on occasion there are no results found for a given criteria.

    What I want to do is when no results are found for a given criteria to return the words "No Data Found" to a cell on a different spreadsheet.

    I'm trying to find the correct syntax for:

    If SpecialCells = "" then

    Sheet("Smith").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "No Data"

    I do know that SpecialCells represents Filtered Records but that's about all I know what to do with that.

    Any ideas?

    Thanks guys.
  • Jun 13, 2011, 01:02 AM
    JBeaucaire

    Now Jakester, I'm sure I've shown you many times in the past how to take out the "selecting" and "Activating" in your macros. Tsk, tsk.

    When I apply Autofilters, after turning on the filter, I test to see if any rows are showing, and only copy if there is data showing past the row1 titles.

    Code:

    Dim LR As Long

    'Code here to turn on your filter

    LR = Range("A" & Rows.Count).End(xlUp).Row
    If LR > 1 Then
      'Code here to copy
    Else
      'Code here to write a message instead of copying
    End If

  • Jun 13, 2011, 02:56 PM
    jakester
    Quote:

    Originally Posted by JBeaucaire View Post
    Now Jakester, I'm sure I've shown you many times in the past how to take out the "selecting" and "Activating" in your macros. Tsk, tsk.

    When I apply Autofilters, after turning on the filter, I test to see if any rows are showing, and only copy if there is data showing past the row1 titles.

    Code:

    Dim LR As Long

    'Code here to turn on your filter

    LR = Range("A" & Rows.Count).End(xlUp).Row
    If LR > 1 Then
      'Code here to copy
    Else
      'Code here to write a message instead of copying
    End If


    JB - I don't recall the tip on taking out Selecting and Activating... I know I would've remembered because I'm still using the darn things. So how can you take those out and still refer to them?

    Thanks, again.
  • Jun 14, 2011, 11:59 PM
    JBeaucaire

    By fully addressing your commands with parent/child relationship, you can issue commands to any sheet, any cell/range and insert values/formulas without having once activated or selected anything, much less even brought the sheet up onscreen.

    VBA is not human, it has no need to physically touch a cell prior to using it.

    Your code, probably recorded, so the recorder shows your human actions:
    Code:

    Sheet("Smith").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "No Data"


    The same code merged down into a fully addressed single command:
    Code:

    Sheet("Smith").Range("C2") = "No Data"

    Notice, I even took out the erroneous FormulaR1C1 since you were inserting a text string, not a formula?
  • Jun 15, 2011, 12:01 AM
    JBeaucaire
    If you'd like to post your workbook and the rest of your macro, I'm sure we'll find a LOT of instances to improve efficiency and shorten code at the same time.

    I use the macro recorder every day, so I'm not scolding. The code recorded is a starting point only, we have to tweak it down into proper VBA syntax.

  • All times are GMT -7. The time now is 02:44 AM.