Loop thru named range of second sheet

[Guest]

I have a workbook with 2 sheets in it

On sheet one, I have a layout of fields (range A1 though M20). Cell A1 has
a value in it, and all of the other cells obtain their values using a vlookup
into a named range on sheet two based upon the value in Cell A1 of sheet one.

I need to create a button that will ascertain a value from the user, using a
dropdown, then loop through the named range on sheet two, looking at the
values in column 1. Wherever it finds a match, it needs to pick off the
value of column 2 of the named range, place that value in cell A1 of sheet
one, thus causing all the other values to be filled in via their vlookup
formulas, and then print Range A1 through M20 of sheet one.

I am successfully doing everything except I cannot figure out how to
reference the cells in the named range of sheet two. I need to know what to
put within my double brackets below:

Dim i as integer
Dim strArgValue as string
strArgValue = Cells(23, 1) 'pick up the value from the dropdown
For i = 1 to [[the number of rows in the named range on sheet two]]
If [[col 1 of row i of the named range of sheet two]] = strArgValue then
Cells(1, 1) = [[the value in col 2 of row i of the named range of
sheet two]]
.....Do other processing, including printing the desired range
End If
i = i + 1
Next

 

[Guest]

Try SheetName.Range("i1").Value

 

[Bob Phillips]

Dim i as integer
Dim strArgValue as string
strArgValue = Cells(23, 1) 'pick up the value from the dropdown
For i = 1 to Worksheets("Sheet2").Range("myRange").Rows.Count
If Worksheets("Sheet2").Range("myRange").Cells(i,1) = strArgValue then
Cells(1, 1) = Worksheets("Sheet2").Range("myRange").Cell(i,2).Value
.....Do other processing, including printing the desired range
End If
i = i + 1
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Range("Dog")

will refer to the named range regardless of which sheet it is on if it is a
workbook level name and there are no sheet level ranges named dog
(Sheet1!Dog for example)

Dim rng as Range, i as Long

For i = 1 to Worksheets("Sheet2").Range("Dog").Rows.count
set rng = worksheets("Sheet2").Cells(i,1)
if rng.Value = strArgValue then
cells(1,1).Value = rng.offset(0,1).Value
end if
Next

You don't need i = i + 1 since the loop increments i