Alternative solutions for adding the first cell of the columns of a worksheet to a li

[VinoBob]

Hello!

The task is the following:

+ What we have: a block the first row is standing out of names and in the first column are also names. There can be any number rows and columns.

+ We would like to get the names from the first row into a multiselect listbox

+ Then we want to select some names from the listbox, and we would like to get a chart from the columns what comtain the selected names

I have only one idea how to do it:

Using the Chr() method, to specify the selected columns letter in a for loop :

Dim s As String 'The range to specify the source of the chart
Dim r As Integer 'The number of rows
Dim i As Integer

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
s = s + Mid(Chr(66+i), 1, 1) + "1:" + Mid(Chr(66+i), 1, 1) + Trim(Str(r)) + ","
End If
Next

This method is only suitable for columns from A to Z, because the columns are marked after that like AA,AB,AC etc..

I have checked the help, and i found Chartgroups, and as much as i understood from it is that the usage of Chartgroups could be provide another solution for this problem, but my knowledge in excel is not enough to understand what its all about. I dont want to learn excel, i just want a code, that makes it possible what i described above.

Can this problem be solved with chartgroups? Or am i totally searching on a wrong place?


Thanks in advance!

[MaximS]

Can you please post sample workbook?

[mbarron]

I think this will do it for you.
Left(Cells(1, 2).Offset(, i ).address(, 0), InStr(Cells(1, 2).Offset(, i ).address(, 0), "$") - 1)

For example, the following will enter the column headers across the top of a sheet - currently set up for XL2003 and prior:

Sub addressHeader()
Dim i As Long
For i = 1 To 256
Cells(1, i) = Left(Cells(1, 1).Offset(, i - 1).address(, 0), _
InStr(Cells(1, 1).Offset(, i - 1).address(, 0), "$") - 1)
Next

End Sub