+ Reply to Thread
Results 1 to 6 of 6

macro input formula

  1. #1
    Registered User
    Join Date
    03-14-2007
    Posts
    58

    Unhappy macro input formula

    so i have a formula that wont work in my macro but if i cut and paste it into the cell i want it to work in, it works fine....


    Range("B2").Formula = "=IF(ISERROR(IF(MATCH(Courses!A1,Master!B1:B700),Courses!A1,"")),"",IF(MATCH(Courses!A1,Master!B1:B700),Courses!A1,""))"

    this one works and its the same format ....
    Range("C2").Formula = "=IF(SUMPRODUCT(--(Updated!$A$1:$A$700=$A$1)*--(Updated!$B$1:$B$700=B2),Updated!$E$1:$E$700),SUMPRODUCT(--(Updated!$A$1:$A$700=$A$1)*--(Updated!$B$1:$B$700=B2),Updated!$E$1:$E$700),(SUMPRODUCT(--(Master!$A$1:$A$700=$A$1)*--(Master!$B$1:$B$700=B2),Master!$C$1:$C$700)))"

    what am i doing wrong? dont worry about the second formula, it works fine but the first one is giving me issues only in my macro .. the only other difference and i dont know if it matters or not but b2 is text and c2 is a number

    Courses!A1 contains a course name that needs to be found on the master sheet anywhere in b1:b700 if its not there it puts a blank cell...and theres no space on courses!a1 if its showing up that way .. :-)
    Last edited by ashleykelley; 03-16-2007 at 11:21 PM.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by ashleykelley
    so i have a formula that wont work in my macro but if i cut and paste it into the cell i want it to work in, it works fine....


    Range("B2").Formula = "=IF(ISERROR(IF(MATCH(Courses!A1,Master!B1:B700),Courses!A1,"")),"",IF(MATCH(Courses!A1,Master!B1:B700),Courses!A1,""))"

    this one works and its the same format ....
    Range("C2").Formula = "=IF(SUMPRODUCT(--(Updated!$A$1:$A$700=$A$1)*--(Updated!$B$1:$B$700=B2),Updated!$E$1:$E$700),SUMPRODUCT(--(Updated!$A$1:$A$700=$A$1)*--(Updated!$B$1:$B$700=B2),Updated!$E$1:$E$700),(SUMPRODUCT(--(Master!$A$1:$A$700=$A$1)*--(Master!$B$1:$B$700=B2),Master!$C$1:$C$700)))"

    what am i doing wrong? dont worry about the second formula, it works fine but the first one is giving me issues only in my macro .. the only other difference and i dont know if it matters or not but b2 is text and c2 is a number

    Courses!A1 contains a course name that needs to be found on the master sheet anywhere in b1:b700 if its not there it puts a blank cell...and theres no space on courses!a1 if its showing up that way .. :-)
    Hi,

    try

    Range("B2").Formula = "=IF(ISERROR(IF(MATCH(Courses!A1,Master!B1:B700),Courses!A1,"""")),"""",IF(MATCH(Courses!A1,Master!B1:B700),Courses!A1,""""))"

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    03-14-2007
    Posts
    58
    that didnt work i get the same error

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by ashleykelley
    that didnt work i get the same error
    what error? - that was a working copy

    ---
    added sample file
    ---
    Attached Files Attached Files
    Last edited by Bryan Hessey; 03-17-2007 at 12:16 PM.

  5. #5
    Registered User
    Join Date
    03-14-2007
    Posts
    58
    when i copy and pasted it from the macro it worked beautifully thank you so very much :-) so was it the "" that was causing the issue?

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by ashleykelley
    when i copy and pasted it from the macro it worked beautifully thank you so very much :-) so was it the "" that was causing the issue?
    Hi,

    when an item is in quotes (") and that item contains quotes (") then the outer pair remain as single (start and end) and all others are doubled to show they are not the start/end pair. note, when you copy/paste from a web item you need to check for web-introduced spaces, and remove them.

    good to see that it worked for you.
    ---

+ 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