Match function with two column fields

[cnatac2000]

I have this Excel Array Formula. It works on the Actual Excel worksheet. However, when I tried to transfer it to VBA Excel, it gives me this error Run time error 1004". I normally creates fhe formula first to make sure it works then transfer it to VBA Excel, that I can include as part of the module

This formula matches code 3987 and amounts$3,988.19 to the Deposit List. The formula considers any amount less than the threshold of $300 under B7 as a match.

A7 contains code 3987
B7 Contains amount $3,988.19

C7 - contains amount $300 used as threshold

This is the deposit list
Deposit!G7:G19 - contains deposit code which include 3987
Deposit!F7:F19- Contains dollar amount the nearest amount is 3,911.87 under row 14

This formula works on D7 but I could not transfer it to Excel VBA

Sheets("Deposit").Range("D7").FormulaArray = _
"=IFERROR(IFERROR(MATCH(Deposit!A7&Deposit!B7,Deposit!G7:G19&Deposit!F7:F19,0)+6,(MATCH(1,(Deposit!G7:G19&Deposit!F7:F19>(Deposit!A7&Deposit!B7-C7))*(Deposit!G7:G19&Deposit!F7:F19<Deposit!A7&Deposit!B7+C7),0)+6)),0)"



I am baffled about how to transfer this to VBA Excel.

Any help will be greatly appreciated.

 

[Alex Blakenburg]

I normally creates fhe formula first to make sure it works then transfer it to VBA Excel

That is a great habit to get into.

Although I am seeing references to the formula can't exceed 255 characters and this is well below that, it looks to me that the length is still the issue.
I am on MS 365 so my excel may be behaving differently but try the below:

1) Is the formula actually on the sheet Deposit ?
If it is then you can remove all the references to "Deposit!" and try it again.

2) If your formula is on a different sheet and you need the Deposit references, try these 2 line

' in the first line I replaced "Deposit!G7:G19&Deposit!F7:F1" with the only one shorter range "Deposit!G7:G19"
Range("D7").FormulaArray = "=IFERROR(IFERROR(MATCH(Deposit!A7&Deposit!B7,Deposit!G7:G199,0)+6,(MATCH(1,(Deposit!G7:G199>(Deposit!A7&Deposit!B7-C7))*(Deposit!G7:G199<Deposit!A7&Deposit!B7+C7),0)+6)),0)"
' reversed my previous replacement
Range("D7").Replace "Deposit!G7:G19", "Deposit!G7:G19&Deposit!F7:F1", xlPart

I got this from @Domenic here VBA error, Unable to set the FormulaArray property of the Range class

 

[cnatac2000]

Domenic,

Thanks for taking the time to look at it. Actually, the formula will be in another sheet. Once I have the VBA code, I will replace it the sheet with the actual one. I just did this way to make it easy for the reader to see it.

For some reason, I did copy your code, but I still get the same error.

Sub test2()



' in the first line I replaced "Deposit!G7:G19&Deposit!F7:F1" with the only one shorter range "Deposit!G7:G19"
Range("D8").FormulaArray = _
"=IFERROR(IFERROR(MATCH(Deposit!A7&Deposit!B7,Deposit!G7:G199,0)+6,(MATCH(1,(Deposit!G7:G199>(Deposit!A7&Deposit!B7-C7))*(Deposit!G7:G199<Deposit!A7&Deposit!B7+C7),0)+6)),0)"

' reversed my previous replacement
Range("D8").Replace "Deposit!G7:G19", "Deposit!G7:G19&Deposit!F7:F1", xlPart


End Sub

 

[Alex Blakenburg]

This won't be right because it is looking at the active sheet instead of deposit but if you use this line does it still give you an error 1004 on that line ?

Range("d7").FormulaArray = "=IFERROR(IFERROR(MATCH(A7&B7,G7:G19,0)+6,(MATCH(1,(G7:G19>(A7&B7-C7))*(G7:G19<A7&B7+C7),0)+6)),0)"

If it does I may not be able to help you since I don't have access to an older version of Excel, and these all work fine on my computer.

 

[cnatac2000]

Domenic

Thank you. The new code works like a charm. You save my day.

Since the data and the formula reside in two different sheets, I will add the sheet name to the formula. Hopefully, this will work.

Once again, my sincere thanks.

Conrad

 

[Alex Blakenburg]

Thanks for the update.