Improve code

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

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" <anonymous@discussions.microsoft.com> wrote in message
news:09e801c545aa$587dbf40$a601280a@phx.gbl...
> 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

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" <Rob_Bovey@msn.com> wrote in message
news:Of8il2aRFHA.2964@TK2MSFTNGP15.phx.gbl...
> 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" <anonymous@discussions.microsoft.com> wrote in message
> news:09e801c545aa$587dbf40$a601280a@phx.gbl...
>> 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
>
>

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
donaldb@281.com
"Rob Bovey" <Rob_Bovey@msn.com> wrote in message
news:u9LKC9aRFHA.3628@TK2MSFTNGP12.phx.gbl...
> 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" <Rob_Bovey@msn.com> wrote in message
> news:Of8il2aRFHA.2964@TK2MSFTNGP15.phx.gbl...
> > 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" <anonymous@discussions.microsoft.com> wrote in message
> > news:09e801c545aa$587dbf40$a601280a@phx.gbl...
> >> 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
> >
> >
>
>

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" <Rob_Bovey@msn.com> wrote in message
news:u9LKC9aRFHA.3628@TK2MSFTNGP12.phx.gbl...
> 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" <Rob_Bovey@msn.com> wrote in message
> news:Of8il2aRFHA.2964@TK2MSFTNGP15.phx.gbl...
> > 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" <anonymous@discussions.microsoft.com> wrote in message
> > news:09e801c545aa$587dbf40$a601280a@phx.gbl...
> >> 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
> >
> >
>
>

Simple and Fast. Hope it will help.

Public Sub RowForRs()
Dim X As Integer
For X = 1 To 6000 Step 1
If Cells(X, 2) = 1 Then Cells(X, 12) = "R"
Next X
End Sub