Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range on another sheet | Excel Worksheet Functions | |||
Refer to Named Range on another sheet for IF function | Excel Worksheet Functions | |||
plz help: creating named range in VBA, loop goes haywire | Excel Programming | |||
create named range in each sheet in workbook | Excel Programming | |||
Referencing Named Range in Other Sheet | Excel Programming |