"Find" Code
 

Hi!
I need to find the name that is in range ("name1") in the range
("dataset1") (column A) and then move three columns to the right
(column D) and paste-special-values.

What would the code look like for this?

Thanks for your time and any help or direction!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=562605

"Find" Code
 

This might be workable for you after making necessary modifications to fit
your needs.


Sub Replacethem()
Dim sAddr As String
Dim rngA As Range, rngB As Range
Dim rng As Range, cell As Range
Dim res As Variant

With Worksheets("Sheet1")

Set rngB = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
Set rngC = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))

For Each cell In rngB
Set rng = rngC.Find(cell.Value, _
After:=rngC(rngC.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
sAddr = rng.Address

Do

rng.Offset(0, 1) = cell.Offset(0, -1).Value
Set rng = rngC.FindNext(rng)
Loop While rng.Address < sAddr

End If
Next
End Sub

--
Best wishes,

Jim


"Brian Matlack" wrote:


Hi!
I need to find the name that is in range ("name1") in the range
("dataset1") (column A) and then move three columns to the right
(column D) and paste-special-values.

What would the code look like for this?

Thanks for your time and any help or direction!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=562605

"Find" Code
 

Jim:
Thanks for your response! I'm pretty green about this VBA stuff bu
I'll try to tweek it to fit my job. I couldn't tell where the re
Variant was used in your code. I believe it would be what I was lookin
for (range name1) in the find part of the code. If you have time t
respond I could use the help, if not I do appreciate the input and I'l
try to make it work.

Thanks Again!

--
Brian Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=56260

"Find" Code
 

Brian,

I should have taken that out. This was from a larger macro. It has no
bearing on this routine. Sorry about the confusion.

Also, there is another change needed. Change "Dim rngA, rngB As Range" to
"Dim rngB, rngC As Range". rngB will be the text you are looking for in
rngC.

Is everything on the same sheet or separate sheets/Workbooks? That will
make a difference in how it needs to be modified.

--
Best wishes,

Jim


"Brian Matlack" wrote:


Jim:
Thanks for your response! I'm pretty green about this VBA stuff but
I'll try to tweek it to fit my job. I couldn't tell where the res
Variant was used in your code. I believe it would be what I was looking
for (range name1) in the find part of the code. If you have time to
respond I could use the help, if not I do appreciate the input and I'll
try to make it work.

Thanks Again!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=562605