Loop through range to find a value-copy paste to another sheet

  • I have some code that filters the data in a worksheet, searches the cells in one of the columns for a value and then if a match is found, copies the value of the cell two columns to the left to another sheet. I am searching for the values 1, 2 or 3. Could someone help me in completing this macro to look for each of the three numbers and do the copy if they are found. I have tried a few things but I can't seem to get it it to work properly. This is what the code looks like:


    [VB]
    Range("A2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=1, Criteria1:= _
    "RELAY Grade 5"
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=2, Criteria1:="G"


    Dim FirstCell As Integer
    Dim LastCell As Integer
    FirstCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
    LastCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(5, 1).Row


    Dim Cell As Range
    For Each Cell In Range("G" & FirstCell & ":G" & LastCell)
    If Cell.Value = 1 Then Cell.Offset(0, -2).Select
    Selection.Copy
    Sheets("RegionLanes").Range("B6").PasteSpecial (xlPasteValues)
    Next Cell
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Loop through range to find a value-copy paste to another sheet



    Dear,
    Could you provide the sample file so that we can debug it step by step.
    thanks

  • Re: Loop through range to find a value-copy paste to another sheet


    I have worked on the problem and have come up with the code below that works. If you have any other suggestions that might work better, that would be great! Thank you for your help.


    [VB]
    Range("A2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=1, Criteria1:= _
    "RELAY Grade 4"
    ActiveSheet.Range("$A$1:$G$241").AutoFilter Field:=2, Criteria1:="G"

    Dim FirstCell As Integer
    Dim LastCell As Integer
    FirstCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
    LastCell = Range("G2", Cells(Rows.count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(5, 1).Row

    Dim x As Integer
    For x = FirstCell To LastCell
    If Range("G" & x).Value = 1 Then
    Sheets("RegionLanes").Range("B6").Value = ActiveSheet.Range("G" & x).Offset(0, -2).Value
    End If
    If Range("G" & x).Value = 2 Then
    Sheets("RegionLanes").Range("B8").Value = ActiveSheet.Range("G" & x).Offset(0, -2).Value
    End If
    If Range("G" & x).Value = 3 Then
    Sheets("RegionLanes").Range("B9").Value = ActiveSheet.Range("G" & x).Offset(0, -2).Value
    End If
    Next x
    [/VB]

    Files

    • TestFile.xls

      (100.35 kB, downloaded 173 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Loop through range to find a value-copy paste to another sheet


    Hi


    You have multiple 1 2 and 3 values. How do you treat the duplication of values. If your code finds a 1 how do you know you got the right 1 in a situation where multiples 1s exist. Why do you need a vba procedure when native XL formula will do what you require? It would have been a bit easier to decipher if there was some more detail on your Regional Lanes sheet. Anyways have a think about it and get back to the forum.:wink:


    Take care


    Smallman

  • Re: Loop through range to find a value-copy paste to another sheet


    Thank you so much for your suggestions. The data I am searching has only one occurrence of each value so the code works properly with my data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Loop through range to find a value-copy paste to another sheet


    Hi Mumps


    I hope you don't mind a further suggestion after the event. It seems you have a vba procedure where native XL will work fine. Additionally as your vba has hard coded variables it only serves the purpose for the given criteria within the vb. If you want to fill the rest of the form out you will either need to change the criteria or extend the vba.



    A far simplier approach would be to set your sheet up more efficiently. I have laid the data out the way I think it should look and used the following formula based on the new layout not your original layout. It will give you the flexibility to fill the regional lanes sheet out without running any code. It should all run as you input the data into the track A tab. Makes a lot more sense? :)


    =INDEX(TrackA!$E$2:$E$21,MATCH(1,(TrackA!$A$2:$A$21=$B$2)*(TrackA!$G$2:$G$21=$A9)*(TrackA!$B$2:$B$21=$A$2),0))


    Array formula so requires CTR SHIFT ENTER.


    Take care


    Smallman

  • Re: Loop through range to find a value-copy paste to another sheet


    Smallman, your suggestion was terrific! It is certainly more efficient and I don't have to worry about any code. Thank you so much.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.