Return the column of the next empty cell

[Todd Huttenstine]

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

 

[Todd Huttenstine]

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.
---------------------------------------------------------- ----------------------------------------------------

.

 

[pfsardella]

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

 

[Matt]

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))