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

JasonC71

Board Regular
Joined
Feb 10, 2008
Messages
159
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
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
:eek: This is untested :eek:

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

lenze
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top