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:

TXN No	Product NO	ACCT NO	TXN TIME		TXN DATE	FLG_ DRCR	DAT_LOG	AMT	Running Balance	Given Time	Nearest Time
4509			21:25:14		30-11-2018	D	11/30/2018	2500	-938200	11:45	11:43:44
4510			11:19:33		30-11-2018	D	11/30/2018	500	-898600
4511			11:22:06		30-11-2018	D	11/30/2018	500	-898100
4512			11:24:33		30-11-2018	D	11/30/2018	500	-897600
4513			11:25:57		30-11-2018	D	11/30/2018	500	-897100
4515			11:38:17		30-11-2018	D	11/30/2018	10000	-887100
4516			11:39:11		30-11-2018	D	11/30/2018	1500	-885600
4518			11:40:22		30-11-2018	D	11/30/2018	500	-885100
4519			11:41:11		30-11-2018	D	11/30/2018	500	-884600
4520			11:42:03		30-11-2018	D	11/30/2018	500	-884100
4521			11:42:46		30-11-2018	D	11/30/2018	500	-883600
4522			11:43:44		30-11-2018	D	11/30/2018	500	-883100
4525			11:48:31		30-11-2018	D	11/30/2018	500	-882600
4526			11:59:17		30-11-2018	D	11/30/2018	3000	-879600
4531			12:11:31		30-11-2018	D	11/30/2018	2000	-877600
4532			12:16:33		30-11-2018	D	11/30/2018	500	-877100

<colgroup><col><col><col><col span="2"><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

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 ...