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