# A For/Next thing that is beyond me..help please!

#### JasonC71

##### Board Regular
Below is a little code that I've worked out...it takes a value from B6, which is a name, and creates a formula that contains a workbook link in C6....The Code So Far..

Code:
``````Sub SetupLinks()
Dim StoreName As String
Dim WhatSheet As String
Dim Ans As String
On Error GoTo Err
StoreName = Range("B6").Value
WhatSheet = "USER SALES"

Ans = ThisWorkbook.Path & "\" & "[" & StoreName & ".xls" & "]" & WhatSheet & "'!"

Exit Sub
Err:
End Sub``````

This all works great...but what I want to do is this...range B6:B21 contain a list of names...I want the above to loop through each name and put a formula in the corresponding cell in column C...so in the above code..we pull the name in B6 and set it to the variable StoreName, and then we put the finished formula in C6...how do I do the same thing for the name in B7..putting the formula into C7, then B8,C8..and so on....

The only thing that changes in the formulas are the name of the workbook that they are pulling data from....I know this is fairly basic..but after 2 hours of no love I'm reduced to pleading for help...

Thanks everyone,

Jason

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### VoG

##### Legend
This is untested

Code:
``````Sub SetupLinks()
Dim StoreName As String
Dim WhatSheet As String
Dim Ans As String
Dim i As Integer
On Error GoTo Err
For i = 6 To 21
StoreName = Range("B" & i).Value
WhatSheet = "USER SALES"

Ans = ThisWorkbook.Path & "\" & "[" & StoreName & ".xls" & "]" & WhatSheet & "'!"

Range("C" & i).Formula = "=IF((VLOOKUP((\$A\$" & i & "),'" & Ans & "\$A\$8:\$AD\$50,3)=0),0,(VLOOKUP((\$A\$" & i & "),'" & Ans & "\$A\$8:\$AD\$50,AF" & i & ")))"
Next i
Exit Sub
Err:
End Sub``````

#### JasonC71

##### Board Regular
Thanks VoG, I'll give it a whirl tonight...

Thanks again!

Jason

#### lenze

##### Legend
Untested
Rich (BB code):
``````Sub SetupLinks()
dim cl as Range
Dim StoreName As String
Dim WhatSheet As String
Dim Ans As String
On Error GoTo Err
StoreName = Range("B6").Value
WhatSheet = "USER SALES"

Ans = ThisWorkbook.Path & "\" & "[" & StoreName & ".xls" & "]" & WhatSheet & "'!"
For Each cl in Range("\$B\$6:\$B\$21")
Next cl
Exit Sub
Err:
End Sub``````

lenze

Replies
4
Views
317
Replies
1
Views
452
Replies
0
Views
500
Replies
3
Views
568
Replies
3
Views
5K

1,191,204
Messages
5,985,262
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back