VBA equivalent of Worksheet Function

A

andreltb

New Member
Joined
Jul 19, 2019
Messages
3
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é
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
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:

Code: 
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:

Code: 
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
 
Last edited:
Upvote 0
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é
 
Upvote 0
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?
 
Upvote 0
andreltb said:
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?
Click to expand...

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
 
Upvote 0
Sorry for this

"31 Feb"

I was testing, obviously there is no such date.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA - Filter a class array based on class function
Replies
1
Views
167
RoryA
RoryA
T
  • Question Question
Excel VBA writing an carriage-return or empty row at the end when saving a text file
Replies
4
Views
358
Tanquen
T
I
  • Question Question
Help with VBA script please
Replies
3
Views
407
jolivanes
jolivanes
R
  • Question Question
Trying to write VBA script for the choosecols and filter function in Excel
Replies
1
Views
697
StephenCrump
S
T
  • Question Question
"Compile Error: Out of memory" with complex userform and many vba subroutines
Replies
1
Views
488
toole123
T

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top