So you have multiple columns in your selection and you want the header from the
second column of that selection?

(I still don't know what you want to do.)

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range
Dim my2ndHeader As Range

Set myRng = Nothing
With Worksheets("sheet1")
On Error Resume Next
Set myRng = Intersect(.UsedRange, Selection)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "Please select cells in the used range."
Exit Sub
End If

Set my2ndHeader = Nothing

Select Case myRng.Areas.Count
Case Is 2
MsgBox "Please select no more than 2 areas"
Case Is = 1
If myRng.Columns.Count < 2 Then
MsgBox "Please select two columns"
Else
Set my2ndHeader = myRng.Cells(1, 1).Offset(0, 1)
End If
Case Is = 2
If myRng.Areas(1).Row < myRng.Areas(2).Row Then
MsgBox "Please start in the same row"

ElseIf myRng.Areas(1).Rows.Count _
< myRng.Areas(2).Rows.Count Then
MsgBox "Please make each area in the" & _
" selection the same number of rows"

ElseIf myRng.Areas(1).Columns.Count _
< myRng.Areas(2).Columns.Count Then
MsgBox "Please make each area the same number of columns"

ElseIf myRng.Areas(1).Columns.Count < 1 Then
MsgBox "Please select one column in each area"

Else
Set my2ndHeader = myRng.Areas(2).Cells(1, 1)

End If
End Select

If my2ndHeader Is Nothing Then
'do nothing
Else
MsgBox my2ndHeader.Address & vbLf & my2ndHeader.Value
End If

End Sub

If this doesn't help, how about just describing what you're trying to do.


wrote:

I have a spreadsheet with 26 columns and 94 rows. The first row consists of
headers(labels) for each column of numbers. Let's call the column labels "A"
through "Z"

If I use the below code and select columns V and Z in that order the MsgBox
gives me the following values:

iDataRowsCt=94
kk=95 sColLabel1=V sColLabel2=Z
rngDataSource(1)=V rngDataSource(iDataRowsCt+1)=nothing

When I tried rngDataSource(1,2) then rngDataSource(1,2)=U even though column U
was not selected.

The "For Each MyCell" loop seems to skip the unselected columns and find the
selected column labels. However, I can't seem to access the 2nd selected
columns label directly with array numbers because it displays the unselected
column values as shown in the output above.

How do you address only the selected column values, namely the 2nd selected
column's label?

Thanks

Dennis
========================Code====================== =============
Set rngDataSource = Intersect(ActiveSheet.UsedRange, Selection)
iDataRowsCt = rngDataSource.Rows.Count
Dim mycell As Range
Dim ii As Long, jj As Long, kk As Long
Dim myName As String, sColLabel1 As String, sColLabel2 As String
ii = 0
For Each mycell In rngDataSource
jj = jj + 1
If Not IsNumeric(mycell.Value) Then
ii = ii + 1
kk = jj
myName = myName & mycell.Value
If ii = 1 Then sColLabel1 = mycell.Value
If ii = 2 Then sColLabel2 = mycell.Value
End If
Next mycell
MsgBox "idatarowsct=" & iDataRowsCt & vbLf & _
"kk=" & kk & " " & sColLabel1 & " " & sColLabel2 & vbLf & _
rngDataSource(1).Value & " " & rngDataSource(iDataRowsCt+1).Value

Dave Peterson wrote:

I'm not sure why the second column's label isn't in the same row as the first
column's label.

I've read your code and I don't quite understand the way the data would be laid
out.

But if looping works, that's ok with me.

wrote:

Nope this didn't work.

When I selected columns X and V in that order, the code below produced column X
row 1 value and column Y(not V) row 1 value. It chooses the row 1 value of the
column next to the first selected column.

Set rngDataSource = Intersect(ActiveSheet.UsedRange, Selection)
MsgBox rngDataSource(1).Value & " " & rngDataSource(1).Offset(0, 1).Value

It seems the only way to do this is with a For loop?

Thanks

Dennis


Dave Peterson wrote:

How about:

msgbox rng(1).offset(0,1).value

(first cell of the range, same row, over one column to the right.)

If I understood correctly, that is...

wrote:

Thanks again Dave.

My columns consist of all numbers accept for the label in row 1.

I know I can get the first selected column's label by using rng(1).value .
However, in order to find the 2nd column's label I had to use a For loop and
test as in :
sColLabel1=rng(1).value
For Each myCell In rng
If Not IsNumeric(myCell.Value) Then
ii = ii + 1
myName = myName & myCell.Value
If ii = 2 Then sColLabel2 = myCell.Value
End If
Next myCell

Is there a one line faster way to get the second column label?

Thanks.

Dennis

--

Dave Peterson