Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
Kevin,
Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
What exactly are you trying to do?
The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
Do you mean that you have actually "named" the cell "A1" to
"switch1port1" or is the value of cell "A1" = "switch1port1"? I think I know what your saying... Could you please give an example of what your sheet looks like? You could email me your spreadsheet, but if you do, post here just to let me know you emailed it. Cheers, Jason Lepack Kevin wrote: There hundreds of names on each sheet. Basically each name represents a cell for each port on a switch. I want to go to have a summary page that will go through each name and add the text from that cell to the next line on the summary page. ex, if sheet1 range(a1) has the name switch1port1 and switch1port2, I want to loop through the names and add the text from each of those cells to a page called summary. if the text in those 2 cells is Host1 and Host2 then I want Host1 and Host2 added to the summary page. I cant just go down the list from each page because the cells on each switch page are all over the place as I laid it out to look physically like the switch. Hope that makes sense.... "Jason Lepack" wrote: What exactly are you trying to do? The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
Sorry for the confusion. When I get home tonight I'll look at your
situation in a little more detail. However, for now, your names are not specific to your worksheet, they are specific to your workBOOK. So, go from there! Sub Macro1() Dim wb As Workbook Dim n As Name Set wb = ActiveWorkbook For Each n In wb.Names Debug.Print n.Name Next n End Sub Cheers, Jason Lepack Kevin wrote: Yes, the name of cell a1 would be switch1port1 and the text inside the cell would say host1 I will email you a page.. "Jason Lepack" wrote: Do you mean that you have actually "named" the cell "A1" to "switch1port1" or is the value of cell "A1" = "switch1port1"? I think I know what your saying... Could you please give an example of what your sheet looks like? You could email me your spreadsheet, but if you do, post here just to let me know you emailed it. Cheers, Jason Lepack Kevin wrote: There hundreds of names on each sheet. Basically each name represents a cell for each port on a switch. I want to go to have a summary page that will go through each name and add the text from that cell to the next line on the summary page. ex, if sheet1 range(a1) has the name switch1port1 and switch1port2, I want to loop through the names and add the text from each of those cells to a page called summary. if the text in those 2 cells is Host1 and Host2 then I want Host1 and Host2 added to the summary page. I cant just go down the list from each page because the cells on each switch page are all over the place as I laid it out to look physically like the switch. Hope that makes sense.... "Jason Lepack" wrote: What exactly are you trying to do? The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
Kevin,
Is this kind of what you're looking for? If not, you can modify it a bit. If something bugs you reply back here. Cheers, Jason Lepack Sub SwitchPortSummary() Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim n As Name Set wb = ActiveWorkbook Set ws = wb.Sheets("Summary") Set r = ws.Range("A2") For Each n In wb.Names r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7) r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8, n.Name, "port") - 3) r.Offset(0, 2).Value = Range(n).Value r.Offset(0, 3).Value = n.Name Set r = r.Offset(1, 0) Next n Set r = ws.Cells r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Set r = Nothing Set ws = Nothing Set wb = Nothing End Sub Jason Lepack wrote: Sorry for the confusion. When I get home tonight I'll look at your situation in a little more detail. However, for now, your names are not specific to your worksheet, they are specific to your workBOOK. So, go from there! Sub Macro1() Dim wb As Workbook Dim n As Name Set wb = ActiveWorkbook For Each n In wb.Names Debug.Print n.Name Next n End Sub Cheers, Jason Lepack Kevin wrote: Yes, the name of cell a1 would be switch1port1 and the text inside the cell would say host1 I will email you a page.. "Jason Lepack" wrote: Do you mean that you have actually "named" the cell "A1" to "switch1port1" or is the value of cell "A1" = "switch1port1"? I think I know what your saying... Could you please give an example of what your sheet looks like? You could email me your spreadsheet, but if you do, post here just to let me know you emailed it. Cheers, Jason Lepack Kevin wrote: There hundreds of names on each sheet. Basically each name represents a cell for each port on a switch. I want to go to have a summary page that will go through each name and add the text from that cell to the next line on the summary page. ex, if sheet1 range(a1) has the name switch1port1 and switch1port2, I want to loop through the names and add the text from each of those cells to a page called summary. if the text in those 2 cells is Host1 and Host2 then I want Host1 and Host2 added to the summary page. I cant just go down the list from each page because the cells on each switch page are all over the place as I laid it out to look physically like the switch. Hope that makes sense.... "Jason Lepack" wrote: What exactly are you trying to do? The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
This is VERY close to what I'm looking for. Thank you so much for the help.
Loops are always my weak spot, but I'm learning.. I'll see what I can do with it!! Thanks, Kevin "Jason Lepack" wrote: Kevin, Is this kind of what you're looking for? If not, you can modify it a bit. If something bugs you reply back here. Cheers, Jason Lepack Sub SwitchPortSummary() Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim n As Name Set wb = ActiveWorkbook Set ws = wb.Sheets("Summary") Set r = ws.Range("A2") For Each n In wb.Names r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7) r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8, n.Name, "port") - 3) r.Offset(0, 2).Value = Range(n).Value r.Offset(0, 3).Value = n.Name Set r = r.Offset(1, 0) Next n Set r = ws.Cells r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Set r = Nothing Set ws = Nothing Set wb = Nothing End Sub Jason Lepack wrote: Sorry for the confusion. When I get home tonight I'll look at your situation in a little more detail. However, for now, your names are not specific to your worksheet, they are specific to your workBOOK. So, go from there! Sub Macro1() Dim wb As Workbook Dim n As Name Set wb = ActiveWorkbook For Each n In wb.Names Debug.Print n.Name Next n End Sub Cheers, Jason Lepack Kevin wrote: Yes, the name of cell a1 would be switch1port1 and the text inside the cell would say host1 I will email you a page.. "Jason Lepack" wrote: Do you mean that you have actually "named" the cell "A1" to "switch1port1" or is the value of cell "A1" = "switch1port1"? I think I know what your saying... Could you please give an example of what your sheet looks like? You could email me your spreadsheet, but if you do, post here just to let me know you emailed it. Cheers, Jason Lepack Kevin wrote: There hundreds of names on each sheet. Basically each name represents a cell for each port on a switch. I want to go to have a summary page that will go through each name and add the text from that cell to the next line on the summary page. ex, if sheet1 range(a1) has the name switch1port1 and switch1port2, I want to loop through the names and add the text from each of those cells to a page called summary. if the text in those 2 cells is Host1 and Host2 then I want Host1 and Host2 added to the summary page. I cant just go down the list from each page because the cells on each switch page are all over the place as I laid it out to look physically like the switch. Hope that makes sense.... "Jason Lepack" wrote: What exactly are you trying to do? The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
Try something like
Dim Nam As Name On Error Resume Next For Each Nam In ActiveWorkbook.Names If Not Nam.RefersToRange Is Nothing Then MsgBox Nam.RefersToRange.Worksheet.Name End If Next Nam -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Kevin" wrote in message ... one more thing I need to do. I need a way to get the worksheet name that a named cell resides. I tried this... Dim Nam For Each Nam In ActiveWorkbook.Names MsgBox Nam.Worksheet.Name Next Nam "Jason Lepack" wrote: Kevin, Is this kind of what you're looking for? If not, you can modify it a bit. If something bugs you reply back here. Cheers, Jason Lepack Sub SwitchPortSummary() Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim n As Name Set wb = ActiveWorkbook Set ws = wb.Sheets("Summary") Set r = ws.Range("A2") For Each n In wb.Names r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7) r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8, n.Name, "port") - 3) r.Offset(0, 2).Value = Range(n).Value r.Offset(0, 3).Value = n.Name Set r = r.Offset(1, 0) Next n Set r = ws.Cells r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Set r = Nothing Set ws = Nothing Set wb = Nothing End Sub Jason Lepack wrote: Sorry for the confusion. When I get home tonight I'll look at your situation in a little more detail. However, for now, your names are not specific to your worksheet, they are specific to your workBOOK. So, go from there! Sub Macro1() Dim wb As Workbook Dim n As Name Set wb = ActiveWorkbook For Each n In wb.Names Debug.Print n.Name Next n End Sub Cheers, Jason Lepack Kevin wrote: Yes, the name of cell a1 would be switch1port1 and the text inside the cell would say host1 I will email you a page.. "Jason Lepack" wrote: Do you mean that you have actually "named" the cell "A1" to "switch1port1" or is the value of cell "A1" = "switch1port1"? I think I know what your saying... Could you please give an example of what your sheet looks like? You could email me your spreadsheet, but if you do, post here just to let me know you emailed it. Cheers, Jason Lepack Kevin wrote: There hundreds of names on each sheet. Basically each name represents a cell for each port on a switch. I want to go to have a summary page that will go through each name and add the text from that cell to the next line on the summary page. ex, if sheet1 range(a1) has the name switch1port1 and switch1port2, I want to loop through the names and add the text from each of those cells to a page called summary. if the text in those 2 cells is Host1 and Host2 then I want Host1 and Host2 added to the summary page. I cant just go down the list from each page because the cells on each switch page are all over the place as I laid it out to look physically like the switch. Hope that makes sense.... "Jason Lepack" wrote: What exactly are you trying to do? The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next loop
Try this, Column E is the worksheet name.
Sub SwitchPortSummary() Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim n As Name Set wb = ActiveWorkbook Set ws = wb.Sheets("Summary") Set r = ws.Range("A2") For Each n In wb.Names r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7) r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8, n.Name, "port") - 3) r.Offset(0, 2).Value = Range(n).Value r.Offset(0, 3).Value = n.Name r.Offset(0, 4).Value = Mid(n, 2, InStr(2, n, "!") - 2) ' puts the worksheet name in col E Set r = r.Offset(1, 0) Next n Set r = ws.Cells r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Set r = Nothing Set ws = Nothing Set wb = Nothing End Sub Chip Pearson wrote: Try something like Dim Nam As Name On Error Resume Next For Each Nam In ActiveWorkbook.Names If Not Nam.RefersToRange Is Nothing Then MsgBox Nam.RefersToRange.Worksheet.Name End If Next Nam -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Kevin" wrote in message ... one more thing I need to do. I need a way to get the worksheet name that a named cell resides. I tried this... Dim Nam For Each Nam In ActiveWorkbook.Names MsgBox Nam.Worksheet.Name Next Nam "Jason Lepack" wrote: Kevin, Is this kind of what you're looking for? If not, you can modify it a bit. If something bugs you reply back here. Cheers, Jason Lepack Sub SwitchPortSummary() Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim n As Name Set wb = ActiveWorkbook Set ws = wb.Sheets("Summary") Set r = ws.Range("A2") For Each n In wb.Names r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7) r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8, n.Name, "port") - 3) r.Offset(0, 2).Value = Range(n).Value r.Offset(0, 3).Value = n.Name Set r = r.Offset(1, 0) Next n Set r = ws.Cells r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Set r = Nothing Set ws = Nothing Set wb = Nothing End Sub Jason Lepack wrote: Sorry for the confusion. When I get home tonight I'll look at your situation in a little more detail. However, for now, your names are not specific to your worksheet, they are specific to your workBOOK. So, go from there! Sub Macro1() Dim wb As Workbook Dim n As Name Set wb = ActiveWorkbook For Each n In wb.Names Debug.Print n.Name Next n End Sub Cheers, Jason Lepack Kevin wrote: Yes, the name of cell a1 would be switch1port1 and the text inside the cell would say host1 I will email you a page.. "Jason Lepack" wrote: Do you mean that you have actually "named" the cell "A1" to "switch1port1" or is the value of cell "A1" = "switch1port1"? I think I know what your saying... Could you please give an example of what your sheet looks like? You could email me your spreadsheet, but if you do, post here just to let me know you emailed it. Cheers, Jason Lepack Kevin wrote: There hundreds of names on each sheet. Basically each name represents a cell for each port on a switch. I want to go to have a summary page that will go through each name and add the text from that cell to the next line on the summary page. ex, if sheet1 range(a1) has the name switch1port1 and switch1port2, I want to loop through the names and add the text from each of those cells to a page called summary. if the text in those 2 cells is Host1 and Host2 then I want Host1 and Host2 added to the summary page. I cant just go down the list from each page because the cells on each switch page are all over the place as I laid it out to look physically like the switch. Hope that makes sense.... "Jason Lepack" wrote: What exactly are you trying to do? The reason it doesn't go into the loop is because there are no names in that worksheet. If you let the group know why you want to do that loop, then the group can tell you how to do it. Cheers, Jason Lepack Doug Glancy wrote: Kevin, Dim Nam as Name For each Nam in Activesheet.Names 'yak yak yak Next Nam hth, Doug "Kevin" wrote in message ... I want to perform certain tasks for each name in the activeworksheet. I'm not sure how to write the loop. For Each Name in Activesheet blah blah Next This doesnt work, what would be the correct syntax? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |