how to increment the Excel column index in vb

[vviti]

I am trying to copy data columns from multiple files into an Excel sheet, each data column to be added to a new column in my active sheet. How can I increment the column index in a DO loop? I tried two different approaches:

1)
increment = 1
Column = "E " + increment.ToString() <= this is not working!
newColumnName = Column & ":" & Column
Columns(newColumnName).Select

2) using the command
columnIndex=i
Cells( , columnIndex).select <= this select only row 1 in column columnIndex

I'd appreciate suggestions/help.

Thanks!

V

 

[PHV]

Column = "E" & increment

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vviti]

Hi PHV and thanks for your reply.

However, when I do this the result is that rather than seeing column "F" as desired I see column "E1" which is an error:


Column = "E" & increment
newColumnName = Column & ":" & Column

Result: newColumnName="E1:E1"

I tested with increment defined as String and as Integer and using "E" + increment and "E" & increment and the result is the same.

Do you have any suggestion?

Thanks,

V

 

[vviti]

placeholder!

Still in need of know-how!

V

 

[Skie]

I didn't test this, but theres always the following option. It should work for columns up to "ZZ" (and I don't think they get that big). I wouldn't imagine you'd go past column "Z", but this code will take you to the double-alpha columns.

Column = "E"
ColumnInc = Right(Column,1)
ColumnInc = Chr(Asc(ColumnInc) + 1)
If ColumnInc > "Z" Then
  ColumnInc = Chr(Asc(ColumnInc) - 26)
  If Len(Column) = 1 Then
    Column = "A" & ColumnInc
  Else
    ColumnLeft = Left(Column,1)
    ColumnLeft = Chr(Asc(ColumnLeft) + 1)
    Column = ColumnLeft & ColumnInc
  End I
Else
  If Len(Column) = 1 Then
    Column = ColumnInc
  Else
    Column = Left(Column,1) & ColumnInc
  End If
End If