0

I was given a project where I need to run some logic before copying and pasting information based on the following. The logic works fine but the issue I'm running into is that no matter what I do I can't get the pastes to occur in the correct spot. Theyre 2x-1 in the current setup. I kept thinking that it has something to do with cell.row but all of my trial, error, and testing has shown no results. If anyone has any ideas I'd be grateful. Changing all of the cell.Row to ((cell.Row+1)/2) doesn't work either.

i.e. were on row 12 and the case is 0. instead of pasting to row 12 it pastes to row 23. for each subsequent row it will paste 2 rows down instead of 1 making the next row 25, and so on.

Sub CopyClass()
'
' Macro2 Macro
'
    Dim rng As Range, cell As Range
    Set rng = Range("B12:B250")
'

    For Each cell In rng

    Select Case cell
    Case Is = 0
        cell(7, 2).Select
        Selection.Copy
        cell(cell.Row, 2).Select
        ActiveSheet.Paste
        Range("G7:R7").Select
        Selection.Copy
        Range(cell(cell.Row, 6), cell(cell.Row, 17)).Select
        ActiveSheet.Paste
    Case Is = 1
        cell(8, 2).Select
        Selection.Copy
        cell(cell.Row, 2).Select
        ActiveSheet.Paste
        Range("G8:R8").Select
        Selection.Copy
        Range(cell(cell.Row, 6), cell(cell.Row, 17)).Select
        ActiveSheet.Paste
    Case Is = 2
        If InStr(cell(cell.Row, 5), "IRA") > 0 Then
            cell(10, 18).Select
            Selection.Copy
            cell(cell.Row, 2).Select
            ActiveSheet.Paste
            cell(9, 18).Select
            Selection.Copy
            cell(cell.Row, 6).Select
            ActiveSheet.Paste
            Range("H9:R9").Select
            Selection.Copy
            Range(cell(cell.Row, 7), cell(cell.Row, 17)).Select
            ActiveSheet.Paste
        Else
            cell(10, 19).Select
            Selection.Copy
            cell(cell.Row, 2).Select
            ActiveSheet.Paste
            cell(9, 19).Select
            Selection.Copy
            cell(cell.Row, 6).Select
            ActiveSheet.Paste
            Range("H9:R9").Select
            Selection.Copy
            Range(cell(cell.Row, 7), cell(cell.Row, 17)).Select
            ActiveSheet.Paste
        End If
    Case Else
        cell(10, 2).Select
        Selection.Copy
        cell(cell.Row, 2).Select
        ActiveSheet.Paste
        Range("G10:R10").Select
        Selection.Copy
        Range(cell(cell.Row, 6), cell(cell.Row, 17)).Select
        ActiveSheet.Paste
    End Select

    Next cell

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
user2471416
  • 31
  • 1
  • 4
  • 2
    Highly suggest you give [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read. Also suggest avoiding `ActiveSheet.Paste`. – BigBen Dec 17 '19 at 15:35
  • 1
    You want `Range(cells(cell.Row, 6), cells(cell.Row, 17))` not `Range(cell(cell.Row, 6), cell(cell.Row, 17))` – Rory Dec 17 '19 at 15:54
  • This was it thanks! any idea why cell was so far off while cells worked perfectly? – user2471416 Dec 17 '19 at 16:00
  • 1
    If you use `Cell` instead of `Cells`, you are offsetting *from each cell*, rather than using an absolute position on the sheet. So if `cell.row` is 4, then `Cell(cell.row, 1)` is actually 3 rows below that (since `Cell(1, 1)` is the Cell itself) – Rory Dec 17 '19 at 16:04
  • Didn't know that. Thanks for the info. – user2471416 Dec 17 '19 at 17:20

0 Answers0