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.
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.