How do I run a column range through a table and paste it in another column using VBA?

Question

So I'm trying to run a column through a table in Excel using VBA. I then want to copy the result and paste in another column. I've gotten it to work for one cell, however, when I try to loop the code, it just pastes the same thing in every cell in the range I want it to paste in. How do I make it so that when it loops, it only pastes in the single cell vs. the entire range? My code is below.

Sub Test1()
'
' Test1 Macro
'

'
Dim rng As Range, cell As Range
Set rng = Range("C16:C20")
For Each cell In rng
Dim rng2 As Range, cell2 As Range
Set rng2 = Range("G16:G20")
For Each cell2 In rng2

    cell.Select
    Selection.Copy
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D12").Select
    Application.CutCopyMode = False
    Selection.Copy
    rng2.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Tranpose:=False
      'ActiveCell.Offset(1, 0).Select

Next cell2
Next cell

End Sub

Thanks!

score 0 · Accepted Answer · answered May 18 '20 at 00:32

Guessing you want something like this:

Sub Test1()

    Dim rng As Range, cell As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set rng = ws.Range("C16:C20")

    For Each cell In rng.Cells
        ws.Range("B4").value = cell.Value
        cell.offset(0, 4).value = ws.Range("D12").Value  'populate in Col G
    Next cell

End Sub

Note there's typically no need to select/activate anything in excel (though the macro recorder does that a lot). Worth reviewing this: How to avoid using Select in Excel VBA

Likewise if you need to transfer values between cells you can do that directly without copy/paste.

How do I run a column range through a table and paste it in another column using VBA?

1 Answers1