Return the column of the next empty cell
 

Hey guys I think this is a simple one. I have Range
A4:Z4. There can be as many as 26 total values in this
range (1 value per cell). There are currently 21 values
in Row4, which means currently the last value is in
ColumnU. The next data I enter needs to go in ColumnV
(Cell V4).

Heres my question:
What would the formula be to return the next empty Column
in Row4? For instance, in this case the formula would
need to return the value "V" because the next empty column
in Range A4:Z4 is columnV.

Todd Huttenstine

Return the column of the next empty cell
 

Beautiful! Thank you, it works perfect.

Todd



-----Original Message-----
Sub FindColumn()
Dim intCol As Integer
Dim strCol As String
intCol = Range("A4").End(xlToRight).Offset(0,
1).Column
strCol = ConvertNumberToLetter(intCol)
MsgBox strCol
End Sub

Function ConvertNumberToLetter(intNum As Integer) As
String
'' Convert a column number to letter.
ConvertNumberToLetter = _
Left(Columns(intNum).Address(False, False), _
2 + 1 * (Columns(intNum).Column < 27))
End Function

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
----------------------------------------------------------
----------------------------------------------------
Be advised to back up your WorkBook before attempting to
make changes.
----------------------------------------------------------
----------------------------------------------------

Hey guys I think this is a simple one. I have Range
A4:Z4. There can be as many as 26 total values in this
range (1 value per cell). There are currently 21 values
in Row4, which means currently the last value is in
ColumnU. The next data I enter needs to go in ColumnV
(Cell V4).

Heres my question:
What would the formula be to return the next empty
Column
in Row4? For instance, in this case the formula would
need to return the value "V" because the next empty
column
in Range A4:Z4 is columnV.

Todd Huttenstine

.

Return the column of the next empty cell
 

Sub FindColumn()
Dim intCol As Integer
Dim strCol As String
intCol = Range("A4").End(xlToRight).Offset(0, 1).Column
strCol = ConvertNumberToLetter(intCol)
MsgBox strCol
End Sub

Function ConvertNumberToLetter(intNum As Integer) As String
'' Convert a column number to letter.
ConvertNumberToLetter = _
Left(Columns(intNum).Address(False, False), _
2 + 1 * (Columns(intNum).Column < 27))
End Function

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hey guys I think this is a simple one. I have Range
A4:Z4. There can be as many as 26 total values in this
range (1 value per cell). There are currently 21 values
in Row4, which means currently the last value is in
ColumnU. The next data I enter needs to go in ColumnV
(Cell V4).

Heres my question:
What would the formula be to return the next empty Column
in Row4? For instance, in this case the formula would
need to return the value "V" because the next empty column
in Range A4:Z4 is columnV.

Todd Huttenstine

Return the column of the next empty cell
 

Hi

Can you tell me about the logic behind this formula cause I am not seeing it
Left(Columns(intNum).Address(False, False), _
2 + 1 * (Columns(intNum).Column < 27))



"Todd Huttenstine" wrote in message ...
Hey guys I think this is a simple one. I have Range
A4:Z4. There can be as many as 26 total values in this
range (1 value per cell). There are currently 21 values
in Row4, which means currently the last value is in
ColumnU. The next data I enter needs to go in ColumnV
(Cell V4).

Heres my question:
What would the formula be to return the next empty Column
in Row4? For instance, in this case the formula would
need to return the value "V" because the next empty column
in Range A4:Z4 is columnV.

Todd Huttenstine