Setting active cell position using vba

[MonsterBait]

Hi All,


I'm missing something from the code below and its driving me bonkers. What I want is to set the active cell to equal my named range ("MyNumber"). What I'm getting is that it is locating 2 rows above what I want.

I want to be able to locate the data row which matches range "MyNumber" so I can edit that row.

Set CurCell = Sheets("sheet1").Range("$A:$A").Cells((Range("MyNumber").Value))
[TABLE="width: 188"]
<TBODY>[TR]
[TD="class: xl64, width: 248, bgcolor: transparent, colspan: 4"]
[TABLE="width: 188"]
<TBODY>[TR]
[TD="class: xl66, width: 248, bgcolor: transparent, colspan: 4"]Database
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Number
[/TD]
[TD="class: xl67, bgcolor: transparent"]Name
[/TD]
[TD="class: xl67, bgcolor: transparent"]Address
[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]AAA
[/TD]
[TD="class: xl67, bgcolor: transparent"]ZZZ
[/TD]
[TD="class: xl66, bgcolor: transparent"]987
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]BBB
[/TD]
[TD="class: xl67, bgcolor: transparent"]XXX
[/TD]
[TD="class: xl66, bgcolor: transparent"]654
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]3
[/TD]
[TD="class: xl67, bgcolor: transparent"]CCC
[/TD]
[TD="class: xl67, bgcolor: transparent"]WWW
[/TD]
[TD="class: xl66, bgcolor: transparent"]321
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]4
[/TD]
[TD="class: xl67, bgcolor: transparent"]DDD
[/TD]
[TD="class: xl67, bgcolor: transparent"]VVV
[/TD]
[TD="class: xl66, bgcolor: transparent"]159
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]5
[/TD]
[TD="class: xl67, bgcolor: transparent"]EEE
[/TD]
[TD="class: xl67, bgcolor: transparent"]QQQ
[/TD]
[TD="class: xl66, bgcolor: transparent"]753
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]6
[/TD]
[TD="class: xl67, bgcolor: transparent"]FFF
[/TD]
[TD="class: xl67, bgcolor: transparent"]UUU
[/TD]
[TD="class: xl66, bgcolor: transparent"]862
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]7
[/TD]
[TD="class: xl67, bgcolor: transparent"]GGG
[/TD]
[TD="class: xl67, bgcolor: transparent"]YYY
[/TD]
[TD="class: xl66, bgcolor: transparent"]248
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8
[/TD]
[TD="class: xl67, bgcolor: transparent"]HHH
[/TD]
[TD="class: xl67, bgcolor: transparent"]PPP
[/TD]
[TD="class: xl66, bgcolor: transparent"]971
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD="class: xl64, bgcolor: transparent"]
[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks,
Alex

 

[Michael M]

Hi Alex
What range does "MyNumber" reside in ?
and when you created my range was the start of that named range 2 cells above where you want to be ?

 

[MonsterBait]

Hi Michael,

The range "MyNumber" resides in a separate sheet (eg sheet2).
There are other fields which correspond to name, address and phone number as per my db example.
I have been able to effectively change the data but in the wrong row.

eg. If MyNumber = 4, and I want to change the name "DDD" to "XYZ", it would go to row 2 and change the name to "XYZ" instead of row 4.
Something in my code is directing it to 2 rows above but I can't identifier what.

Regards,
Alex

Hi Alex
What range does "MyNumber" reside in ?
and when you created my range was the start of that named range 2 cells above where you want to be ?

 

[Michael M]

Maybe you need to post the rest of the code