VBA Tricky Question

[Closed]
Report
Posts
1
Registration date
Friday March 4, 2016
Status
Member
Last seen
March 4, 2016
-
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
-
Hi All,

I am working on excel and I need some help with VBA/macros.

Basically, I have a variable called Y and I have a table of set values, called X which are vlookup valves, ranging from 1-10. The X values correspond to different colours 1.e x1=blue x2=red etc.

X Colour
1 Red
2 Blue
3 Green
4 Purple
etc

The equation Z where Z=X*Y and must meet the criteria where X*Y<=1

For example, if Y=0.3 the value should return is X=3 since the Z value is the max value where 0.9 which is <=1

X Z=X*Y
1 1*0.3=0.3
2 2*0.3=0.6
3 3*0.3=0.9
4 4*0.3=1.2
etc

The value is then used to lookup from a table to produce the colour i.e. X=3 therefore show the text green in another cell.

However, the variable Y needs to be copied into other places in the workbook

I.e. one place might have Y as 0.6, whereas another place may have Y was 0.4

I was wondering if it would be possible to therefore embed a formula into any location and run it so that the formula will use the same X values list from 1-10, but vary to Y value to recalculate calculate Z until the criteria is met (X*Y<=1)?



I hope this makes sense, any help is much appreciated!

Kind regards,
Max

1 reply

Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
469
Hi Max,

You mean like this?

Formula for J6:
=INT(1/H6)

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!