Adding an array formula into my macro

    Hi and Welcome to the Forum :)


    Not sure to understand your question ...


    In your Column I in the RaceData sheet ... you are using an Array Formula ...


    and regarding your macro ... where exactly is the instruction you need to fix ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    this is the line that's not working

    Code
    Range("c" & SR + 1 & ":c" & SR + 22).FormulaR1C1 = "="=IF(RC[-1]="""","""",SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1),""""))/SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1))),1)),1,"""")))"

    when I do it manually it works because I press F ctrl shit enter


    but in the macro its not working

    Hello again,


    Below is an example to insert an Array Formula


    Code
    Sub InsertArrayFormula()
    Range("I6").FormulaArray = _
        "=IF(RC[-1]="""","""",SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1),""""))/SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),1,"""")))"
End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi


    I tried this code but its giving me errors

    Code
    Range("c" & SR + 1 & ":c" & SR + 22).FormulaArray = _
"=IF(RC[-1]="""","""",SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1),""""))/SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),1,"""")))"
Range("l" & SR + 1 & ":l" & SR + 22).FormulaR1C1 = "=IF(RC[-1]="""","""",(RC[-2]+RC[-1])/2)"

    Hi again


    1. Have you tried the code posted in message # 4 ...?


    2. Does it work or not ...?


    3. You should test


    Code
    Sub InsertAllArrayFormulas()
    For i = 1 To 22
        Range("c" & SR + i).FormulaArray = _
            "=IF(RC[-1]="""","""",SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1),""""))/SUM(IF(ISNUMBER(0+MID(RC[-1],ROW(INDIRECT(""1:""&LEN(RC[-1]))),1)),1,"""")))"
    Next i
End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi,


    Why aren't you answering the three previous questions ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi must be doing something that's not correct


    I edit my macro and tried all messages but it would not let me g o beyond its highlights in red


    so I must be doing something incorrect when trying to add in the lines you recommend

    Hi


    may you please try and add them into the macro so I can see where my mistake lies and if possible attached the corrected macro


    I must confess that all I have learnt so far has been by trial and error which was corrected for me


    regards


    Raj


    I do thank you for the time you are taking in trying to provide a solution

    Re,


    Can you post your latest version of your macro ... the one which generates all the errors ...


    since my crystal ball cannot see ... what is in front of your eyes ...!!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Glad to hear an Array Formula can be inserted by a macro ... !!!


    Sorry but I do not understand what you mean by " all my races " ...


    Are you talking about the other sheet Racescrape ??? ( which by the way is not structured in the same way ... Column C ...)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi


    Thanx for the reply


    Yes they are structured in the same as the first race


    all appear in column C


    the button fixed the first race and how it solve all remaining races


    regards


    Raj


    The macro runs from racesscrape and write s to racedata

    Hi

    I just check something


    after the array button is activated the race one works fine


    I manually copied the array command from the race one column C and pasted then sin each race it works


    Just need a way that it can automate


    regards


    raj

    Sorry but I am lost ...


    In sheet Racedata there a Column C for the Array Formula


    In sheet Racescrape ... Column C shows the heading ' Horse '


    In addition the structure you have selected for the sheet Racescrape ... will prevent you doing anything ...

    if you want to build something effective .. you do need to have a database approach ...!!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi


    Yes racescrape has horse


    the macro creates the colum,n C and moves all columns one away when it writes to racedata

    all my formula are added into columns that the macro creates


    its just the array one that's deluding me at the moment


    the datafile that is pasted into racescrape comes from data that I scrape using a pyton script


    therafter the macro creates the columns and copies the formulae



    regards


    raj

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login