PDA

View Full Version : 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.

heres the code




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"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
':thumb
'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