Problem of putting an array formula in VBA

[cbk40060]

I am using the following array formula in one of my worksheets with auto-filter and visible rows, to retrieve the nearest time from visible rows to a given time:

Range(“M69”) =INDEX($E$72:$E$265,MATCH(MIN(ABS(L69-E72:E265)),ABS(L69-$E$72:$E$265),0))

The worksheet looks as under:
[TABLE="width: 1117"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]TXN No[/TD]
[TD]Product NO[/TD]
[TD]ACCT NO[/TD]
[TD]TXN TIME[/TD]
[TD] [/TD]
[TD]TXN DATE[/TD]
[TD]FLG_
DRCR[/TD]
[TD]DAT_LOG[/TD]
[TD]AMT[/TD]
[TD]Running Balance[/TD]
[TD]Given Time[/TD]
[TD]Nearest Time[/TD]
[/TR]
[TR]
[TD]4509[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]21:25:14[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]2500[/TD]
[TD]-938200[/TD]
[TD]11:45[/TD]
[TD]11:43:44[/TD]
[/TR]
[TR]
[TD]4510[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:19:33[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-898600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4511[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:22:06[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-898100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4512[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:24:33[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-897600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4513[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:25:57[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-897100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4515[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:38:17[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]10000[/TD]
[TD]-887100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:39:11[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]1500[/TD]
[TD]-885600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4518[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:40:22[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-885100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4519[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:41:11[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-884600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4520[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:42:03[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-884100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4521[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:42:46[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-883600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4522[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:43:44[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-883100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4525[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:48:31[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-882600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4526[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11:59:17[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]3000[/TD]
[TD]-879600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4531[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12:11:31[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]2000[/TD]
[TD]-877600[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4532[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12:16:33[/TD]
[TD] [/TD]
[TD]30-11-2018[/TD]
[TD]D[/TD]
[TD]11/30/2018[/TD]
[TD]500[/TD]
[TD]-877100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
The formula works when I directly use it on the worksheet.
But I am unable to put that array formula in a VBA macro.
Will anybody help me to put this array formula in a VBA macro ?

 

[James006]

Hello,

You could test following :

Range("M29").FormulaArray = _
        "=INDEX(R72C5:R265C5,MATCH(MIN(ABS(RC[-1]-R[3]C[-8]:R[196]C[-8])),ABS(RC[-1]-R72C5:R265C5),0))"

HTH

 

[cbk40060]

Dear James,

The code works as per my requirements.

Thanks a lot.

Further I have modified the array formula, so as to match the value stored in a string variable.
The following array formula is working fine:

Dim strMatch As String
strMatch = “11:45”
Range("M69").FormulaArray = "=INDEX(R72C5:R265C5,MATCH(MIN(ABS(""" & strMatch & """-R[3]C[-8]:R[196]C[-8])),ABS(""" & strMatch & """-R72C5:R265C5),0))"

‘=======================================================
But now I want to search only the values of visible rows in column “E”.
But I am trying with the following code:

Dim strMatch As String
strMatch = “11:45”
Dim Rng As Range, frng As Range
Dim rngAF As Range
Dim rngAF_col_E As Range

With ActiveSheet
LR = Range("A" & Rows.Count).End(xlUp).Row

Set frng = Range(Cells(1, 1), Cells(LR, 12))
Set Rng = Range(Cells(2, 1), Cells(LR, 12))
frng.AutoFilter Field:=1, Criteria1:=ATMID, Operator:=xlFilterValues
Set rngAF = Rng.SpecialCells(xlCellTypeVisible)
Set rngAF_col_E = rngAF.Columns("E")
Range("M69").FormulaArray = "=INDEX(""" & rngAF_col_E & """,MATCH(MIN(ABS(""" & strMatch & """-""" & rngAF_col_E & """)),ABS(""" & strMatch & """-""" & rngAF_col_E & """),0))"

And I could not make out where I am making a mistake in the above formula.

So need your help again.

- CHANDRASHEKHAR.

 

[James006]

Before jumping into the VBA code ... make sure to build a 100% correct formula in your worksheet ...

Once tested and operational ... then ... you can convert it into your code ...