Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Cell and Copy adjacent value to another location
I am using the following code to search through multiple sheets for a value.
I need to select the data in the column to the left of the value and copy it to another sheet. What do I need to replace the "C.value = "" " statement with to select and copy to another location? Sub FindEID() Dim c As Range Dim sFirstHit As String 'address of first hit For i = 1 To Sheets.Count 'look in all sheets With Range("c:c") 'use column c on all sheets Set c = .Find(Range("EIDNO").Value, LookIn:=xlValues) 'find EIDNo from Report Worksheet If Not c Is Nothing Then 'find value sFirstHit = c.Address Do 'c.Value = "" Set c = .FindNext(c) Loop While Not c Is Nothing End If End With Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Cell and Copy adjacent value to another location
Try this code...
Public Sub FindEID() Dim rngFound As Range Dim rngToSearch As Range Dim rngcopyto As Range Dim wksCurrent As Worksheet Dim wksCopyTo As Worksheet Dim strFirstAddress As String Set wksCopyTo = Sheets("Sheet1") Set rngcopyto = wksCopyTo.Range("A1") For Each wksCurrent In Worksheets Set rngToSearch = wksCurrent.Range("C1").EntireColumn Set rngFound = rngToSearch.Find(Range("EIDNO").Value, LookIn:=xlValues) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound.EntireRow.Copy rngcopyto Set rngcopyto = rngcopyto.Offset(1, 0) Set rngFound = rngToSearch.FindNext(rngFound) Loop While rngFound.Address < strFirstAddress End If Next wksCurrent End Sub In answer to your specific question you can just copy your range object "c", to a destination range (rngCopyTo in my code). There was one other problem in your code. You would have hit an infinite loop as "c" would potentailly not go to nothing. You had the address string as your stopper, but you did not use it... HTH "JJalomo" wrote: I am using the following code to search through multiple sheets for a value. I need to select the data in the column to the left of the value and copy it to another sheet. What do I need to replace the "C.value = "" " statement with to select and copy to another location? Sub FindEID() Dim c As Range Dim sFirstHit As String 'address of first hit For i = 1 To Sheets.Count 'look in all sheets With Range("c:c") 'use column c on all sheets Set c = .Find(Range("EIDNO").Value, LookIn:=xlValues) 'find EIDNo from Report Worksheet If Not c Is Nothing Then 'find value sFirstHit = c.Address Do 'c.Value = "" Set c = .FindNext(c) Loop While Not c Is Nothing End If End With Next i End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Cell and Copy adjacent value to another location
Sorry this code copies the entire row. You can just modify this to
rngCurrent.offset(0,1).copy rngCopyTo HTH "Jim Thomlinson" wrote: Try this code... Public Sub FindEID() Dim rngFound As Range Dim rngToSearch As Range Dim rngcopyto As Range Dim wksCurrent As Worksheet Dim wksCopyTo As Worksheet Dim strFirstAddress As String Set wksCopyTo = Sheets("Sheet1") Set rngcopyto = wksCopyTo.Range("A1") For Each wksCurrent In Worksheets Set rngToSearch = wksCurrent.Range("C1").EntireColumn Set rngFound = rngToSearch.Find(Range("EIDNO").Value, LookIn:=xlValues) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound.EntireRow.Copy rngcopyto Set rngcopyto = rngcopyto.Offset(1, 0) Set rngFound = rngToSearch.FindNext(rngFound) Loop While rngFound.Address < strFirstAddress End If Next wksCurrent End Sub In answer to your specific question you can just copy your range object "c", to a destination range (rngCopyTo in my code). There was one other problem in your code. You would have hit an infinite loop as "c" would potentailly not go to nothing. You had the address string as your stopper, but you did not use it... HTH "JJalomo" wrote: I am using the following code to search through multiple sheets for a value. I need to select the data in the column to the left of the value and copy it to another sheet. What do I need to replace the "C.value = "" " statement with to select and copy to another location? Sub FindEID() Dim c As Range Dim sFirstHit As String 'address of first hit For i = 1 To Sheets.Count 'look in all sheets With Range("c:c") 'use column c on all sheets Set c = .Find(Range("EIDNO").Value, LookIn:=xlValues) 'find EIDNo from Report Worksheet If Not c Is Nothing Then 'find value sFirstHit = c.Address Do 'c.Value = "" Set c = .FindNext(c) Loop While Not c Is Nothing End If End With Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate, but only if an adjacent cell is identical | Excel Worksheet Functions | |||
find lowest number and return it's adjacent cell | Excel Worksheet Functions | |||
When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
How to find out the cell location | Excel Discussion (Misc queries) | |||
find data in adjacent cell | Excel Discussion (Misc queries) |