"Find" Code

[Brian Matlack]

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

[Jim Jackson]

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

[Brian Matlack]

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

[Jim Jackson]

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