Double Nested For Loops in Excel VBA

Question

I am trying to set up a code that first 1.) iterates through a list and replaces values in certain cells and then 2.) copy and pastes values in a list

So a made up example:

 Column A     Column B
   NY          500
   CA          1000
   GA          200

I have a for loop to iterate through column A (to replace values in cells D4,D5,D6 with NY then CA then GA) but I need a second for loop that will copy and paste those values in column B one at a time (e.g. copy and paste value in B1 into B1 after the first replacement of NY, then B2 into B2 after the replacement of CA, then B3, etc)

Sub Macro2()

    Dim x As Integer
    NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
    Range("A1").Select
    For x = 1 To NumRows
        Range("D4") = ActiveCell
        Range("D5") = ActiveCell
        Range("D6") = ActiveCell
        ActiveCell.Offset(1, 0).Select
    Next

End Sub

If i understand this correct you want to copy the value which is in Range A1 into every Range in Column D and then in Column B? — MGP
– MGP, Commented Nov 24, 2015 at 8:41
Basically, I am iterating through column A to replace the values in D4,5,6 (e.g. all 3 cells will have NY in the first iteration, then CA in the second, etc) -- however, I want to iterate through column B values only one at a time. So copy and paste Column B1 after the NY "replacement" happens, then B2 for the CA "replacement", etc — user3682157
– user3682157, Commented Nov 24, 2015 at 8:47
Yes it does. Can you maybe say where the values of Column B should be copied to? — MGP
– MGP, Commented Nov 24, 2015 at 8:49
All I need is to copy and paste the value in B1 into B1, B2 into B2, etc (updated my initial comment above as well) — user3682157
– user3682157, Commented Nov 24, 2015 at 8:51
if you have a formula in B1, then this will be removed after the first iteration and you won't see any changes?! you will just see the value which is located in column B after the first iteration — MGP
– MGP, Commented Nov 24, 2015 at 8:54

SierraOscar · Accepted Answer · 2015-11-24 17:33:17Z

1

I think this is what you're asking for... in which case it can all be done in a single loop - no need for a nested loop.

Sub MM()

    For i = 1 To Cells(1, 1).End(xlDown).Row
        '// Assign the value of Cells(i, 1) [1 = column number] to the range D4:D6.
        [D4:D6] = Cells(i, 1).Value
        '// This is the same as copy -> paste values. Change the "2" for different column
        Cells(i, 2).Value = Cells(i, 2).Value
    Next

End Sub

edited Nov 24, 2015 at 17:33

answered Nov 24, 2015 at 10:43

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3682157 Over a year ago

Hi macro man! Do you mind just explaining what each step of the macro does? Also, where are the specific columns I am iterating through called -- I only ask because in my actual example it's not column a and b but actually column ET and EU

SierraOscar Over a year ago

See edit above - basically just change the second number in Cells(r, c) to the number of the column you wish to look at.

Collectives™ on Stack Overflow

Double Nested For Loops in Excel VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related