Loop thru named range of second sheet
 

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

Loop thru named range of second sheet
 

Try SheetName.Range("i1").Value

"Bill Sturdevant" wrote:

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

Loop thru named range of second sheet
 

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).Va lue
.....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)


"Bill Sturdevant" wrote in
message ...
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

Loop thru named range of second sheet
 

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

--
Regards,
Tom Ogilvy


"Bill Sturdevant" wrote in
message ...
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