ExcelBanter - Offset based on column heading

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Offset based on column heading (https://www.excelbanter.com/excel-programming/347003-offset-based-column-heading.html)

Offset based on column heading

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

Offset based on column heading

One way:

Option Explicit
Sub testme02()

Dim FoundCell1 As Range
Dim FoundCell2 As Range
Dim rng As Range
Dim WhatToFind As String

WhatToFind = "4J6N"

With Worksheets("sheet1")

With .Range("a:a")
If Application.CountIf(.Cells, WhatToFind) < 2 Then
MsgBox "not enough to find!"
Exit Sub
End If

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

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

End With

Set rng = .Range(FoundCell1, FoundCell2).Offset(0, 2)

Application.Goto rng

End With

End Sub

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

Offset based on column heading

achidsey:

try,

Dim MyRange As Range
Dim c As Range
With Worksheets(1).Columns(1)
Set c = .Find("4J6N", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If MyRange Is Nothing Then
Set MyRange = c
Else
Set MyRange = Union(MyRange, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
MyRange.Offset(, 2).Select
End With

--
å¤©è¡Œå¥ï¼Œå›å*ä»¥è‡ªå¼·ä¸æ¯
åœ°å‹¢å¤ï¼Œå›å*ä»¥åŽšå¾·è¼‰ç‰©

http://www.vba.com.tw/plog/

"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

Offset based on column heading

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

Offset based on column heading

And a typo (sigh):

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 FoundCell3 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

Offset based on column heading

Dave and chijanzen,

Thanks for the assistance.

Alan

--
achidsey

"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