VBA to perform calculation and insert calculated values in a range

[asad]

Hello Guys,

I am trying to get this line of code

 Range("D15:D30").Value = WorksheetFunction.IF(OR(B15="Vacant",B15=""),"",IFERROR(INDEX('Roster Export'!$B$1:$B$1500,MATCH($C15&D$3,('Roster Export'!$AP$1:$AP$1500)*1&'Roster Export'!$D$1:$D$1500,0)),IF(OR((('Leave from TT'!$C$2:$C$500)*1=$C15)*('Leave from TT'!$A$2:$A$500<=D$3)*('Leave from TT'!$B$2:$B$500>=D$3)),"Leave","OFF")))

to calculate values and insert those values in the range. How can I do this? Is this even possible?
The formula in the line above is an Array formula that I using in my file. The problem is that the file is too big with many tabs and many ranges use same and/or similar array formulas to calculate. This has made the file painfully slow.
I was thinking if VBA could do those calculations in the background and insert values in the ranges, that will make the file faster. Wouldn't it?

Thanks for looking at it.

Asad

 

[L. Howard]

Try it like this. Make sure it is all on one line, I don't think you can wrap the formula with Underscores ( _ ).
I returns #NAME ? on my sheet because I have none of the sheets named in the formula.

Howard

Sub My_Formula()
 With Range("D15:D30")
  .Formula = "=WorksheetFunction.IF(OR(B15=""Vacant"",B15=""""),"""",IFERROR(INDEX('Roster Export'!$B$1:$B$1500,MATCH($C15&D$3,('Roster Export'!$AP$1:$AP$1500)*1&'Roster Export'!$D$1:$D$1500,0)),IF(OR((('Leave from TT'!$C$2:$C$500)*1=$C15)*('Leave from TT'!$A$2:$A$500<=D$3)*('Leave from TT'!$B$2:$B$500>=D$3)),""Leave"",""OFF"")))"
  .Value = .Value
 End With
 
End Sub

The above is somewhat confusing, the code should look like this.

Sub My_Formula()

 With Range("D15:D30")
  .Formula = "= The very long formula"
  .Value = .Value
 End With
 
End Sub

 

[asad]

It is doing the same for me too.
Instead of getting values, I am also getting #NAME ?

The same formula is giving me the correct values in other cells. So what could be wrong?
It's not as if any other worksheets are missing. If I am getting answers in other cells, I should get the same result.

 

[asad]

Is it because the actual formula is an array formula entered with ctrl+shift+enter and the code is entering it as a normal formula?

 

[asad]

Here is the actual formula I have used to test the VBA. I shortened the formula a little bit because the last part of the formula was not relevant for this testing.

With Range("D15:D30")  .Formula = "=WorksheetFunction.IF(OR(B15=""Vacant"",B15=""""),"""",INDEX('Roster Export'!$B$1:$B$1500,MATCH($C15&D$3,('Roster Export'!$AP$1:$AP$1500)*1&'Roster Export'!$D$1:$D$1500,0)))"
  .Value = .Value
 End With

 

[asad]

I tried the same code but without "WorksheetFunction." part.
Now it gave me "#N/A"

 

[asad]

And sorry I missed one part, I had to change .Formula to .FormulaArray before I got "#N/A". Without array part, I got #Value for two top cells and the rest #N/A.

 

[asad]

Finally Worked it out.
The trick was to use relative reference in Array formula.
So this code worked for me

Sub Rotation_C()

                            


Dim lr As Long
 With Range("D15:D30")
 For lr = 15 To 30
  Range("D" & lr).FormulaArray = "=IF(OR($B" & lr & "=""Vacant"",$B" & lr & "=""""),"""",INDEX('Roster Export'!$B$1:$B$1500,MATCH($C" & lr & "&D$3,('Roster Export'!$AP$1:$AP$1500)*1&'Roster Export'!$D$1:$D$1500,0)))"
 Next lr
.Value = .Value
End With
                 


End Sub

Thanks for all your help and guidance.
Asad