Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Houston, Texas
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Help Needed (2003/SP1)

    I need to write a macro to do the following:

    - Read column C in worksheet "One"
    - Compare each cell in column C to each cell in column D in worksheet "Two"
    - If a cell in column C (worksheet "One") matches a cell in column D (worksheet "Two"), then copy the contents of a different cell on the same row column D (worksheet "Two") to another cell in Worksheet "One"
    - Loop through column C in worksheet "One" until all cells have been test.

    Any help will be greatly appreciated!
    Caroline

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help Needed (2003/SP1)

    You need to be a little more specific than "then copy the contents of a different cell". Are we just supposed to pick a random cell on the same row and copy it to a random cell anywhere on worksheet "One"?
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Houston, Texas
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help Needed (2003/SP1)

    Sorry I wasn't more precise. Please see if this is any better.

    - Read column C in worksheet "One"
    - Compare each cell in column C to each cell in column D in worksheet "Two"
    - If the contents C2 (worksheet "One") matches the contents of any cell (row "x") in column D (worksheet "Two"), then copy the contents of "Ex" (column E, row x) in worksheet "Two" to "E2" in Worksheet "One"
    - Loop through column C in worksheet "One" until all cells have been tested

    Caroline

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Macro Help Needed (2003/SP1)

    You could do this without a macro. The formula in E1 on "One" would be

    =IF(ISNA(VLOOKUP(C1,Two!$D$1:$E$5,2,FALSE)),"",VLO OKUP(C1,Two!$D$1:$E$5,2,FALSE))

    (replace 5 with the row number of the last used row in column D on "Two". Fill down as far as needed. If you want a macro, try this:

    Sub MatchOneTwo()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngMaxTargetRow As Long
    Dim rngCell As Range
    Dim rngTarget As Range

    Set wshSource = Worksheets("One")
    Set wshTarget = Worksheets("Two")

    lngMaxTargetRow = wshTarget.Range("D65536").End(xlUp).Row
    Set rngTarget = wshTarget.Range("D1" & lngMaxTargetRow)

    lngMaxRow = wshSource.Range("C65536").End(xlUp).Row
    For lngRow = 1 To lngMaxRow
    Set rngCell = rngTarget.Find( _
    What:=wshSource.Range("C" & lngRow), _
    LookAt:=xlWhole)
    If Not rngCell Is Nothing Then
    wshSource.Range("E" & lngRow) = rngCell.Offset(0, 1)
    End If
    Next lngRow

    Set rngCell = Nothing
    Set rngTarget = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing
    End Sub

    The code can easily be modified to start in row 2 instead of row 1.

Posting Permissions

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