Tricky MAX/LARGE column retrieval in VBA

[nj2406]

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.

 

[nj2406]

Bump.

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

 

[XOR LX]

Hi.

Try e.g.:

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

Regards

 

[Peter_SSs]

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.

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]

However VBA does not like comma separated arrays, ...

Hi Nick

Here is another option, with a comma separated array

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