VBA; greater than, less than - multiple solutions-CodePudding

This should be simple but my noob (to coin a phrase used by my 10 year old) brain can't figure it out.

Ok, I need to search Column V (called TotalWeight). If it's under 100 then in Column W it will say "L" between 101 - 250 "H" between 250 - 500 "VH" Over 500 "VVH"

I've tried

Sub Change_Weight()

=IF([TotalWeight]0 > 100, “L”, IF([TotalWeight]101 > 250, “H”, IF([TotalWeight]250 > 500, “VH”, IF([TotalWeight]500 > 900, “VVH”))))

End Sub

I don't think I'm far off? Cheers, Pip

CodePudding user response：

It might be better to work backwards from the heaviest weight to the lightest, with the lightest being the last 'False' result, so:

=If([TotalWeight]>500,"VVH",IF([TotalWeight]>250,"VH",IF([TotalWeight]>100,"H","L")))

CodePudding user response：

What I would suggest, is to put your conditions into a separate table. Any time your boss wants different values or wants more conditions it is much easier to maintain that list than to change the formula.

Put this formula into Column V:

=INDEX(configTotalWeight[Description],MATCH(A3,configTotalWeight[TotalWeight],1))

https://i.imgur.com/yRmXvsJ.png (including screenshots doesn't work at the moment)