Tricky MAX/LARGE column retrieval in VBA

nj2406

New Member
Joined
Jul 21, 2015
Messages
15
Hello!

I have four columns of data (some minus numbers too) which are not next to each other (columns R,V,Z and AD).
In VBA I'd like to run a loop through my rows of data and find which values in these columns is the largest absolute value.

In excel itself it works perfectly like this.
=IF(ABS(R2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"R",IF(ABS(V2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"V",IF(ABS(Z2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"Z",IF(ABS(AD2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"AD"))))

However VBA does not like comma seperated arrays, only Range("R2:AD2") which wont work as the columns are not next to each other.
I have tried the following to no avail.
WorksheetFunction.Large(range("R"&r),range("v"&r),range("z"&r),range("AD"&r), 2)


Any help or tips would be fantastic, thanks!
Nick.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

nj2406

New Member
Joined
Jul 21, 2015
Messages
15
Bump.

The excel sheet way works fine, but is this replicable in VBA alone?
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi.

Try e.g.:

Code:
WorksheetFunction.Large(Union(Range("R" & r), Range("V" & r), Range("Z" & r), Range("AD" & r)), 2)
Regards
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
In excel itself it works perfectly like this.
=IF(ABS(R2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"R",IF(ABS(V2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"V",IF(ABS(Z2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"Z",IF(ABS(AD2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),"AD"))))

However VBA does not like ...
Using your formula in vba (with doubled-up quotes) worked fine for me.
Wasn't sure where you wanted the results - I've put them in column AE.

Code:
Sub MaxAbs()
  With Range("AE2:AE" & Range("R" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(ABS(R2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),""R"",IF(ABS(V2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),""V""," _
        & "IF(ABS(Z2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),""Z"",IF(ABS(AD2)=MAX(ABS(R2),ABS(V2),ABS(Z2),ABS(AD2)),""AD""))))"
    .Value = .Value
  End With
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
However VBA does not like comma separated arrays, ...

Hi Nick

Here is another option, with a comma separated array

Code:
Sub Test()
Dim vArray As Variant, v As Variant

vArray = Array(Abs(Range("R2")), Abs(Range("V2")), Abs(Range("Z2")), Abs(Range("AD2")))

With Application
    v = .Index(Array("R", "V", "Z", "AD"), .Match(.Large(vArray, 2), vArray, 0))
End With

MsgBox "Result: " & v
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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
Top