• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

Excel: Looping through rows to enter data in empty cell

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

1 Solution
Anthony BerenguelCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now