[Rob Bovey]

Hi Rohit,

Here's one way to do it:

Dim ecode As Range
Dim rngTable As Range
With ActiveSheet
Set rngTable = .Range(.Range("H4"), _
.Range("H65536").End(xlUp))
End With
For Each ecode In rngTable
With ecode
If Len(.Text) 0 Then
.Offset(, -3).Value = Sheets _
("Site Parameters").Range("N11").Value
End If
End With
Next ecode

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rohit Thomas" wrote in message
...
I have the following code to look through a range of cells
and populate another range if the text is not blank. My
code searches range H4:H201 everytime. Sometimes text may
only be in range H4:H20 or H4:H100. How can I make this
code more efficient to only look in the cells that have
text in column H starting with H4?

Dim ecode As Range
Range("H4:H110").Select
For Each ecode In Selection
With ecode
If Len(.Text) 0 Then
.Offset(, -3).Value = Sheets _
("Site Parameters").Range("N11").Value
End If
End With
Next ecode

Thanks,
Rohit

[Daniel.M[_2_]]

For Rohit,

Rob has answered your main problem.

Although you won't see a big difference here (200 cells max by your own
specs), you could put the N11 value in a temporary variable and use it in
the loop.

Dim TmpVal As Variant
Dim eCode As Range
Dim rngTable As Range
With ActiveSheet
Set rngTable = .Range(.Range("H4"), _
.Range("H65536").End(xlUp))
End With
TmpVal = Sheets("Site Parameters").Range("N11").Value
For Each eCode In rngTable
If Len(eCode.Text) 0 Then
eCode(1, -4).Value = TmpVal
End If
Next eCode

Regards,

Daniel M.

"Rob Bovey" wrote in message
...
Hi Rohit,

Here's one way to do it:

Dim ecode As Range
Dim rngTable As Range
With ActiveSheet
Set rngTable = .Range(.Range("H4"), _
.Range("H65536").End(xlUp))
End With
For Each ecode In rngTable
With ecode
If Len(.Text) 0 Then
.Offset(, -3).Value = Sheets _
("Site Parameters").Range("N11").Value
End If
End With
Next ecode

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rohit Thomas" wrote in message
...
I have the following code to look through a range of cells
and populate another range if the text is not blank. My
code searches range H4:H201 everytime. Sometimes text may
only be in range H4:H20 or H4:H100. How can I make this
code more efficient to only look in the cells that have
text in column H starting with H4?

Dim ecode As Range
Range("H4:H110").Select
For Each ecode In Selection
With ecode
If Len(.Text) 0 Then
.Offset(, -3).Value = Sheets _
("Site Parameters").Range("N11").Value
End If
End With
Next ecode

Thanks,
Rohit