Arrays F2 into VBE as arrays

[vincecodegreeen]

I have need for a function to be a part of the VBA code. {=IF(H47>0,CELL("address",OFFSET(TDC!B9,0,MATCH(LARGE(IF(TDC!B9:H9>0,(TDC!B9:H9)),N47),TDC!B9:H9,0)-1)),
CELL("address",OFFSET(TDC!B9,0,MATCH(SMALL(IF(TDC!B9:H9>0,(TDC!B9:H9)),N47),TDC!B9:H9,0)-1)))}

Not knowing how to write this directly into the VBE I recorded it in via the F2 enter.

This doesn't save it as a true array. When I run it and call up the cell formula, it has lost its brackets and when I restore them I get the right answer.
The LARGE and SMALL functions do function as arrays (perhaps that is inherent in its function) but the "IF(TDC!B9:H9>0," is not recognised and the cell with the smallest number returns 0.

Can anyone tell me how to preserve the array in VBA?

Thanks,
Vince

 

[sheetspread]

.formulaarray instead of .formula will put the curly braces around it.

 

[vincecodegreeen]

Thanks sheetspread,
However, I get "Unable to set FormulaArray property of the Range class"
Any ideas what's wrong?

 

[sheetspread]

Can you paste the macro here?

 

[vincecodegreeen]

Sure!

Sub MacroC()
'
' MacroC Macro
Dim units As String
Dim wkb As Workbook
Dim ws As Worksheet
Dim mealname As String
Dim quantity As String
Set wkb = ThisWorkbook


For Each ws In wkb.Worksheets
Select Case ws.Name
Case "TDC", "Food Cals", "Unassigned foods"
' skip
Case Else
ws.Activate


Range("g47").Select
For j = 1 To 4


If Abs(activecell.Offset(0, 1)) >= 10 Then

'Find meal to be adjusted
For i = 1 To 4
activecell.Offset(0, 7).Value = i
activecell.FormulaArray = _
"=IF(RC[1]>0,CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(LARGE(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[7]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1))," & Chr(10) & "CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(SMALL(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[7]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1)))"


'Find its Cell Address


Call CellAddress(j)
'Qualify quantity type
activecell.Offset(4, 0).Select
activecell.Formula = "=INDIRECT(R[-4]C[-1])"
If activecell <> "units" Then
Exit For
End If
activecell(47, 7).Select
Next i

Range("e47").Select
activecell = "=SUM(INDIRECT(RC[2]),-RC[3])"

End If
Cells(47 + j, 7).Select


Next j
End Select

Next ws
End Sub

Thanks

 

[sheetspread]

That error usually is returned when the character length exceeds 255. Your formula is only 102 on the worksheet but 340 when written from VBA. Chop off part of the formula to see if it does run, then we'll figure out a way to reduce its size or try something else.

 

[vincecodegreeen]

Here's how I split it:

Sub MacroC()
'
' MacroC Macro
Dim units As String
Dim wkb As Workbook
Dim ws As Worksheet
Dim mealname As String
Dim quantity As String
Set wkb = ThisWorkbook


For Each ws In wkb.Worksheets
Select Case ws.Name
Case "TDC", "Food Cals", "Unassigned foods"
' skip
Case Else
ws.Activate


Range("g47").Select
For j = 1 To 4


If Abs(activecell.Offset(0, 1)) >= 10 Then

'Find meal to be adjusted
For i = 1 To 4
activecell.Offset(0, 7).Value = i
If activecell.Offset(0, 1) >= 0 Then
activecell.FormulaArray = _
"=CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(LARGE(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[7]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1))," & Chr(10)
Else
activecell.FormulaArray = _
"=CELL(""address"",OFFSET(TDC!R[-38]C[-5],0,MATCH(SMALL(IF(TDC!R[-38]C[-5]:R[-38]C[1]>0,(TDC!R[-38]C[-5]:R[-38]C[1])),RC[7]),TDC!R[-38]C[-5]:R[-38]C[1],0)-1)))"
End If
'Find its Cell Address


Call CellAddress(j)
'Qualify quantity type
activecell.Offset(4, 0).Select
activecell.Formula = "=INDIRECT(R[-4]C[-1])"
If activecell <> "units" Then
Exit For
End If
activecell(47, 7).Select
Next i

Range("e47").Select
activecell = "=SUM(INDIRECT(RC[2]),-RC[3])"

End If
Cells(47 + j, 7).Select


Next j
End Select

Next ws
End Sub

But I still get the same error "Unable to set FormulaArray property of the Range class"

 

[sheetspread]

What if you record the split formula like you did originally?

 

[vincecodegreeen]

THANKS!
That works!
Why did it work after I recorded the split but not when I manually split it?
Vince

 

[sheetspread]

Well if the syntax is exactly the same in the code, then I can't guess, but perhaps there's a very slight difference that was easily overlooked.