Is there a better code to do this?
 

You can do the following

set MyRange = Range("A5:AK1757")

then use Range or colun again

Myrange.Range("A1") Really A5
or
MyRange.columns("A")

or

MyRange.Rows(1) really row 1


to get last column

MyRange(Columns.Count)

For the code to take 30 minutes you must be looping through the range
multiple times. Because you have two FOR loops it loos like you are looping
so you are going through each cell AxA times where A is the number of cell
in your range. I'm not sure what you are really trying to do.


"Ayo" wrote:

I am trying to do a Find and Replace in Range("A5:AK1757") and I have the
following code to do it but it is just taking too long, more than 30 minutes.
IS there a better way to do this faster?
Thanks

For Each progresshdr In rngProgresshdr.Cells
proPos = InStr(1, progresshdr.Address(ColumnAbsolute:=False), "$",
vbTextCompare)
progresslastColumn = Left(progresshdr.Address(ColumnAbsolute:=False),
proPos - 1)

If Right(progresshdr.Value, 4) = "Date" Then
ActiveSheet.Range(progresslastColumn & "5:" & progresslastColumn &
progresslastRow).Select
For Each c In Selection
If c.Value <= Date And c.Offset(0, 1).Value = "Projected" Then
c.Offset(0, 1).Value = "Past Due"
End If
Next
End If
Next progresshdr