Update of sheet does not cause action until I run vba module twice

Question

New to VBA I'm confused as to why I need to run my module twice to get it to update my cells. My code:

Option Explicit

Sub m_Range_End_Method()
Dim lRow As Long
Dim lCol As Long
Dim currentRow As Long
Dim i As Integer
Dim rng As Range

Set rng = ActiveCell
Range("B:B").Select

lRow = Cells(Rows.Count, 1).End(xlUp).Row

lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("MySheet").Select

' Loop Through Cells to set description in each cell
    Do While rng.Value <> Empty
        currentRow = ActiveCell.Row
        If InStr(rng.Value, "PETROL") = 0 Then
            Set rng = rng.Offset(1)
            rng.Select
        Else
            Worksheets("MySheet").Cells(currentRow, 5) = "Shopping"
            Worksheets("MySheet").Cells(currentRow, 6) = "Car"
            Set rng = rng.Offset(1)
            rng.Select
        End If
   Loop

End Sub

On the first run what happens in Excel 2016 is that Column B gets highlighted and that's it. I then have to press "Run" again in visual basics editor for it to then update all the entries at which point column B gets unselected. All I want to do is update the cells at the currentRow of a specified worksheet. I've been reading but have got myself into some confusion, someone said I should use the

Range("B:B").Select

statement and for some reason the spreadsheet update works but only if I run it twice. Without this Range command, for reasons I don't understand, the spreadsheet doesn't update - all that happens is that the box selection moves to entries with Petrol and stays there with the program running but not updating.

The aim of the program is to find in a sheet all occurrences of a word in column B, in this initial case that is PETROL (I'm going to expand to include many others). For that match on the same row I want it to update columns 5 and 6 with descriptions. The excel spreadsheet will have hundreds of rows of entries with varying descriptions in column B.

Any help would be much appreciated.

A friendly advice. You will not make any serious step forward until you get rid of using `anything.select` in your code, and start to manipulate qualified ranges directly. Additional problem is that even an experienced VBA programmer who is willing to help gets lost when reading this stuff. — A.S.H, May 14 '17 at 14:25

score 1 · Accepted Answer · edited May 23 '17 at 11:47

I guess you have to run it twice because the first time you run it, the ActiveCell could be anything, and your loop depends on it not being empty to start with, but after the first run you have selected column B (and other things)...

Read this previous answer on avoiding the use of Select and Activate, it will make your code more robust: How to avoid using Select in Excel VBA macros

Revised Code

See the comments for details, here is a cleaner version of your code which should work first time / every time!

Sub m_Range_End_Method()
    Dim col As Range
    Dim rng As Range
    Dim currentRow As Long
    ' Use a  With block to 'Fully Qualify' the ranges to MySheet
    With ThisWorkbook.Sheets("MySheet")
        ' Set col range to the intersection of used range and column B
        Set col = Intersect(.UsedRange, .Columns("B"))
        ' Loop through cells in col to set description in each row
        For Each rng In col
            currentRow = rng.Row
            ' Check upper case value match against upper case string
            If InStr(UCase(rng.Value), "PETROL") > 0 Then
                .Cells(currentRow, 5) = "Shopping"
                .Cells(currentRow, 6) = "Car"
            End If
        Next rng
    End With
End Sub

Update of sheet does not cause action until I run vba module twice

1 Answers1