Results 1 to 5 of 5

For Each Loops and copying cells next to c.Value

Threaded View

  1. #1
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Question For Each Loops and copying cells next to c.Value

    Hi all, first I'll submit my code and then the problem.

    Sub MoveData2()
    Dim a As Range
    Dim b As Range
    Application.ScreenUpdating = False
    For Each c In Worksheets("LookTbl").Range("Cansim2").Cells
    'c.Value.Select
    ActiveCell.Select
    Set a = Range("Data1").Find(c.Value)
    Application.Worksheets("IndMthly (2)").Select
    If Not a Is Nothing Then a.Select
    
    Do While ActiveCell.Value = c.Value
    Application.Worksheets("LookTbl").Select
    ActiveCell.End(xlToRight).Offset(, -24).Resize(, 25).Copy
    Application.Worksheets("IndMthly (2)").Select
        ActiveCell.End(xlToRight).Offset(, -24).Select
        ActiveSheet.Paste
        ActiveCell.End(xlToLeft).Select
        ActiveCell.Offset(1, 0).Select
        If ActiveCell.Value = c.Value Then Exit Do
    Loop
    Next c
    Application.ScreenUpdating = True
    End Sub
    The logic behind this is supposed to be:

    1. For each value in one worksheet (LookTbl), find the corresponding value in another worksheet (IndMthly (2)).
    2. When found, copy the values in the range from LookTble and paste them into their proper cells in IndMthly (2).

    The first iteration happens successfully. However, when the second iteration occurs, the range to copy goes back to the first iteration's data range.

    How do I select the cells for Next c when it stays on the first c's row. Sorry if this is confusing but I am confused. If more is needed please let me know.

    Thanks
    Attached Files Attached Files
    Last edited by Mordred; 07-21-2010 at 04:38 PM. Reason: Uploading a file

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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