# Adding an array formula into my macro

• Hi Hi I have this macro that is working however the first line of formula is not really working because the formula is an array and I don't know how to fix my line on the macro to solve this

• 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 "Thumbs Up" icon, below, in the bottom right corner

• this is the line that's not working

Code
1. 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
1. Sub InsertArrayFormula()
2. Range("I6").FormulaArray = _
3. "=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,"""")))"
4. End Sub

Hope this will help

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

• Hi

I tried this code but its giving me errors

Code
1. Range("c" & SR + 1 & ":c" & SR + 22).FormulaArray = _
2. "=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,"""")))"
3. 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
1. Sub InsertAllArrayFormulas()
2. For i = 1 To 22
3. Range("c" & SR + i).FormulaArray = _
4. "=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,"""")))"
5. Next i
6. End Sub

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

• Hi

Tried entering the code but the macro shows errors in red

• 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 "Thumbs Up" icon, below, in the bottom right corner

• Hi

I tried all of them

I cannot get further than the vba macro sheet

• 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,

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 "Thumbs Up" icon, below, in the bottom right corner

• Hope attached test file will work for you ...

## Files

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

• Hi

Excellent its what I was looking for

so how do I get it to do for all races

Raj

• 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 "Thumbs Up" icon, below, in the bottom right corner

• Hi

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 "Thumbs Up" 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