Excel: Looping through rows to enter data in empty cell

Posted on 2012-09-21
Last Modified: 2012-09-23
Dear Experts,

A little stuck here... Please see attached screenshot:
When item is selected (red arrow) vba code (Worksheet_SelectionChange) runs a vlookup to enter data into cell L3. Would like then to run a looping structure to find an empty cell between A10 through A16 to enter that code. Each time I invoke the SelectionChange event I would like to keep populating any empty cell. Should the rows between A10 and A16 fill up I would like to start replacing values beginning with A10.  
Thanks in advance for looking at this.

Here is the code I currently have but of course as you can see my current do while loop enters values into Cell A10 through A16 which is not what I want to do. I want to enter the value only once into any empty cell between those rows.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, [M8:U65]) Is Nothing Then

    'MsgBox Target.Address
    Dim lookFor As Range
    Dim rng As Range
    Dim col As Integer
    Dim found As Variant
    Dim ws As Worksheet
    Dim row As Integer
    Set ws = ActiveWorkbook.Sheets("Report")
    row = 10
    Set lookFor = Sheets("Report").Range(Target.Address)
    Set rng = Sheets("Number and Operations").Columns("A:B")
    col = 2
    On Error Resume Next
    found = Application.WorksheetFunction.VLookup(lookFor.Value, rng, col, 0)
    If IsError(found) Then
    MsgBox lookFor & " not found"
    Else: Range("L3").Value = found
       'MsgBox "The look-up value of " & lookFor & " is " & found & " in column " & col
    End If
    On Error GoTo 0
     ws.Cells(row, 1).Value = Range("L3")
     row = row + 1
    Loop While row < 17
    row = 10
End If

End Sub

Open in new window

Question by:shogun5
    1 Comment
    LVL 10

    Accepted Solution

    hi shogun,

    i have modified your do loop statement to populate only the first blank cell in your range.

        if len(ws.Cells(row,1).Value) = 0 then 'a blank cell has be found
            'populate blank cell
            ws.Cells(row, 1).Value = Range("L3")
            'exit loop 
            exit do
        end if
        row = row + 1
    Loop While row < 17

    Open in new window


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now