Array formula for a textbox

Question

Hi,

I want textbox3 to return the value based on following array formula :

{=sum(vlookup(val(txtRegNum),newrange,{19,23},0))}

How to write vba code for this?

newrange = Sheet8!A1:BO10000000

Textbox1 is named as txtRegNum

Answer 1

Untested, but I think something like this should work...

textbox3 = Evaluate("SUM(VLOOKUP(" & Val(txtRegNum) & ",'" & newrange.Parent.Name & "'!" & newrange.Address & ",{19,23},0))")

Answer 2

Wow! Its perfect sir. Many thanks.

Request you to please explain what are the meaning of those modifications in the formula? And can it be done for SUMIFS too?

I mean, if today is 29-Aug-2016, then it should add all the values below or equal to 29-Aug-2016. Table is as below :

RegNum	Date	Amt.	Date	Amt.	Date	Amt.
1	01-Jul-16	50	01-Aug-16	150	01-Sep-16	100
2	02-Jul-16	20	02-Aug-16	100	02-Sep-16	50

So, suppose if I search for RegNum1 today, it should return 200 e.g. (50+150).

If I search for RegNum1 on 01-Sep-2016, it should then return 300 e.g. (50+150+100).

Answer 3

Sir,

It is vba variable name.

Dim newrange As range

Set newrange = Worksheet(Sheet8).Range("A:BO")

Answer 4

Is newrange a VBA variable name or an Excel Defined Name?