ok. here is my question:
1. I have 16 columns with cells having values between 0 to 25. I want
to substitute the values in this manner: if value is ‰¤3 then
substitute it with 1. if value 3 ‰¤7 then substitute it with 2, and so
on and so forth. the condition for each column will be different. for
e.g. I may have a condition in column 2 where I would want value ‰¤ 5
substituted by 1 and 5 ‰¤ 8 substituted by 2, etc.

Hello,

Sounds dangerous. How would you see were a program stopped (by chance
or error)? What if you insert rows? No solution for your stated
problem could be rerun...

I suggest to introduce helper columns which show your desired results.
The worksheet function of your choice should be LOOKUP then.

In general: separate your input from your output.

Regards,
Bernd

hi bernd,
could you suggest how to go about doing this. i dont mind a seperate
input and output. or even inserting rows. also, i am a complete newbie
to excel macros so please if you could explain in a simpler manner,
i'd be really grateful.

Bernd wrote:
Hello,

Sounds dangerous. How would you see were a program stopped (by chance
or error)? What if you insert rows? No solution for your stated
problem could be rerun...

I suggest to introduce helper columns which show your desired results.
The worksheet function of your choice should be LOOKUP then.

In general: separate your input from your output.

Regards,
Bernd

Hello again,

If your input values are in columns A:P, then enter into ...
Q1:
=LOOKUP(A1,{0,3.001,7.001},{1,2,3})
R1:
=LOOKUP(B1,{0,5.001,8.001},{1,2,3})
and copy down as far as necessary, for example.

Have a look into Excel's help on LOOKUP.

A more precise approach could even be in Q1:
=LOOKUP(-A1,{-1E+304,-7,-3},{3,2,1})

[Imagine what happens if your input would be 7.0001 in cell A1. Test
it with both formulas.]

Regards,
Bernd

Hi there,

Bernd is correct in what he says. Make sure you have a backup of your data.
However, the following code would process one column (Column A) and you would
have to adapt it from there because you said that different conditions apply
to each column. Probably copies of the code and change case conditions for
each column.

Option Explicit

Sub Substitute_Values()
Dim cel1 As Object
Dim rng1 As Range

Sheets("Sheet1").Select
Set rng1 = Range("A:A")

For Each cel1 In rng1
If cel1.Value = 0 Then Exit For 'No more data
Select Case cel1.Value
Case 1 To 3
cel1.Value = 1
Case 4 To 7
cel1.Value = 2
Case 8 To 11
cel1.Value = 3
Case 12 To 18
cel1.Value = 4
Case 19 To 25
cel1.Value = 5
End Select
Next cel1
End Sub