PDA

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



sirdragon
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.


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"
Worksheets(1).Activate
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))
'D.Select
Do
'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"?
Worksheets(2).Activate
'Rng.Select
'MsgBox "2 activated"
'Rng.value=D.value
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
:doh:
help..

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

oh well

c u