Oops. I didn't notice that the columns could vary position:

Option Explicit
Sub testme02()

Dim FoundCell1 As Range
Dim FoundCell2 As Range
Dim FoundCell3 As Range
Dim FoundCell4 As Range

Dim rng As Range

Dim WhatToFind1 As String
Dim WhatToFind2 As String
Dim WhatToFind3 As String

WhatToFind1 = "Acct"
WhatToFind2 = "Entry"
WhatToFind3 = "4J6N"

With Worksheets("sheet1")

With .Rows(1)
Set FoundCell1 = .Cells.Find(What:=WhatToFind1, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set FoundCell2 = .Cells.Find(What:=WhatToFind2, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell1 Is Nothing _
Or FoundCell2 Is Nothing Then
MsgBox "Headers not found in row 1!"
Exit Sub
End If

With FoundCell1.EntireColumn

Set FoundCell3 = .Cells.Find(What:=WhatToFind3, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set FoundCell4 = .Cells.Find(What:=WhatToFind3, _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If FoundCell1 Is Nothing Then
MsgBox WhatToFind3 & " not found in the correct column"
Exit Sub
End If
End With

Set rng = .Range(FoundCell3, FoundCell4)

Set rng = rng.Offset(0, FoundCell2.Column - FoundCell1.Column)

Application.Goto rng

End With

End Sub

<<snipped

achidsey wrote:

Excel Experts,

I would like to select a range offset to the right from the original range
based on the column headings.

My spreadsheet is similar to the following

A B C D
1 Acct Entry
2
3 4J6N 15
4 4J6N 15
5 4J6N 15
6 4J6N 15
7

Set St4J6N = Cells.Find(What:="4J6N").Select

Range("A100").Select

Set End4J6N = Cells.Find(What:="4J6N", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)

Set rng4J6N = Range(St4J6N, End4J6N)

Set EntryS = Cells.Find(What:="Entry")

trd4J6N.Offset(, D1EntryS.Columns.Count - trd4J6N(1).Columns.Count).Select

End Sub

The final line isn't working. My goal is to select cells C3..C6.

What code will do this?

Thanks in advance.

Alan

--
achidsey

--

Dave Peterson

--

Dave Peterson