Using Named Ranges 3

[mveera]

Hi,

I have named a column as Test1 (=Sheet1!$F:$F) and a Row as Test2(=Sheet1!$4:$4). I would like know how i can set the value of the intersection of Test1 and Test2 using VBA.
I don't want to hardcode Cells(4,F).value = "Test". Instead can i use the name of the ranges to set the values. This even if i insert a new Column/Row it won't affect the VBA code.

Thanks
Veera

 

[xlbo]

This works:
Sub GetIntersection()
Set isect = Application.Intersect(Range("test1&quot, Range("test2&quot)
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
MsgBox isect.Address
End If
End Sub Rgds
~Geoff~

 

[RobBroekhuis]

How cool! Not something I've had a need for, so far, but it's good to know it exists.
Rob

 

[mveera]

Thanks for the answer. it was really useful.

Is this the only way we can set the values? The reason is, i have a lot of values to be set depending upon the modifications done by the user in a form. using this method i will have to call intersect for each value i set. i think it is cumbersome.

The requirement is something similar to this

I have three rows and two columns. Based on the user modifications, the values in the corresponding cells have to be set. As of now i am hardcoding the cells addresses. If i use the intersection i think i will have to call 3x2 times intersection to get address and then set values.

Anyway thanks for the answer.

Thanks
Veera

 

[RobBroekhuis]

You could also use the following range reference:

cells(range("test2&quot.row,range("test1&quot.column)

since in your case the named ranges are single rows and columns.
Rob

 

[mveera]

Hi,

I found how to set the value instead of using an Intersect. it is

Cells(Range("Test1&quot.Row, Range("Test2&quot.Column).Value = 65

Only thing it does is prevent the Intersection. Also i am not sure whether it is the right way to do it. But it works.

Thanks
Veera

 

[mveera]

Thanks Rob.

I didn't see u'r post when i posted same method.

Thanks
Veera