+ Reply to Thread
Results 1 to 6 of 6

Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

  1. #1
    Registered User
    Join Date
    07-18-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    30

    Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

    Hello,

    My problem is that my below Macro stops at “Set f3 = f1.SpecialCells(xlCellTypeBlanks)” because there are no non-Blank cells visible after the first Auto-Filter – but I still need to be able to add the name of the second pattern where those filtered cells were labelled by a previous Pattern eg. f = f & "//2ndUpStAfterWin". The entire Code I am using is below & I have attached the Starting Spreadsheet & the Desired Result (highlighted).

    Is there any tweak I can make to this code so that it can cope with where there are no cells existing according to those Set f2 and set f3.
    IF ELSE doesn’t work on my spreadsheets as they are very large (it stops halfway). Very keen to see what solutions may be and appreciate very much anyone's help.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,409

    Re: Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-18-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

    Hello,

    I inserted that code above but it is still stopped at “Set f3 = f1.SpecialCells(xlCellTypeBlanks)” because f3 does not find any Blank cells. Not sure whether counting Visible cells is a solution because the visible cells in column E are still going to be either Blank or Non-Blank.

    Basically what I am trying to do is:
    - For Blank Visible Cells, cell = "Pattern Name"; and
    - For Non-Blank Visible Cells, cell = cell.value&"//PaternName"

    I've just tried using Count Blank Visible rows as part of an inserted IF ELSE (as follows) but this also stopped as an error "BlankCounter2 = f1.SpecialCells(xlCellTypeBlanks).Rows.Count".
    Running out of ideas...help!

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,409

    Re: Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

    These mods did not error out (?)

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-18-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

    That worked. Amazing. So that I am learning, can you tell me what is that section of code actually doing. I've never used some of those commands.Looks like Err.Clear seems to prevent the macro from stopping after Set blanks but I don't know what that code means.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,409

    Re: Trying to accommodate IF ELSE within Set Cells for SpecialCellsVisible

    Please Login or Register  to view this content.
    Line #2 could throw an error if there were no constants found so the On Error Resume Next is placed ahead of it to catch any error and cause the macro to proceed.
    If there is an error, b2 would continue to be set to Nothing (its initial value assigned when Dim'd)

    Err.clear resets the Err code to zero so that On Error can be used again, ...

    In your case we are going to check if b2 is Nothing, but sometimes you might want to check the actual Err value and do some other calculation.
    Last edited by protonLeah; 03-04-2015 at 11:57 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Please help to accommodate changing file format
    By LenaK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2015, 12:53 PM
  2. [SOLVED] A formula to accommodate a few simple math conditions
    By nicki_rae22 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-21-2014, 08:48 AM
  3. [SOLVED] The macro is not flexible enough to accommodate the date input
    By BNCOXUK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-25-2013, 06:20 AM
  4. Macro to accommodate the chart size
    By parekhharsh_j in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2012, 08:30 AM
  5. Replies: 1
    Last Post: 03-03-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1