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

Refer row or column of a range name
 

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

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

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

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

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

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