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..
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
Code:
Sub SetupLinks()
Dim StoreName As String
Dim WhatSheet As String
Dim Ans As String
On Error GoTo Err
Application.DisplayAlerts = False
StoreName = Range("B6").Value
WhatSheet = "USER SALES"
Ans = ThisWorkbook.Path & "\" & "[" & StoreName & ".xls" & "]" & WhatSheet & "'!"
Range("c6").Formula = "=IF((VLOOKUP(($A$6),'" & Ans & "$A$8:$AD$50,3)=0),0,(VLOOKUP(($A$6),'" & Ans & "$A$8:$AD$50,AF6)))"
Application.DisplayAlerts = True
Exit Sub
Err:
Application.DisplayAlerts = True
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