Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" function question - No code required | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming | |||
VBA code to display the "Find" (Ctrl+F) dialog box | Excel Programming |