Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improve code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improve code | Excel Programming | |||
Improve code | Excel Programming | |||
How to optimize and improve that code ? | Excel Programming | |||
Improve ADO code, Export Excel to Access | Excel Programming | |||
How to improve this code? | Excel Programming |