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

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

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

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


--
Jacob (MVP - Excel)


"Stilltrader47" wrote:


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

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

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

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...

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