IF/THEN/ELSE for sequential numbers

[juggler]

I have a series of sequential numbers that I want to output as two
parts of a whole component.

Example: 1, 2, 3, 4, 5, 6, etc would become 1A, 1B, 2A, 2B, 3A, 3B,
etc.

I was thinking of assigning odd/even for A/B, but then I come out with
1A, 2B, 3A, 4B, etc. With 20 or more numbers it becomes even more
unwieldy.

Suggestions?

 

[Mike H]

Juggler,

I'm not sure what you mean by output but the code below takes a range of
numbers and appends an A and a B to each in the 2 columns to the right.

Sub addanAorB()
Dim myRange As Range
Set myRange = Range("A1:A10")
For Each c In myRange
c.Select
ActiveCell.Offset(0, 1).Value = c.Value & "A"
ActiveCell.Offset(0, 2).Value = c.Value & "B"
Next
End Sub

Mike

 

[Tom Ogilvy]

Just an additional approach.

Sub AA()
Dim i As Long, j As Long
Dim s As String
For i = 1 To 6
For j = 65 To 66
s = s & i & Chr(j) & ","
Next j
Next i
MsgBox Left(s, Len(s) - 1)
End Sub

 

[juggler]

Juggler,

I'm not sure what you mean by output but the code below takes a range of
numbers and appends an A and a B to each in the 2 columns to the right.

Sub addanAorB()
Dim myRange As Range
Set myRange = Range("A1:A10")
For Each c In myRange
c.Select
ActiveCell.Offset(0, 1).Value = c.Value & "A"
ActiveCell.Offset(0, 2).Value = c.Value & "B"
Next
End Sub

Mike








- Show quoted text -

I am trying to alter the numbering sequence, not just add an 'A' or
'B'. In a string of numbers such as 1,2,3,4,5,6,7,8,9,10 I want to end
up with 1A, 1B, 2A, 2B, 3A, 3B, 4A, 4B, 5A, 5B.

For example, the problem in writing the code is how I identify that I
want the numeral 9 to change to 5A?

 

[NickHK]

Try this function. You should add error/array checking.

Private Function ReSequence(SequenceCount As Long, CharList As Variant) As
String()
Dim j As Long
Dim Counter As Long
Dim Index As Long
Dim OutPut() As String

ReDim OutPut(1 To SequenceCount)

Do
Counter = Counter + 1
For j = LBound(CharList) To UBound(CharList)
Index = Index + 1
If Index > SequenceCount Then GoTo Finish
OutPut(Index) = Counter & CharList(j)
Next
Loop

Finish:
ReSequence = OutPut()

End Function

And call it with something like:

Private Sub CommandButton1_Click()

Const Chars As String = "A,B"
'Const Chars As String = "i,ii,iii"
Selection.Value = Application.Transpose(ReSequence(Selection.Cells.Count,
Split(Chars, ",")))

End Sub

NickHK