Loop through list of Range Names in two columns

[GeeWhiz7]

Hi Folks,
I'm trying to figure out if there is a more efficient way of doing the following.

I have a bunch of named ranges for two different variables that I use a drop down to select (well a dropdown that selects a name that then references the named range and puts its data in the correct column on another sheet).
Once both are selected calculations happen on other sheets and I get an output answer. Here is what I'm wondering, but haven't been successful at making run yet.

• Normally I just hand select each dropdown and then run a macro to capture the results to a summary page.
• I would like to automate the selection for each variable and was wondering if I could use the actual range names (with a loop).
• Example below, Range Names for mu and k data are in B and C columns.
• Selection is in E3 and F3 columns
• Calculations happen elsewhere and a final number ends up in the output cell which I will record by calling another macro.
• Essentially, the ranges in each list are always are matched up by the "#" row they are in, ie Range1 and RangeA, RangeST and RangeF, etc.

So I would like to loop through each pair of variables by the row they are in.

I tried using For Each for a range/single list of names and fill cell E3, but the second list (k) isn't updating so I'm not sure how to do both E3 and F3 at the same time in their respective cells. Or maybe I should be looking at this more from a For Row = 3 to ... instead, but I thought I'd ask here.

 

[offthelip]

does this do what you want??

Sub test()
Dim temparr(1 To 1, 1 To 2) As Variant

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3)) 'pick up list in column B and C
For i = 3 To lastrow
    temparr(1, 1) = inarr(i, 1)
    temparr(1, 2) = inarr(i, 2)
    Range(Cells(3, 5), Cells(3, 6)) = temparr
    ' run your code to copy here
    MsgBox ("check it looks correct")
 Next i
End Sub

 

[GeeWhiz7]

does this do what you want??

Sub test()
Dim temparr(1 To 1, 1 To 2) As Variant

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3)) 'pick up list in column B and C
For i = 3 To lastrow
    temparr(1, 1) = inarr(i, 1)
    temparr(1, 2) = inarr(i, 2)
    Range(Cells(3, 5), Cells(3, 6)) = temparr
    ' run your code to copy here
    MsgBox ("check it looks correct")
 Next i
End Sub

Hi Offthelip, this does work on the example so let me put it in the big workbook and try it there too. Thanks for this! I notice you are using temporary arrays again here like the last solution you provided. I haven't forgotten and will be trying to convert some of my other code to be more efficient by using your recommendations. Thanks again and I'll update the post when I get chance to test in the bigger workbook.

 

[GeeWhiz7]

does this do what you want??

Sub test()
Dim temparr(1 To 1, 1 To 2) As Variant

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3)) 'pick up list in column B and C
For i = 3 To lastrow
    temparr(1, 1) = inarr(i, 1)
    temparr(1, 2) = inarr(i, 2)
    Range(Cells(3, 5), Cells(3, 6)) = temparr
    ' run your code to copy here
    MsgBox ("check it looks correct")
 Next i
End Sub

Hi Offthelip,
I finally got the chance to put your code in a bigger sheet and it works great with only one error that I don't quite understand. The loop works all the way to the last element of the two columns, but then breaks on an out of range error. I tried adding an error handler and tinkered around with the array, but to no avail. I'm new to arrays so can't see to figure it out.

 

[GeeWhiz7]

Hi Offthelip,
I finally got the chance to put your code in a bigger sheet and it works great with only one error that I don't quite understand. The loop works all the way to the last element of the two columns, but then breaks on an out of range error. I tried adding an error handler and tinkered around with the array, but to no avail. I'm new to arrays so can't see to figure it out.

View attachment 55396

I found my error. I added a firstrow variable to limit the for loop maximum from (lastrow - firstrow).
Thanks OfftheLip!

Sub test()
Dim temparr(1 To 1, 1 To 2) As Variant

lastrow = Cells(Rows.Count, "M").End(xlUp).Row
firstrow = 58

MsgBox "Last Row = " & lastrow
inarr = Range(Cells(59, 13), Cells(lastrow, 14)) 'pick up list in column B and C

For i = 1 To lastrow - firstrow
MsgBox "current i is " & i
    temparr(1, 1) = inarr(i, 1)
    temparr(1, 2) = inarr(i, 2)
    Range(Cells(54, 13), Cells(54, 14)) = temparr
    ' run your code to copy here
    MsgBox ("check it looks correct")
    
 Next i
    
Erase inarr
    
End Sub

 

[offthelip]

Well done for sorting the problem out, one way round this sort of probelm is to use the size of the array to determine the limit of the loop, which one can do withthe "Ubound" function like this:
replace:

For i = 1 To lastrow - firstrow

with

For i = 1 to Ubound(inarr,1)

this will work if you change the arrray size which is picked up

 

[GeeWhiz7]

Well done for sorting the problem out, one way round this sort of probelm is to use the size of the array to determine the limit of the loop, which one can do withthe "Ubound" function like this:
replace:

For i = 1 To lastrow - firstrow

with

For i = 1 to Ubound(inarr,1)

this will work if you change the arrray size which is picked up

Well received, thank you!