variable columns

[msnook]

I have written a macro that loops through a column of data finding matching entries. Where a = the starting value I want to match and c becomes the end of the matching entries.

MySup = Range("B" & a)
For y = a To b
If Range("B" & y) <> MySup Then
c = y - 1
y = b
End If
Next y

This works well as long as the data I want to use is in column B but I am trying to set this up so that others can use the spreadsheet and not have to worry about column arrangment.

I can find the specific column with this code

Cells.Find(What:="Supervisor", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
col1 = ActiveCell.Column

Is there a way to use the variable col1 (or any variable for that matter) to specify the column I am looking for.

I want to locate the specific column and starting with "a" and sequentially run through it "1-5000" looking for matches to MySup.

I thank everyone in advance for assistance.

 

[SkipVought]

hi,

    Dim rFound As Range

    Set rFound = Cells.Find(What:="Supervisor", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not rFound Is Nothing Then
        MySup = Cells(a, rFound.Column)
        For y = a To b
          If Range("B" & y) <> MySup Then
            c = y - 1
            y = b
          End If
        Next y
    Else
        MsgBox "Not found"
    End If
    
    Set rFound = Nothing

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[msnook]

I see how that sets the MySup value but can I use the same language in the "y" loop.

for y=a to b
if Cells(a, rFoundColumn) <> MySup then
c=y-1
y=b
End if
next y

 

[SkipVought]

did you try it?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[msnook]

I did, it worked beautifully. Thank you for the assistance.

For r = a To b
MySup = Cells(a, rSup.Column)

For y = a To b
If Cells(y, rSup.Column) <> MySup Then
c = y - 1
y = b
End If
Next y