Previous Topic Next Topic

Range of numbers equals 1 number

Closed
John906 - Nov 18, 2010 at 09:47 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Nov 18, 2010 at 10:36 AM
Hello,

Here is what I'm trying to do:

I have a list of groups of numbers that when you enter a certain number in one cell, it will autofill another cell depending on the range that the first cell number was in.

So if you enter any number between 1-10 in A1 you will get 45% in B1. Or if you put any number 11-20 in A1, you will get 32% in B1.

There is about 15 ranges of numbers that need to equal one number(%) in a seperate cell.

Any ideas? Dropdown?

I'm not the most savvy with Xcel, so please try and be as simple as possible.

Thanks,


1 reply

Answer 1 / 1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 513
Nov 18, 2010 at 10:36 AM
Hi John906,

Try this code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 11 And Range("A1") <= 20 Then Range("B1") = "32%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%"
If Range("A1") >= 1 And Range("A1") <= 10 Then Range("B1") = "45%" Else Range("B1").ClearContents

    End Sub

First copy the code.
Then goto excel and right click on the sheets tab and click on 'view code'.
Paste the code in the big white field.
Now you're done.

Please note: you haven't given the rest of the statements. You will have to change them first before the code will work properly.

Let me know if you get stuck somewhere.

Best regards,
Trowa
0