S
Sajit
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.Address
a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersToRange
'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.address
$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.
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.Address
a = ActiveWorkbook.ActiveSheet.Names("notes2").RefersToRange
'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.address
$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.