+ Reply to Thread
Results 1 to 12 of 12

Macro to autofill a IF function based on the column before it

  1. #1
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Macro to autofill a IF function based on the column before it

    I am working on a macro that designed to reformat data and it is coming along pretty well, but I keep getting the following error

    Run-time error '1004':
    Application-defined or object-defined error

    Here is the code

    Please Login or Register  to view this content.
    The offending line is
    Please Login or Register  to view this content.

    I am sure that I calling this wrong but I am unsure as to how I should be calling it
    Last edited by capnhud; 06-16-2010 at 11:58 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to autofill a IF function based on the column before it

    Never "autofill" (a two step process) when you can insert the formulas into the entire range all at once in the first step:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to autofill a IF function based on the column before it

    It looks like your formula insertion code is inside the WITH .SORT stuff, it shouldn't be there.
    Please Login or Register  to view this content.

    I took out the UsedRange method for lastrow since there are some hidden traps with that which could result in a bad answer filling that variable.

  4. #4
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Macro to autofill a IF function based on the column before it

    Thank you JBeaucaire for taking the time to examine my macro, but I am still getting the same error even with your revised code:

    the line that is problematic:

    Please Login or Register  to view this content.
    I thought that I had created a dynamic sheet so that any workbook that I open that need this particular type of formatting I could format without explicitly call/reference the name of the workbook, but instead would act on the first sheet in the workbook that needs this formating.


    I had asked in this thread how to even set up the formula and thought that I had refenced the sheet as a dynamic sheet, but something is still wrong


    I am using Excel 2007 on Windows XP
    Last edited by capnhud; 06-16-2010 at 09:52 AM.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to autofill a IF function based on the column before it

    So your saying you're trying this code on a workbook with no sheet called Sheet1? That would cause this.

    You'll need to construct that formula with a sheet name variable in place of where Sheet1 is currently.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Macro to autofill a IF function based on the column before it

    the sheet is the 1st sheet and not sheet one. I thought that I was using an index instead of a name. How am I supposed to reference the first sheet?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to autofill a IF function based on the column before it

    Quote Originally Posted by capnhud View Post
    the sheet is the 1st sheet and not sheet one. I thought that I was using an index instead of a name. How am I supposed to reference the first sheet?

    I've shown you how to do that in post #5. Look for all references/use of FirstSht variable.

  8. #8
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Macro to autofill a IF function based on the column before it

    Does the following piece of code

    Please Login or Register  to view this content.
    mean that the word FirstSht is the same as the first sheet that appears in a workbook no matter what the name of the first sheet may be?

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to autofill a IF function based on the column before it

    Try it and see. Use F8 to step through the code one line at a time. After that line of code executes, hover your mouse over the word FirstSht and you'll see what the current value is.

  10. #10
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Macro to autofill a IF function based on the column before it

    When i try to run the macro from what you have posted I am receiving the same error

    Run-time error '1004':
    Application-defined or object-defined error

    and it corresponds to this line of code when I indicate to debug

    Please Login or Register  to view this content.


    edit: FirstSht when I mouse over it says FirstSht = "" which to me means that nothing is being specified yet is that correct?
    Last edited by capnhud; 06-16-2010 at 11:50 AM.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to autofill a IF function based on the column before it

    Is this any better?
    Please Login or Register  to view this content.
    Check that the formula inserted in column F is correct.

  12. #12
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Macro to autofill a IF function based on the column before it

    Check that the formula inserted in column F is correct.
    I see that you removed the $A$1 and once you did that the macro completed and did as it was expected to do. I introduced this error I see when I tried to create a variable worksheet you solution works perfectly.


    Thank you for pointing out my errors in trying to create this macro. I am just learning. I have marked this as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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