select case problem

Hi everybody! I'm quite a vba newbeginner, but I think this is a simple question..

I have a value in Range("I3"), and if that value is higher than 0 and less than what I have got in Range("I10"), I want the value of Range("I4") to be Range("H10") and so on. I have 20 select cases...
I'm not sure if select case is the best solution to my problem? But this is what I have got so far:

Please Login or Register  to view this content.

When I try this I get the message Run time error Type mismatch

Sorry if this is not the right place to post my question, if my English is not satisfying or my question is confusing!!

jippii,

Try this macro instead. Does it accomplish what you're looking for?

Please Login or Register  to view this content.

Hope this helps,
~tigeravatar

Yes!! This is exactly what I'm looking for.. I think
Only one problem (this is an example):

H I
1 Bicycle 20
2 Train 30
3 Bus 40
4 Car 50
5 Motorcycle 60

If I use your macro and I have a number, for example 35, I get the value from Range("I2") -30 while what I want is Range("H2") - Train. I think we are on the right way though. Do you understand?

jippii,

Ah, I see. Here's the updated code:

Please Login or Register  to view this content.

~tigeravatar

Thank you tigeravatar!

Sorry for asking again, but I have got a new problem. Lets use my example with bicycles and trains. If I use your macro when I have a value that is 35, the macro correctly gives me the answer I'm looking for Train. But when I change the value to for example 100 it still gives me Train as answer, not Motorcycle like it should..

jippii,

The way the macro is setup, it only finds a value greater than what is in I3. If there is no value greater than I3, it simply doesn't update I4. Its not actually returning a value. Since 100 is higher than the highest value of Motorcycle at 60, the macro finds no match and ends. If a value is entered that is higher than the highest and you need that to return the highest value (so entering 100 would return Motorcycle), use this updated code:

Please Login or Register  to view this content.

I also included a LastRow variable. Set that to the lastrow containing data that you want the macro to look through. Right now its set to end at row 30. So if the macro does not find any matches, then it will simply return the item at row 30. To get it to work with your sample data, you'd have to change LastRow to 14, since the sample data starts on row 10 and ends at row 14.

Hope that helps,
~tigeravatar

Wonderful!