Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate all occurrances of a value then transfer to 2nd table on another sheet
To All,
I have 2 identical size tables on two seperate worksheets. I need to locate <only ALL occurances of the value "S4" in 1st table and then transfer to the exact same location within the 2nd table on the 2nd sheet, without overwriting any other data in the 2nd table. Any directiontal thoughts? Jim Pellechi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate all occurrances of a value then transfer to 2nd table on another sheet
Try this!
Sub Andoni() Dim Looked_Value As Variant Dim C As Range Dim First_Address As Variant With Sheets(1) 'This Sheet contain the first Table and the Desired Value 'Range("S4").Value .Visible = True .Activate Looked_Value = .Range("S4").Value End With Application.ScreenUpdating = False With Worksheets(1).Cells 'Put any other range (such a Range("A1:C14")) Set C = .Find(Looked_Value, LookIn:=xlValues) If Not C Is Nothing Then First_Address = C.Address Do Sheets(2).Range(C.Address).Value = Looked_Value 'Sheets(2).Range(First_Address).Select Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address < First_Address Sheets(2).Range("S4").Value = "" End If End Wit -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate all occurrances of a value then transfer to 2nd table on another sheet
Andoni,
Thanks so much ... minor tweak to account for identical table size but at a different location on the 2nd sheet ... Been spending some time trying to understand/unravel your code which works flawlessly!!! Brilliant ... THANKS AGAIN ... J.Pellechi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfer contents from a cell in previous sheet if the sheet is a | New Users to Excel | |||
How can I transfer a required data from sheet 1 to sheet 2 automat | Excel Discussion (Misc queries) | |||
Transfer/Copy Sheet, Dynamically linked Sheet | Excel Discussion (Misc queries) | |||
Locate and retrivie data in table/matrix | Excel Worksheet Functions | |||
Locate all occurrances of a value then transfer to 2nd table on another sheet | Excel Programming |