+ Reply to Thread
Results 1 to 7 of 7

Recorded VBA Macro error 1004 - Formula issue.

  1. #1
    Registered User
    Join Date
    01-19-2023
    Location
    New Zealand
    MS-Off Ver
    Office365
    Posts
    14

    Recorded VBA Macro error 1004 - Formula issue.

    Hi.

    I'm having an issue with an error 1004 application defined or object defined error.

    I know the formula works but I've recorded it and something is not right.

    I just need to get this bit right for the rest of the script to work.

    Can someone please help?


    Sub MonthlyReport()
    '
    ' MonthlyReport Macro
    '

    '

    last_row = Cells(Rows.Count, 17).End(xlUp).Row
    Range("Y2").Formula = "=IF(RC1=""P2"",RC23+(4/24),IF(RC1=""P1"",RC23+(2/24),LET(a,MOD(RC23,1)<TIME(8,0,0),b,WEEKDAY(RC23,2)<6,c,IF(ISERROR(MATCH(INT(RC23),'Public Holidays'!R1C2:R12C2,0)),TRUE,FALSE),d,LOOKUP(RC1,{""P3"",""P4""},IF(R1C25=""Response Due Date"",{3,10},{6,13})),e,IF(AND(a,b,c,d>1),1,0),f,MOD(RC23,1)>TIME(16,0,0),g,IF(OR(f,OR(a,b=FALSE)),TIME(16,0,0),MOD(RC23,1)+IF(d>1,0,d*8/" & _
    "24)),WORKDAY(RC23,IF(d=1,0,d)-e,'Public Holidays'!R1C2:R12C2)+g)))" & _
    ""
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & last_row)

    last_row = Cells(Rows.Count, 17).End(xlUp).Row
    Range("AC2").Formula = "=IF(RC[-28]=""P2"",RC[-6]+(8/24),IF(RC[-28]=""P1"",RC[-6]+(4/24),LET(a,MOD(RC23,1)<TIME(8,0,0),b,WEEKDAY(RC23,2)<6,c,IF(ISERROR(MATCH(INT(RC23),'Public Holidays'!R1C2:R12C2,0)),TRUE,FALSE),d,LOOKUP(RC1,{""P3"",""P4""},IF(R1C29=""Response Due Date"",{3,10},{6,13})),e,IF(AND(a,b,c,d>1),1,0),f,MOD(RC23,1)>TIME(16,0,0),g,IF(OR(f,OR(a, b=FALSE)),TIME(16,0,0),MOD(RC23,1)+" & _
    "IF(d>1,0,d*8/24)),WORKDAY(RC23,IF(d=1,0,d)-e,'Public Holidays'!R1C2:R12C2)+g)))" & _
    ""
    Range("AC2").AutoFill Destination:=Range("AC2:AC" & last_row)

    End Sub

  2. #2
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,790

    Re: Recorded VBA Macro error 1004 - Formula issue.

    Can you paste the formula that works as it appears in the cell?

  3. #3
    Registered User
    Join Date
    01-19-2023
    Location
    New Zealand
    MS-Off Ver
    Office365
    Posts
    14

    Re: Recorded VBA Macro error 1004 - Formula issue.

    Hi ByteMarks,

    Yes sure. There are two formulas in the order as below.

    =IF($A2="P2",$W2+(4/24),IF($A2="P1",$W2+(2/24),LET(a,MOD($W2,1)<TIME(8,0,0),b,WEEKDAY($W2,2)<6,c,IF(ISERROR(MATCH(INT($W2),'Public Holidays'!$B$1:$B$12,0)),TRUE,FALSE),d,LOOKUP($A2,{"P3","P4"},IF($Y$1="Response Due Date",{3,10},{6,13})),e,IF(AND(a,b,c,d>1),1,0),f,MOD($W2,1)>TIME(16,0,0),g,IF(OR(f,OR(a,b=FALSE)),TIME(16,0,0),MOD($W2,1)+IF(d>1,0,d*8/24)),WORKDAY($W2,IF(d=1,0,d)-e,'Public Holidays'!$B$1:$B$12)+g)))

    =IF(A2="P2",W2+(8/24),IF(A2="P1",W2+(4/24),LET(a,MOD($W2,1)<TIME(8,0,0),b,WEEKDAY($W2,2)<6,c,IF(ISERROR(MATCH(INT($W2),'Public Holidays'!$B$1:$B$12,0)),TRUE,FALSE),d,LOOKUP($A2,{"P3","P4"},IF($AC$1="Response Due Date",{3,10},{6,13})),e,IF(AND(a,b,c,d>1),1,0),f,MOD($W2,1)>TIME(16,0,0),g,IF(OR(f,OR(a, b=FALSE)),TIME(16,0,0),MOD($W2,1)+IF(d>1,0,d*8/24)),WORKDAY($W2,IF(d=1,0,d)-e,'Public Holidays'!$B$1:$B$12)+g)))

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Recorded VBA Macro error 1004 - Formula issue.

    Have you tried using .Formula2 or, more likely in this case, .FormulaR1C12 ?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,790

    Re: Recorded VBA Macro error 1004 - Formula issue.

    Does it work if use code them like this?

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-19-2023
    Location
    New Zealand
    MS-Off Ver
    Office365
    Posts
    14

    Re: Recorded VBA Macro error 1004 - Formula issue.

    Thank you ByteMarks.

    I haven't had time to look at the difference but it works well. All sorted. Appreciate it.

  7. #7
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,790

    Re: Recorded VBA Macro error 1004 - Formula issue.

    That's great.
    The "problem" is that the macro recorder records formulae in R1C1 notation.

    If you select the cell with the formula and go to the immediate window (Ctrl+G) in the VBA Editor and type the following and press enter, it should generally give you the formula in a code-friendly format which you can copy and paste in your routine.

    Please Login or Register  to view this content.

+ 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. Issue with recorded macro in Excel
    By butterscotch in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-17-2021, 11:24 AM
  2. [SOLVED] issue with macro freeze pane from sheet 1 to sheet 2 getting runtime error 1004
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2019, 01:16 PM
  3. Runtime error 1004 when running excel macro. Other users do not have same issue with file
    By macronewbie99 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-09-2018, 02:23 PM
  4. [SOLVED] Recorded Macro returns Run-time error '1004'
    By absconditus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2015, 04:56 AM
  5. Cell references error with recorded Conditional Formatting (Formula) macro
    By nlexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2012, 01:38 PM
  6. Recorded macro gets formula error
    By CityMPLSEmpolyee in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-10-2012, 09:28 AM
  7. Macro Issue - Run time error 1004
    By Matt7102 in forum Excel General
    Replies: 1
    Last Post: 12-21-2005, 08:40 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