How to create VBA code to find Next empty column and next empty row

How to create VBA code to find next empty column and next empty row

Requirement:

The user has started an application in Excel.

Column 1 or A contains codes of people names.

The user wants the vba code to find the next empty column (which will be B).

The user wants to place data in B until i have reached the length of FILLED column A (Find the next empty row in column B).

Only then the user wants the code to find the next empty column.

The code to find the next empty column is as follows and works just fine:

Dim NextEmptyCol As Long
NextEmptyCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column + 1

MsgBox "Column Number" & " " & NextEmptyCol & vbCr & _
"Or column letter """ & Replace(Cells(1, NextEmptyCol).Address(0, 0), 1, "") & """", vbInformation, "The next empty Column is ..."

A	B	C	D	E
Teachers
AC
PT
JC
WT

The user needs assistance filling column B with data until the length of filled column A had been reached.

Solution:

You can navigate the cell like...

Code:

Sub data_Input()
 Dim lngRow As Long
 Dim lngCol As Long
 Dim lngRowInput As Long
 Dim lngColInput As Long

 lngRow = Cells(Rows.Count, "A").End(xlUp).Row
 lngCol = Cells(2, Columns.Count).End(xlToLeft).Column
 lngRowInput = Cells(Rows.Count, lngCol).End(xlUp).Row

If lngRowInput >= lngRow Then
   lngColInput = lngCol + 1
   lngRowInput = 2
Else
 lngColInput = lngCol
 lngRowInput = lngRowInput + 1
End If

Cells(lngRowInput, lngColInput) = "x"
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by pike.