Out Of range Error with Find

[Shiseiji]

I've used similar code in the past, but can't figure out what I'm missing. Troubleshooting I've reduced the find down to a single string and still get "Out of Range." Help is appreciated!
Some of the comments are notes to myself as to why code may be different than code I've used in the past.

Sub MakeRanges_m()
   '
    Sheets("OHR Report").Activate
    '
    Dim LastCol             As Integer
    Dim LastRow             As Long
    Dim rng_HeaderRow       As Range
    Dim ThisWb              As Workbook
    Dim ThisWs              As Worksheet
    '
    Set ThisWb = ActiveWorkbook
    Set ThisWs = ActiveSheet
    '
    With ThisWb
    With ThisWs
    '
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
'    Range("A1", Cells(LastCol)).Name = "rng_HeaderRow" 'I don't understand why this doesn't work with a "DIM rng_HeaderRow As Long"  like "LastRow", but changed it to a named range
'    Range("A1", Cells(LastRow, LastCol)).Name = "rng_tbl"
'---Start create ranges---
    .Names.Add Name:="rng_HeaderRow", RefersTo:=Range("A1", Cells(LastCol))
'  Range("A1", Cells(LastCol)).Name = "rng_HeaderRow" wouldn't work, no idea why . . . 
'
    Set Rng = Range("rng_HeaderRow")
    With Rng
            .Find(What:="Primary TW Zip Code", LookAt:=xlAll, SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_p_zipcode"
    End With
   Cells(1, 1).Select
   Cells(1, 1).Activate
    End With 'ThisWs
    End With 'ThisWb
End Sub

TIA

Ron

 

[Bosquedeguate]

A copy of the range (it looks like a Header Row) would be helpful. Can you create a mini-sheet and share it?

 

[Joe4]

This does not appear to be a valid range reference:

RefersTo:=Range("A1", Cells(LastCol))

Try this:

RefersTo:=Range(Cells(1, "A"), Cells(LastRow, LastCol))

 

[Shiseiji]

This does not appear to be a valid range reference:

RefersTo:=Range("A1", Cells(LastCol))

Try this:

[QUOTE="Bosquedeguate, post: 6098760, member: 508385"]
A copy of the range (it looks like a Header Row) would be helpful.   Can you create a mini-sheet and share it?
[/QUOTE]

Thanks, will have to do it from home. Sharing sites are all blocked from work. "But" you prompted me to look deeper at the named range and it's not what I expected!
It's ='OHR Report'!$A$1:$S$13216
And it should be 'OHR Report'!$A$1:$S$1

So there is the "real" problem. Now to figure out how I got there . . .

 

[Shiseiji]

This does not appear to be a valid range reference:

RefersTo:=Range("A1", Cells(LastCol))

Try this:

RefersTo:=Range(Cells(1, "A"), Cells(LastRow, LastCol))

Same result. I may have looked at the wrong range name,

What I find crazy is only the zip code ranges are not created. I inserted the ".Range("A1").Activate" trying to be sure to set the arguments explicitly. Am I doing this correctly? I also tried a complete new With Range and get the same errors. it's after the first zip code is found that the find "Primary TW Zip Code" fails. Same if I rem it and try "Secondary TW Zip Code" with "LookAt:=xlAll"

With Range("rng_HeaderRow")
            .Find(What:="UserName", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_UserName"
            .Range("A1").Activate
            .Find(What:="Employee ID", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_EmployeeID"
            .Range("A1").Activate
            .Find(What:="Zip Code", LookAt:=xlAll, SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_zipcode"
            .Range("A1").Activate
            .Find(What:="Primary TW Zip Code", LookAt:=xlAll, SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_p_zipcode"
            .Range("A1").Activate
            .Find(What:="Secondary TW Zip Code", LookAt:=xlAll, SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_s_zipcode"
    End With 'Range("rng_HeaderRow")

 

[Joe4]

Sorry, if you just want the first row, try this instead:

RefersTo:=Range(Cells(1, "A"), Cells(1, LastCol))

 

[Akuini]

LookAt:=xlAll should be LookAt:=xlWhole
and you don't need
.Range("A1").Activate

 

[Shiseiji]

I apologize for my belated response. Thank all both very much.I
Bosquedeguate prompted me to look at the range that had an error.
Akuini spotted my bad enumeration and prompted me to re-look at the Find options.
Joe4 reminded me that I'd re-used less than best practice code. I thought I'd purged my LastRow of the old code. Bad "assumption."

All combined together fixed my code!

I had placed the

.Range("A1").Activate

in my code thinking maybe the search wasn't returning to the top left of the range. I found the answer that unless the start point is set, then the search starts at the top-left cell of the range.

Again, thank you all very much!

Ron

 

[MARK858]

then the search starts at the top-left cell of the range.

Just to be clear the Find starts after the top-left cell of the range. If you want the entire range searched then you start the Find after the last cell in the range.

With Range("rng_HeaderRow")
            .Find(What:="UserName", LookAt:=xlPart, After:=.Cells(.Cells.Count), SearchOrder:=xlByRows, SearchDirection:=xlNext).Name = "c_UserName"
'            Rest of code
End With

 

[Shiseiji]

the Find starts after the top-left cell of the range.

That's what I thought and then obviously misread the Microsoft VBA Library when I rechecked the option after reading Akuini's response. <slaps forehead>
"Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)"
And the previous paragraph where, as you point out, it states "the specified cell isn't searched until the method wraps back around to this cell." That's the reasoning I had for forcing the Find to re-start from "A1" when the best option would be to simply tell Find where to start using your example which I would not have thought of.

Thank you!