Excel basic - column reference 1

[jakefwilson]

Hi,

I'm using a macro that includes the following code:

Columns("K:K").Select

This is fairly static, and unusable in worksheets in which the column I'm interested in is not "K". Is there any way to perform this selection via reference to the column label (my data has a header row)? By column label I mean the text contained in the topmost cell of the column.

Thank you,
Jake

 

[SkipVought]

Hi,

If your heading is in row 1 and the heading value is "Address" then..

With ActiveSheet
   with .Columns(.Rows(1).Find("Address").Column)
    'do whatever to the column
   end with
End With

I advise against the overuse of the Select and Activate methods.





Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[jakefwilson]

Thanks Skip for your reply. I think I'm almost there...

Once I've found the column in question, I need to be able to navigate through its cells as well as the cells in one of the columns next to it. I figured I would use ActiveCell offset logic in order to accomplish this. Here's a snippet from the code I tried:

Dim r1 as range
With ActiveSheet

For Each cell in r1
    'I get a "range" of the column in question...
    Set r1 = .Columns(.Rows(1).Find("Address").Column)
    For Each cell In r1
        If cell.Value = "" Then
            Exit For
        End If

    ...

Next

...

End With

When I run this, however, I get a "type mismatch" error in the comparison (cell.Value = ""). I've tried a variety of things, but nothing seems to work. This attempt is based off of examples I've found on the web.

In addition, are the cells in the above example considered "active"? Can I use ActiveCell offset logic to move among them?

Thanks again,
Jake

 

[SkipVought]

    Dim r1 As Range
    With ActiveSheet
    
        For Each r1 In .Columns(.Rows(1).Find("Address").Column)
           With r1
                If .Value = "" Then  Exit For
                AdjacentCellValue = .Offset(0, 1).Value 
            End with
        Next
    
    
    End With

Skip,

_{[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount!}

 

[jakefwilson]

Thanks again Skip, the help is appreciated.

I copied the snippet above verbatim (after changing "Address" to the actual value) and still got the same "run-time error '13': Type Mismatch". When I select "Debug" in the error messagebox, the following code is highlighted:
If .Value = "" Then

For what it's worth, I'm running Excel 2000, VBA 6.3, on Windows XP.

here is my code, copied directly from the VBA editor:

Sub genNormalizedFitness_labeled()
    Dim r1 As Range
    With ActiveSheet
    
        For Each r1 In .Columns(.Rows(1).Find("fitness_mean").Column)
           With r1
                If .Value = "" Then Exit For
                AdjacentCellValue = .Offset(0, 1).Value
            End With
        Next
    End With
End Sub

Please let me know if you see any errors in the above, or have further suggestions. I have little experience with Excel VBA, so I might be missing very basic things.

Thanks,
Jake

 

[SkipVought]

sorry...

Sub genNormalizedFitness_labeled()
    Dim r1 As Range, AdjacentCellValue, iCol As Integer
    With ActiveSheet
        iCol = .Rows(1).Find("fitness_mean").Column
        For Each r1 In .Range(.Cells(2, iCol), .Cells(2, iCol).End(xlDown))
           With r1
                If .Value = "" Then Exit For
                AdjacentCellValue = .Offset(0, 1).Value
            End With
        Next
    End With
End Sub

this will only work if the data in column, fitness_mean, contains contiguous data. Otherwise another technique must be used.

Skip,

_{[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount!}