Re: Refer row or column of a range name

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
dguillett1@xxxxxxxxxxxxx
"Sajit" <Sajit@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:46BB8CFE-C548-4369-BDEE-A6B945F32484@xxxxxxxxxxxxxxxx
I have this length of code to read 2 related lists and place the result on a
third *** 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
*** 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.Active***.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.
--
Sajit
Abu Dhabi

.

Quantcast