none
Loop a range of blank (white) cells until a number is found RRS feed

  • Question

  • Hello,

    I am not a developer but need assistance with a formula/macro in Excel 2013 to loop through a range of blank cells until a number is found and when it is, stop looping and populate that number in a specific cell.  Please keep in mind I am not a developer and will need step by step details...loop through cell range W16:BN16, at the first cell in that range that has a number, any number, stop the loop and enter that number in cell J16. 

    LDVS



    • Edited by LDVS Tuesday, August 22, 2017 7:11 PM
    Tuesday, August 22, 2017 6:56 PM

All replies

  • First, select File > Options.

    Select Customize Ribbon in the navigation pane on the left.

    Make sure that the check box for Developer is ticked in the list of Main Tabs on the right.

    Click OK.

    You now have a Developer tab in the ribbon. Activate it.

    Click Visual Basic to activate the Visual Basic Editor (you can also press Alt+F11 if you prefer keyboard shortcuts).

    Select Insert > Module to create a new code module.

    Copy the following macro into the new module:

    Sub LoopUntilNotBlank()
        Dim cel As Range
        For Each cel In Range("W16:BN16")
            If cel.Value <> "" And IsNumeric(cel.Value) Then
                Range("J16").Value = cel.Value
                Exit For
            End If
        Next cel
    End Sub

    Close the Visual Basic Editor.

    Click Macros on the Developer tab of the ribbon.

    Select LoopUntilNotBlank, then click Run.

    Make sure that you save the workbook as a macro-enabled Excel workbook (.xlsm), and that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 22, 2017 8:53 PM