return next in sequence

  • Thread starter Thread starter Question Boy
  • Start date Start date
Q

Question Boy

I am trying to write, and have no clue how to start, a routine that I would
supply a value from a sequence and it would return the next logical value.

I am trying to automate Excel using vba and would pass a column value (A-Z.
AA, AB, AC,...) and would like to get the next column value. Ie I give G it
returns H, I give AO and it returns AP and so on.

Thank you for the help,

QB
 
try this:

Public Function GetNextColumn(ByVal oldCol As Variant) As String

Dim i As Integer
Dim done As Boolean
Dim allZs As Boolean
Dim nonZpos As Integer

If IsNull(oldCol) Then
MsgBox "Error: No value passed to function GetNextColumn"
Exit Function
End If

GetNextColumn = oldCol
Letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

i = Len(oldCol)
done = False
allZs = True
nonZpos = i

While (Not done)
If InStr(1, Letters, Mid(GetNextColumn, i, 1)) = 0 Then
msgbox "Invalid value '" & oldCol & "' passed to GetNextColumn"
GetNextColumn = vbNullString
Exit Function
End If

If Mid(GetNextColumn, i, 1) = "Z" Then
Mid(GetNextColumn, i, 1) = "A"
Else
nonZpos = i
done = True
allZs = False
End If

If i > 1 Then
i = i - 1
Else
done = True
End If
Wend

If Not allZs Then
pos = InStr(1, Letters, Mid(GetNextColumn, nonZpos, 1))
Mid(GetNextColumn, nonZpos, 1) = Mid(Letters, pos + 1, 1)
Else
GetNextColumn = "A" & GetNextColumn
End If


End Function

You should add standard error handling and you may want to modify some of
the error handling I have here. Also, the Letters variable should really be
defined as a public constant in a standard module. It will work the way it
is, but it's 'good programming practice' to define a value like that as a
constant. The argument to the function is the value of the column you want to
change, e.g. "AO". The function would return the next column value, e.g.
"AP". It should handle all possible values.
 
Back
Top