Improve code
 

Sub NewCode2()
Dim rngToCheck As Range
Set rngToCheck = Range("B2", Range("B65536").End(xlUp))
rngToCheck.Offset(0, 10).Formula = "=IF(B2=1,"R",na())"
On Error Resume Next
set rng = Range("L:L").specialCells(xlformulas,xlErrors)
On Error goto 0
If not rng is nothing then
rng.clearcontents
end if
set rng = Range(cells(1,"L"),Cells(rows.count,"L").End(xlup) )
rng.formula = rng.Value
End Sub

This will fail if you have more than 8192 discontiguous cells (separate
areas) that contain an R, but this is highly unlikely I would think.

--
Regards,
Tom Ogilvy


"Rob Bovey" wrote in message
...

Hi Gareth,

It's early here, and I'm pretty sure I misunderstood what you were
trying to do. <g My previously posted code only works for a single

instance

of the number 1 in column B. If you have multiple instances of the
number

1,

there's no better VBA solution than some variation on looping the
whole
column.

You could make it run faster by using the Application.Match
technique

to

skip over cells more quickly, but it makes the code more complicated
and
you'll still have the potentially long loop. Why don't you just enter
a
worksheet formula in column L to do the work for you? The following
procedure does this and runs very fast:

Sub NewCode2()
Dim rngToCheck As Range
Set rngToCheck = Range("B2", Range("B65536").End(xlUp))
rngToCheck.Offset(0, 10).Formula = "=IF(B2=1,""R"","""")"
End Sub

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

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Rob Bovey" wrote in message
...

Hi Gareth,

This method is quite a bit more efficient:

Sub NewCode()
Dim rngToCheck As Range
Dim vMatch As Variant
Set rngToCheck = Range("B2", Range("B65536").End(xlUp))
vMatch = Application.Match(1, rngToCheck, False)
If Not IsError(vMatch) Then
rngToCheck.Cells(vMatch, 1).Offset(0, 10).Value = "R"
End If
End Sub

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

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Gareth" wrote in message
...

There must be a better/quicker way of putting an R in
column L if column B has a 1 in it, at the moment I have
the following:

Sub Macro1()
Dim cell As Range
For Each cell In Range("B2:B" & Range("B65536").End
(xlUp).Row)
If cell.Value = 1 Then
cell.Offset(0, 10).Value = "R"
End If
Next cell
End Sub

The sheet may have several thousand rows.

TIA.

Gareth