Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Harry Hunter   on Jan 14 In MS Office Category.

  
Question Answered By: Abasi Massri   on Jan 14

There's probably several ways of doing it, but here's one that's
fairly
simple:

I filled B2:C5 with 1,2,3,4,5,6
and E4:F6 with A,B,C,D,E,F

Then, I took advantage of the fact that a Range.Row returns the row
number of the first cell, and Range.Column returns the column number
of the first cell.
Then measured the distance  between the first cell  of each range  and
used the value as an "offset"

Function Distance(Rng1 As Range, Rng2 As Range)
Dim RowRng1, RowRng2, ColRng1, ColRng2
Dim OffsetRow, OffsetCol, Data, msg
RowRng1 = Rng1.Row
RowRng2 = Rng2.Row
ColRng1 = Rng1.Column
ColRng2 = Rng2.Column
OffsetRow = RowRng2 - RowRng1
OffsetCol = ColRng2 - ColRng1
msg = ""
For Each Data In Rng1
msg = msg & Data.Value _
& Cells(Data.Row + OffsetRow, Data.Column + OffsetCol) _
& Chr(13)
Next Data
MsgBox msg
End Function

I then called the function  with:

stat = Distance(Range("B3:C5"), Range("E4:F6"))

it produces a msgbox with:
1A
2B
3C
4D
5E
6F

You can change the math any way you want...


Another way would be to load two arrays with the contents of the
ranges, then compare array indexes..
(for a 30x30 array)
for X = 0 to 29
for Y = 0 to 29
msg = msg & Array1(X,Y) & ", " & Array2(X,Y)
Next Y
Next X

Share: 

 

This Question has 6 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Distance Function Or get search suggestion and latest updates.


Tagged: