Array to Loop through Cell Values Excel VBA

Question

I have defined cells inside of two separate arrays. (i.e. source_arr = ("B4","B5"...) and target_arr = ("B5","B6")

I want to loop through both arrays and set the value of the target workbook cells equal to that of the source workbook cells. Right now it sets all the cells equal to one value.

 For i = LBound(source_array) To UBound(source_array)
For j = LBound(target_array) To UBound(target_array)
Data = source_workbook.Sheets("Questionnaire").Cells(source_array(i)).Value
target_workbook.Sheets("Questionnaire").Cells(target_array(j)).Value = Data
Next j
Next i

You may not be needing two arrays at all. Could you post a screenshot of your data? — user4066647
– user4066647, Commented Oct 13, 2017 at 14:48

Scott Craner · Accepted Answer · 2017-10-13 14:48:34Z

2

You only need to one loop. And you want Range not Cells:

For i = LBound(source_array) To UBound(source_array)
    Data = source_workbook.Sheets("Questionnaire").Range(source_array(i)).Value
    target_workbook.Sheets("Questionnaire").Range(target_array(i)).Value = Data
Next i

answered Oct 13, 2017 at 14:48

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

Mdurocher Over a year ago

I did manage to fix the Range part on my own as soon as I posted the question...*rolls eyes* However for some reason I was thinking I needed 2 loops! Such a bozo, I was over thinking it. Thanks for the help it works perfect.

Collectives™ on Stack Overflow

Array to Loop through Cell Values Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related