+ Reply to Thread
Results 1 to 5 of 5

.Formula not working. I bet this is simple!

Hybrid View

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    Morrisville, NC
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Exclamation .Formula not working. I bet this is simple!

    I am having an error with the code below to insert a formula into Range A4:A(whatever last row is). Anyquick fixes? My formula is:
    Formula: copy to clipboard
    =IF(B3="","",TEXT(RIGHT(INDIRECT(ADDRESS("MATCH("Date: "&"*",$M:$M,0),13,4,1)),LEN(INDIRECT(ADDRESS(MATCH("Date: "&"*",$M:$M,0),13,4,1)))-6),"mm/dd/yyyy"))


    Sub FillFormulas()
    
    Dim Lastrow As Long
    Application.ScreenUpdating = False
    Lastrow = Range("M" & Rows.Count).End(xlUp).Row
    Range("A3:A" & Lastrow).Formula = "=IF(B3="","",TEXT(RIGHT(INDIRECT(ADDRESS("MATCH("Date: "&"*",$M:$M,0),13,4,1)),LEN(INDIRECT(ADDRESS(MATCH("Date: "&"*",$M:$M,0),13,4,1)))-6),"mm/dd/yyyy"))"
    
    End Sub

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,274

    Re: .Formula not working. I bet this is simple!

    You need to double every " mark in the formula:
    Range("A3:A" & Lastrow).Formula = "=IF(B3="""","""",TEXT(RIGHT(INDIRECT(ADDRESS(MATCH(""Date: *"",$M:$M,0),13,4,1)),LEN(INDIRECT(ADDRESS(MATCH(""Date: *"",$M:$M,0),13,4,1)))-6),""mm/dd/yyyy""))"
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    Morrisville, NC
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Re: .Formula not working. I bet this is simple!

    Awesome!!! Thanks a million. Works like a dream. I appreciate your time with this!

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,274

    Re: .Formula not working. I bet this is simple!

    Glad to help.

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: .Formula not working. I bet this is simple!

    Maybe:

    Sub gedwards913()
    Dim Lastrow As Long
    Application.ScreenUpdating = False
    Lastrow = Range("M" & Rows.Count).End(xlUp).Row
    Range("A3:A" & Lastrow).Formula = "=IF(B3="""","""",TEXT(RIGHT(INDIRECT(ADDRESS(""MATCH(""Date: ""&""*"",$M:$M,0),13,4,1)),LEN(INDIRECT(ADDRESS(MATCH(""Date: ""&""*"",$M:$M,0),13,4,1)))-6),""mm/dd/yyyy""))"
    End Sub
    You need double "

+ 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] Simple v look up formula is not working
    By ryan4646 in forum Excel General
    Replies: 11
    Last Post: 11-30-2013, 02:09 AM
  2. Simple Formula Addition Not working
    By kwright90 in forum Excel General
    Replies: 26
    Last Post: 03-31-2009, 09:10 AM
  3. [SOLVED] Simple formula NOT working :(
    By Danny Lewis in forum Excel General
    Replies: 3
    Last Post: 08-01-2006, 05:40 AM
  4. [SOLVED] simple formula not working
    By Charles Shapiro in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2006, 03:29 AM
  5. Simple Formula not working?
    By GETGO in forum Excel General
    Replies: 2
    Last Post: 01-06-2005, 08:50 AM

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