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


S

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
 
Ad

Advertisements

J

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
 
Ad

Advertisements

R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top