View Full Version : How do you make range A sheet 2 equal rangeB sheet 1??

08-18-2005, 03:28 PM
Hi All

bit of a mind block on this one, :dunno

trying to get the values in 2 ranges on 2 seperate worksheets to equal each other.

heres the code


Sub GetData(name As String, MyRng As Range)
Dim MyString, FirstAddr As String
Dim c, D, Rng, NameRng As Range

MyString = name
Set Rng = MyRng
'passed variables name(name to find) and MyRng(where the data should end up.)
'sheet(1) is called "Date", sheet(2) is called "Standard & Request"

Set NameRng = Sheets("Date").Range("A10:A30")

With NameRng

Set c = .Find(MyString, LookIn:=xlValues)

If Not c Is Nothing Then

FirstAddr = c.Address

'MsgBox FirstAddr

Set D = Range(c.Offset(0, 1), c.Offset(3, 7))
'need to get the values found in Range D to be entered into Range Rng.
'this runs through each cell finding the "name" but doesnt change the values
'in the range? :dunno
'how do i pass the range values back so "Rng = D"?

'MsgBox "2 activated"
Rng = D.Value

'Sheets("Standard & Request").Rng=D.Value

Set c = .FindNext(c)
Loop While c.Address <> FirstAddr

End If
End With

End Sub

obviously I am trying to get Rng in sheet 2 to equal the values in range D in sheet 1.

the code works fine in so far as it finds each name in sheet 2 finds it in sheet1 selects the offset range and goes back to sheet2 finds the corrersponding offset range but the values dont change>>>>>

my mind has gone blank

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
ok so 3 in morning not best time to make judgements
should have been Rng.value=D.value

oh well

c u