| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer row or column of a range name
I have this length of code to read 2 related lists and place the result on a
third sheet starting at a cell named, notes2. The lists are with columns for, 1. page_no and note_num 2. note_num and note Each of the page_no may have more than one note to it. My method is to read through the list1, until the page_no changes. Pick the notes corresponding to the note_num from list2. Place the note on a third sheet starting at name notes2. Sub place_note() Windows("Data for butterfly valves.xls").Activate Set myrange2 = Worksheets("Data1").Range("A1:CZ1000") Set myrange1 = Worksheets("Data1").Range("A1:CZ1") Set myrange3 = Worksheets("Note_tbl").Range("A1:B1000") Set myrange4 = Worksheets("Notes").Range("A1:B1000") Set myrange5 = Worksheets("Notes").Range("A2:A1000") colm1 = 1 row1 = 2 c1 = Trim(myrange3.Cells(2, 1)) b1 = Trim(myrange3.Cells(2, 1)) Windows("But_test1.xls").Activate c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersT oRange 'c3.Select 'Debug.Print c2.Row 'Debug.Print c2.Column note_row = 0 If b1 < "" Then While Trim(myrange3.Cells(2, colm1)) = b1 nt1 = Trim(myrange3.Cells(row1, 2)) With myrange5 Set nt2 = .Find(nt1, LookIn:=xlValues) Windows("But_test1.xls").Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate note1 = Trim(ActiveCell.Value) 'Debug.Print nt2.Rows 'nt2.Offset 'note1 = mrange5.Cells(nt2.Rows, 2) ActiveCell.Value = note1 End With Worksheets("But").Range(c2) = note1 Worksheets("But").Range(a1) = text2 Windows("But_test1.xls").Activate c1 = ActiveWorkbook.Names("notes").RefersTo row1 = row1 + 1 Wend End If 'notes = End Sub The immediate window gives the following result, ?ActiveWorkbook.Names("notes2").RefersToRange.addr ess $A$35 1. How do I select the cell corresponding to the address, the cells function needs the row and column as parameters. 2. How can the row numbers in the address, in the form $A$35, be incremented to move to the next row for the next note. -- Sajit Abu Dhabi |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refer row or column of a range name
Don,
The range name notes2 is inserted manually in a template file. The note that the other routine will bring up has to be placed at the location starting this name. There will several of these sheets to be created by placing these notes. A close thing to this would be mail merge in word. I was actually doing this using Access reports. When the users decided that they would like to have a file which they could eventually edit manually. Therefore the Excel. -- Sajit Abu Dhabi "Don Guillett" wrote: A cursory look suggests that you are doing a lot of unnecessary work here. Post your layout along with what you are looking for. Naming a range each time and selecting(activating) are not necessary. If you are looking for the same thing multiple times use FINDNEXT instead. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... I have this length of code to read 2 related lists and place the result on a third sheet starting at a cell named, notes2. The lists are with columns for, 1. page_no and note_num 2. note_num and note Each of the page_no may have more than one note to it. My method is to read through the list1, until the page_no changes. Pick the notes corresponding to the note_num from list2. Place the note on a third sheet starting at name notes2. Sub place_note() Windows("Data for butterfly valves.xls").Activate Set myrange2 = Worksheets("Data1").Range("A1:CZ1000") Set myrange1 = Worksheets("Data1").Range("A1:CZ1") Set myrange3 = Worksheets("Note_tbl").Range("A1:B1000") Set myrange4 = Worksheets("Notes").Range("A1:B1000") Set myrange5 = Worksheets("Notes").Range("A2:A1000") colm1 = 1 row1 = 2 c1 = Trim(myrange3.Cells(2, 1)) b1 = Trim(myrange3.Cells(2, 1)) Windows("But_test1.xls").Activate c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersT oRange 'c3.Select 'Debug.Print c2.Row 'Debug.Print c2.Column note_row = 0 If b1 < "" Then While Trim(myrange3.Cells(2, colm1)) = b1 nt1 = Trim(myrange3.Cells(row1, 2)) With myrange5 Set nt2 = .Find(nt1, LookIn:=xlValues) Windows("But_test1.xls").Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate note1 = Trim(ActiveCell.Value) 'Debug.Print nt2.Rows 'nt2.Offset 'note1 = mrange5.Cells(nt2.Rows, 2) ActiveCell.Value = note1 End With Worksheets("But").Range(c2) = note1 Worksheets("But").Range(a1) = text2 Windows("But_test1.xls").Activate c1 = ActiveWorkbook.Names("notes").RefersTo row1 = row1 + 1 Wend End If 'notes = End Sub The immediate window gives the following result, ?ActiveWorkbook.Names("notes2").RefersToRange.addr ess $A$35 1. How do I select the cell corresponding to the address, the cells function needs the row and column as parameters. 2. How can the row numbers in the address, in the form $A$35, be incremented to move to the next row for the next note. -- Sajit Abu Dhabi |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refer row or column of a range name
I, for one, still cannot tell what you are trying to do. If you like, send
me a file to the address below along with a full and detailed explanation along with before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... Don, The range name notes2 is inserted manually in a template file. The note that the other routine will bring up has to be placed at the location starting this name. There will several of these sheets to be created by placing these notes. A close thing to this would be mail merge in word. I was actually doing this using Access reports. When the users decided that they would like to have a file which they could eventually edit manually. Therefore the Excel. -- Sajit Abu Dhabi "Don Guillett" wrote: A cursory look suggests that you are doing a lot of unnecessary work here. Post your layout along with what you are looking for. Naming a range each time and selecting(activating) are not necessary. If you are looking for the same thing multiple times use FINDNEXT instead. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... I have this length of code to read 2 related lists and place the result on a third sheet starting at a cell named, notes2. The lists are with columns for, 1. page_no and note_num 2. note_num and note Each of the page_no may have more than one note to it. My method is to read through the list1, until the page_no changes. Pick the notes corresponding to the note_num from list2. Place the note on a third sheet starting at name notes2. Sub place_note() Windows("Data for butterfly valves.xls").Activate Set myrange2 = Worksheets("Data1").Range("A1:CZ1000") Set myrange1 = Worksheets("Data1").Range("A1:CZ1") Set myrange3 = Worksheets("Note_tbl").Range("A1:B1000") Set myrange4 = Worksheets("Notes").Range("A1:B1000") Set myrange5 = Worksheets("Notes").Range("A2:A1000") colm1 = 1 row1 = 2 c1 = Trim(myrange3.Cells(2, 1)) b1 = Trim(myrange3.Cells(2, 1)) Windows("But_test1.xls").Activate c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersT oRange 'c3.Select 'Debug.Print c2.Row 'Debug.Print c2.Column note_row = 0 If b1 < "" Then While Trim(myrange3.Cells(2, colm1)) = b1 nt1 = Trim(myrange3.Cells(row1, 2)) With myrange5 Set nt2 = .Find(nt1, LookIn:=xlValues) Windows("But_test1.xls").Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate note1 = Trim(ActiveCell.Value) 'Debug.Print nt2.Rows 'nt2.Offset 'note1 = mrange5.Cells(nt2.Rows, 2) ActiveCell.Value = note1 End With Worksheets("But").Range(c2) = note1 Worksheets("But").Range(a1) = text2 Windows("But_test1.xls").Activate c1 = ActiveWorkbook.Names("notes").RefersTo row1 = row1 + 1 Wend End If 'notes = End Sub The immediate window gives the following result, ?ActiveWorkbook.Names("notes2").RefersToRange.addr ess $A$35 1. How do I select the cell corresponding to the address, the cells function needs the row and column as parameters. 2. How can the row numbers in the address, in the form $A$35, be incremented to move to the next row for the next note. -- Sajit Abu Dhabi |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refer row or column of a range name
Perhaps, you can just help me on the bit I am stuck up with.
The statement below c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss gives the result as $A$35 How can I get the row and column corresponding to this? -- Sajit Abu Dhabi "Don Guillett" wrote: I, for one, still cannot tell what you are trying to do. If you like, send me a file to the address below along with a full and detailed explanation along with before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... Don, The range name notes2 is inserted manually in a template file. The note that the other routine will bring up has to be placed at the location starting this name. There will several of these sheets to be created by placing these notes. A close thing to this would be mail merge in word. I was actually doing this using Access reports. When the users decided that they would like to have a file which they could eventually edit manually. Therefore the Excel. -- Sajit Abu Dhabi "Don Guillett" wrote: A cursory look suggests that you are doing a lot of unnecessary work here. Post your layout along with what you are looking for. Naming a range each time and selecting(activating) are not necessary. If you are looking for the same thing multiple times use FINDNEXT instead. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... I have this length of code to read 2 related lists and place the result on a third sheet starting at a cell named, notes2. The lists are with columns for, 1. page_no and note_num 2. note_num and note Each of the page_no may have more than one note to it. My method is to read through the list1, until the page_no changes. Pick the notes corresponding to the note_num from list2. Place the note on a third sheet starting at name notes2. Sub place_note() Windows("Data for butterfly valves.xls").Activate Set myrange2 = Worksheets("Data1").Range("A1:CZ1000") Set myrange1 = Worksheets("Data1").Range("A1:CZ1") Set myrange3 = Worksheets("Note_tbl").Range("A1:B1000") Set myrange4 = Worksheets("Notes").Range("A1:B1000") Set myrange5 = Worksheets("Notes").Range("A2:A1000") colm1 = 1 row1 = 2 c1 = Trim(myrange3.Cells(2, 1)) b1 = Trim(myrange3.Cells(2, 1)) Windows("But_test1.xls").Activate c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersT oRange 'c3.Select 'Debug.Print c2.Row 'Debug.Print c2.Column note_row = 0 If b1 < "" Then While Trim(myrange3.Cells(2, colm1)) = b1 nt1 = Trim(myrange3.Cells(row1, 2)) With myrange5 Set nt2 = .Find(nt1, LookIn:=xlValues) Windows("But_test1.xls").Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate note1 = Trim(ActiveCell.Value) 'Debug.Print nt2.Rows 'nt2.Offset 'note1 = mrange5.Cells(nt2.Rows, 2) ActiveCell.Value = note1 End With Worksheets("But").Range(c2) = note1 Worksheets("But").Range(a1) = text2 Windows("But_test1.xls").Activate c1 = ActiveWorkbook.Names("notes").RefersTo row1 = row1 + 1 Wend End If 'notes = End Sub The immediate window gives the following result, ?ActiveWorkbook.Names("notes2").RefersToRange.addr ess $A$35 1. How do I select the cell corresponding to the address, the cells function needs the row and column as parameters. 2. How can the row numbers in the address, in the form $A$35, be incremented to move to the next row for the next note. -- Sajit Abu Dhabi |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refer row or column of a range name
MsgBox Range("notes2").Row 'Address -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... Perhaps, you can just help me on the bit I am stuck up with. The statement below c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss gives the result as $A$35 How can I get the row and column corresponding to this? -- Sajit Abu Dhabi "Don Guillett" wrote: I, for one, still cannot tell what you are trying to do. If you like, send me a file to the address below along with a full and detailed explanation along with before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... Don, The range name notes2 is inserted manually in a template file. The note that the other routine will bring up has to be placed at the location starting this name. There will several of these sheets to be created by placing these notes. A close thing to this would be mail merge in word. I was actually doing this using Access reports. When the users decided that they would like to have a file which they could eventually edit manually. Therefore the Excel. -- Sajit Abu Dhabi "Don Guillett" wrote: A cursory look suggests that you are doing a lot of unnecessary work here. Post your layout along with what you are looking for. Naming a range each time and selecting(activating) are not necessary. If you are looking for the same thing multiple times use FINDNEXT instead. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... I have this length of code to read 2 related lists and place the result on a third sheet starting at a cell named, notes2. The lists are with columns for, 1. page_no and note_num 2. note_num and note Each of the page_no may have more than one note to it. My method is to read through the list1, until the page_no changes. Pick the notes corresponding to the note_num from list2. Place the note on a third sheet starting at name notes2. Sub place_note() Windows("Data for butterfly valves.xls").Activate Set myrange2 = Worksheets("Data1").Range("A1:CZ1000") Set myrange1 = Worksheets("Data1").Range("A1:CZ1") Set myrange3 = Worksheets("Note_tbl").Range("A1:B1000") Set myrange4 = Worksheets("Notes").Range("A1:B1000") Set myrange5 = Worksheets("Notes").Range("A2:A1000") colm1 = 1 row1 = 2 c1 = Trim(myrange3.Cells(2, 1)) b1 = Trim(myrange3.Cells(2, 1)) Windows("But_test1.xls").Activate c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersT oRange 'c3.Select 'Debug.Print c2.Row 'Debug.Print c2.Column note_row = 0 If b1 < "" Then While Trim(myrange3.Cells(2, colm1)) = b1 nt1 = Trim(myrange3.Cells(row1, 2)) With myrange5 Set nt2 = .Find(nt1, LookIn:=xlValues) Windows("But_test1.xls").Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate note1 = Trim(ActiveCell.Value) 'Debug.Print nt2.Rows 'nt2.Offset 'note1 = mrange5.Cells(nt2.Rows, 2) ActiveCell.Value = note1 End With Worksheets("But").Range(c2) = note1 Worksheets("But").Range(a1) = text2 Windows("But_test1.xls").Activate c1 = ActiveWorkbook.Names("notes").RefersTo row1 = row1 + 1 Wend End If 'notes = End Sub The immediate window gives the following result, ?ActiveWorkbook.Names("notes2").RefersToRange.addr ess $A$35 1. How do I select the cell corresponding to the address, the cells function needs the row and column as parameters. 2. How can the row numbers in the address, in the form $A$35, be incremented to move to the next row for the next note. -- Sajit Abu Dhabi |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refer row or column of a range name
That did it.
Thanks Don. I realised it was pointless trying to tell all that I was doing or trying to do. Mind reading being too expensive these days. However, I also wish to know, if there is a way of extracting the location of a range in rows and columns, not the long way of having to read the string and deciphering it into rows and columns. -- Sajit Abu Dhabi "Don Guillett" wrote: MsgBox Range("notes2").Row 'Address -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... Perhaps, you can just help me on the bit I am stuck up with. The statement below c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss gives the result as $A$35 How can I get the row and column corresponding to this? -- Sajit Abu Dhabi "Don Guillett" wrote: I, for one, still cannot tell what you are trying to do. If you like, send me a file to the address below along with a full and detailed explanation along with before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... Don, The range name notes2 is inserted manually in a template file. The note that the other routine will bring up has to be placed at the location starting this name. There will several of these sheets to be created by placing these notes. A close thing to this would be mail merge in word. I was actually doing this using Access reports. When the users decided that they would like to have a file which they could eventually edit manually. Therefore the Excel. -- Sajit Abu Dhabi "Don Guillett" wrote: A cursory look suggests that you are doing a lot of unnecessary work here. Post your layout along with what you are looking for. Naming a range each time and selecting(activating) are not necessary. If you are looking for the same thing multiple times use FINDNEXT instead. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sajit" wrote in message ... I have this length of code to read 2 related lists and place the result on a third sheet starting at a cell named, notes2. The lists are with columns for, 1. page_no and note_num 2. note_num and note Each of the page_no may have more than one note to it. My method is to read through the list1, until the page_no changes. Pick the notes corresponding to the note_num from list2. Place the note on a third sheet starting at name notes2. Sub place_note() Windows("Data for butterfly valves.xls").Activate Set myrange2 = Worksheets("Data1").Range("A1:CZ1000") Set myrange1 = Worksheets("Data1").Range("A1:CZ1") Set myrange3 = Worksheets("Note_tbl").Range("A1:B1000") Set myrange4 = Worksheets("Notes").Range("A1:B1000") Set myrange5 = Worksheets("Notes").Range("A2:A1000") colm1 = 1 row1 = 2 c1 = Trim(myrange3.Cells(2, 1)) b1 = Trim(myrange3.Cells(2, 1)) Windows("But_test1.xls").Activate c2 = ActiveWorkbook.Names("notes2").RefersToRange.Addre ss a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersT oRange 'c3.Select 'Debug.Print c2.Row 'Debug.Print c2.Column note_row = 0 If b1 < "" Then While Trim(myrange3.Cells(2, colm1)) = b1 nt1 = Trim(myrange3.Cells(row1, 2)) With myrange5 Set nt2 = .Find(nt1, LookIn:=xlValues) Windows("But_test1.xls").Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate note1 = Trim(ActiveCell.Value) 'Debug.Print nt2.Rows 'nt2.Offset 'note1 = mrange5.Cells(nt2.Rows, 2) ActiveCell.Value = note1 End With Worksheets("But").Range(c2) = note1 Worksheets("But").Range(a1) = text2 Windows("But_test1.xls").Activate c1 = ActiveWorkbook.Names("notes").RefersTo row1 = row1 + 1 Wend End If 'notes = End Sub The immediate window gives the following result, ?ActiveWorkbook.Names("notes2").RefersToRange.addr ess $A$35 1. How do I select the cell corresponding to the address, the cells function needs the row and column as parameters. 2. How can the row numbers in the address, in the form $A$35, be incremented to move to the next row for the next note. -- Sajit Abu Dhabi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to refer to a range except one cell | Excel Discussion (Misc queries) | |||
| refer to range in another sheet | Excel Discussion (Misc queries) | |||
| Refer to column in range statement | Excel Programming | |||
| Refer to rows in a range | Excel Programming | |||
| How do I refer a Range to a Cell | Excel Worksheet Functions | |||
Linear Mode
