if then else?

[sufianmalik]

hi there

importing a report into excel, it contains rows of numbers with subtotals.

all the rows where the number is between 40 and 60 must be changed to 40. All of these rows have a resource name and was using this in a do loop until blank. also need another do loop to keep going to each column until blank

all subtotals to remain untouched

all values greater than 40 to remain untouched.

i'm sure there's a much better way, if anyone can suggest?

Look forward to your response

Suf


Here's what i have so far....


Data:
sufian 40 70 40 40
bob 40 20 40 75
fred 45 50 31 56



Example code....

Sub between40and60()
'
Range("c5").Select

Do Until ActiveCell.Offset(1, 0).Value = ""

ActiveCell.Offset(0, 1).Select

If ActiveCell.Value > 40 And ActiveCell.Value <= 60 Then

ActiveCell.Value = 40

End If

Loop

Range("c5").Offset(1, 0).Select

Do Until ActiveCell.Offset(0, 1).Value = ""

If ActiveCell.Offset.Value > 40 And ActiveCell.Offset.Value <= 60 Then

ActiveCell.Offset.Value = 40

End If


Loop

ActiveCell.Offset(1, 0).Select





End Sub

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Cell As Range
    For Each Cell In Range("C5").CurrentRegion
        If IsNumeric(Cell.Value) And Cell.Value > 40 And Cell.Value <= 60 Then
            Cell.Value = 40
        End If
    Next Cell
End Sub

 

[sufianmalik]

thanks but current region picks up the subtotals

what can i do about them?

 

[sufianmalik]

how about naming a range?

how could i use that?

 

[sufianmalik]

ok that makes sense, thanks

i guess if my records increase..i will have to adjust this