Find Cell in Named Range

[David]

I have one column of numbers, in three sections (B2:B30), B34:B44), (B48:B55)
that can have duplicate numbers.
I was using successfully code to find a number in column B and if found,
activate the hyperlink in Column A in the same row as the found number.
But, when I started getting duplicate numbers, my code bombed.
I know I need to create a RANGE for each section, but how do I need to
change my code to look at a particular section? I have one set of code for
each section. Here is the code I am working with for the second section - I
want it to only look at the RANGE for A34:A44, let's call the Range OHAC:

With wbkData.ActiveSheet
iOffice = .Range("J30")
End With

With wbkSum.Sheets(1)
Dim lastrow As Long, lastcol As Long, xV As Long, xR As Long, xC As Long
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
'get matching row
xC = 2
For xV = 1 To lastrow
If iOffice = .Cells(xV, 2) Then xR = xV
Next xV
If xR = 0 Then MsgBox "Franchise #: " & iOffice & " not found in Summary
Table"
End With
With wbkSum.Sheets(1)
If xR > 0 Then Range("A" & xR).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.CommandBars("Task Pane").Visible = False
End With

Thanks much!

 

[Barb Reinhardt]

dim OHAC as range
dim r as range

Set OHAC = Range("A34:A44")
for each r in OHAC
if r.value = ioffice then
debug.print r.address, r.row, r.column
'I'm not sure what you want to do.
end if
next r

 

[David]

The OHAC range will change as items are added. I have set up a Named Range
using offset and counta starting at A:34 and counting A:34:A44. How do I just
refer to the Named Range OHAC without defining it in the code as Set OHAC....?
Thanks!

 

[David]

I'm looking for the physical row number.

dim OHAC as range
dim r as range

Set OHAC = Range("A34:A44")
for each r in OHAC
if r.value = ioffice then
debug.print r.address, r.row, r.column
'I'm not sure what you want to do.
end if
next r