On Mon, 6 Jul 2009 11:16:37 -0700 (PDT), Curious
wrote:

Thanks Lars-Åke! It works.

It works fine until it loops to a row with column H whose value is "#N/
A", where I get a "Type mismatch" error at runtime. I thought I had
filered out these rows by using an IF statement ("If ActiveSheet.Cells
(i, "H").Value < "#N/A" Then" ).

Anyway, my code is below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount
If ActiveSheet.Cells(i, "E").Value = "S" Then
If ActiveSheet.Cells(i, "H").Value < "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Max(ActiveSheet.Cells
(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
Else
If ActiveSheet.Cells(i, "E").Value = "B" Then
If ActiveSheet.Cells(i, "H").Value < "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Min
(ActiveSheet.Cells(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

How to resolve the issue about a column having both numeric and text
value? Also how can I know at which line I get the "Type mismatch
error"? Please help me.

Change

If ActiveSheet.Cells(i,"H").Value < "#N/A" Then

to

If Not IsError(ActiveSheet.Cells(1, "H").Value) Then

You can use the debugger to print the value of variable i when your
macro stops due to an error.
(Press CTRL+G and type ?i in the Immediate window.)

Hope this helps / Lars-Åke