*** VBA Assign Value From Text and Value Ranges ***

Hello,

I am trying to obtain some assistance on a project I'm working on. I have a series of values in one worksheet across one row. The row contains "Yes" or "No" text and also some currency or other number values.

My spreadsheet looks like this (the blank shown is a cell that has no value or text):

Yes No Yes No (blank) Yes No $100 50% 5

What I'd like to do is to have a VBA code that assigns a value to each of these text and numerical values by the following criteria:

If Yes, Value = 0
If No, Value = 5
If "Blank", Value = 0
If $0 to $25, Value = 1
If $26 to $50, Value = 2
If $51 to $75, Value = 3
If $76 to $100, Value = 4
If 0% to 25%, Value = 1
If 26% to 50%, Value = 2
If 51% to 75%, Value = 3
If 76$ to 100%, Value = 4
If 1 to 5, Value = 1
If 6 to 10, Value = 2
If 11 to 15, Value = 3
If 16 to 20, Value = 4

Therefore, I want to have the outputs in another worksheet (sheet 2) and it should show the following numbers based on the criteria above:

0 5 0 5 0 0 5 4 2 1

Any help would be greatly appreciated.

Thank you!

Jakob,

Thanks for your help. I'm having a bit of trouble now with the output of this user defined function in a new row or spreadsheet. I created a new module and inserted the code within the active spreadsheet I'm using.

If my original text and numbers were in A3 through J3, how do I use this user defined function now to create the 0, 5, 0, 5 , 0 , 0, 4 , 2 , 1 that I want in the next row (A4 through J4)?

Hello there,

Below is a macro that I believe would work as well.

Please Login or Register  to view this content.

To use the code in your workbook

1. Press Alt+F8 on your keyboard
2. Clear the macro name field and then type LoopThroughRow
3. Select the Create option
4. Between the Sub LoopThroughRow() and End copy and paste the above code.
5. Anything that appears in green is comment meant to help you understand what is taking place
6. Close out of Visual Basic
7. Press Alt+F8
8. Select the LoopThroughRow macro
9. Select Run