Macro to "find" the next empty cell in a range

[Stilltrader47]

Thanks for help writing the macro.

Assumption: No price values are currently entered in the price cell
range (“S&P 500â€). Range B56:AF133 cells are all empty.

Each time the macro is run the next empty cell in Range B56:AF133 will
become the active cell.

Desired Outcome: Each time I run the macro (ctl-f) it locates the next empty
cell in the range as the active cell. A price value can then be entered in
the found cell, or another forward cell in the range.

Below is a script of how the macro should function.

Action: Run macro.
Outcome: Cursor should land in cell B56 (since no cell in range are
populated with a value).

Action: Enter a value in cell E56 (Jan. 4).

Action: Run macro.

Outcome: Cursor should land in cell F56 (since it is the next empty
cell in range).

Action: Enter a value in cell L56 (Jan. 11).

Action: Run macro.
Outcome: Cursor should land in cell M56 (since it is the next empty
cell in range).


Thank-you

 

[Jacob Skaria]

Try the below macro..

Sub Macro()

Dim varRange As Range

On Error Resume Next
Set varRange = Range("B56:AF133").SpecialCells(xlCellTypeBlanks)(1, 1)
On Error GoTo 0
If varRange Is Nothing Then
Set varRange = Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
End If
varRange.Select

End Sub

 

[Rick Rothstein]

I think this should do what you want...

Sub FindNextEmptyCell()
On Error Resume Next
Range("B56:AF133").Find("").Select
If Err.Number Then Range("B56").Select
End Sub