You may want to add this line to remove the formula and leave the R
rngToCheck.Offset(0, 1) = rngToCheck.Offset(0, 1).Value

--
Don Guillett
SalesAid Software

"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