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

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

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!