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 |
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 |