+ Reply to Thread
Results 1 to 3 of 3

Find Cell and Copy adjacent value to another location

  1. #1
    JJalomo
    Guest

    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. #2
    Jim Thomlinson
    Guest

    RE: 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. #3
    Jim Thomlinson
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1