VBA Tricky Question

Closed
Report
Posts
1
Registration date
Friday March 4, 2016
Status
Member
Last seen
March 4, 2016
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 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
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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 2821 users have said thank you to us this month