VBA equivalent of Worksheet Function

[andreltb]

Hi everyone,

I am new to VBA and this forum, so, please forgive me if I'm doing something work.

I have been fairly successful with what I am trying to write in VBA, but right now I need some help.

Basically, I've been trying to identify the cell number and row in a spreadsheet that matches a value on another spreadsheet.
The following worksheet function I found online and adapted seems to work perfectly:

=ADDRESS(SUMPRODUCT((Compilado!S3:AD33=Totais!U2)*ROW(Compilado!S3:AD33));SUMPRODUCT((Compilado!S3:AD33=Totais!U2)*COLUMN(Compilado!S3:AD33))).

However I am failing to write this in VBA. Does anyone know the VBA syntax for this or something equivalent??

Thanks in advance.
André

 

[DanteAmor]

Welcome to the forum!

Activate the macro recorder.
Edit the formula and press enter.
Stop the macro recorder.
Check the macros and you will have something like this:

Sub Macro6()
'
' Macro6 Macro
    Range("F16").FormulaR1C1 = _
        "=ADDRESS(SUMPRODUCT((Compilado!R[-11]C[13]:R[19]C[24]=Totais!R[-12]C[15])*ROW(Compilado!R[-11]C[13]:R[19]C[24])),SUMPRODUCT((Compilado!R[-11]C[13]:R[19]C[24]=Totais!R[-12]C[15])*COLUMN(Compilado!R[-11]C[13]:R[19]C[24])))"
End Sub

Then you can put it like this:

Sub Macro7()
'
' Macro6 Macro
    Range("F16") = _
        "=ADDRESS(SUMPRODUCT((Compilado!S3:AD33=Totais!U2)*ROW(Compilado!S3:AD33)),SUMPRODUCT((Compilado!S3:AD33=Totais!U2)*COLUMN(Compilado!S3:AD33)))"
End Sub

 

[andreltb]

Let me try to explain myself better.

Basically on worksheet "Compilado" I have something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Day[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD](..)[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]3[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD](...)[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]31[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]70[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD](...)[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]31[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD](...)[/TD]
[TD](...)[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD]31[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]70[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

On worksheet "Totais" I have:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Max of Year[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD](...)[/TD]
[TD](..)[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

The worksheet function I shared would be an intermediate step that would retrieve the cell in "Compilado" where the "Max of the Year" number was pulled so I can find what I really need - the Day and Month of each year's the "Max of the Year".

If anyone has an ideia of a way I can easily find this date for each without using VBA, please, feel free to share.

Thanks again,
André

 

[andreltb]

Hello Dante,

Thanks for the welcoming and the promptness to help!

I've asked this because this was the only worksheet function I found that worked for my situation, but, ultimately, I need to add it to a loop, changing the destination cell and the range. I wasn't clear enough in my question, sorry.

Would your suggestion help me in this case?

 

[DanteAmor]

Hello Dante,
Thanks for the welcoming and the promptness to help!
I've asked this because this was the only worksheet function I found that worked for my situation, but, ultimately, I need to add it to a loop, changing the destination cell and the range. I wasn't clear enough in my question, sorry.
Would your suggestion help me in this case?

It is no longer clear what you need.


You asked for help to pass the formula to VBA.


Then you asked for help to find the day and month.
I give you the formulas to find the day and month of the greatest value.

I guess the data on the sheet is like this

<b>Compilado</b><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Year</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Day</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Jan</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Feb</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Nov</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Dec</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1919</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1919</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1919</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">50</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1919</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">(...)</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">30</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1919</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">31</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">70</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1920</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1920</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1920</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">(...)</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1920</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">31</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">2019</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">2019</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">31</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">80</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">70</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td></tr></table>


-------------
Output

<b>Totais</b><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Year</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Day</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Month</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1919</td><td style="text-align:right; ">31</td><td >Dec</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">1920</td><td style="text-align:right; ">1</td><td >Nov</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:right; ">2019</td><td style="text-align:right; ">31</td><td >Feb</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=INDEX(Compilado!$B$2:$B$12,MAX((Compilado!$A$2:$A$12=A2)*(Compilado!$C$2:$F$12=MAX(IF(Compilado!$A$2:$A$12=A2,Compilado!$C$2:$F$12)))*ROW(Compilado!$C$2:$F$12))-1)}</td></tr><tr><td >C2</td><td >{=INDEX(Compilado!$C$1:$F$1,0,MAX((Compilado!$A$2:$A$12=A2)*(Compilado!$C$2:$F$12=MAX(IF(Compilado!$A$2:$A$12=A2,Compilado!$C$2:$F$12)))*COLUMN(Compilado!$C$2:$F$12))-2)}</td></tr></table></td></tr></table>


--------
Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[DanteAmor]

Sorry for this

"31 Feb"

I was testing, obviously there is no such date.