PDA

View Full Version : find/place based on cell but absolute reference issue/macro mainting sequential order



jw01
12-20-2010, 11:45 PM
Sub CreateFormulas()
Dim ws As Worksheet
Dim MainPath As String
Dim CityBk As String
Dim Rw As Long
Application.DisplayAlerts = False

MainPath = "='C:\Documents and Settings\Archieve 2011\"

For Each ws In Worksheets
If ws.Range("C5") = "Period" Then
CityBk = "\[" & ws.Name & ".xls]G.S Branch'!"
For Rw = 7 To 1119 Step 21
Range("C" & Rw + 1, "M" & Rw + 20).Formula = MainPath & Range("C" & Rw) & CityBk & "C" & Rw - 2
Range("N" & Rw + 1, "Y" & Rw + 20).Formula = MainPath & Range("C" & Rw) & CityBk & "O" & Rw - 2
Next Rw
End If
Next ws

End Sub


the above code simply looks at a column c to insert a field (period/week) into the formula or link so it extracts the data from the corresponding folder i.e. P1W1 (period 1, week 1) to P12W5 (period 12 week 5)

....the cells in range (E:Y) contain a similar formula
i.e.
='J:\xxx\xxx\Archieve 2011\P1W1\[Atlanta-Charleston.xls]G.S Branch'!$E$5

where $E$5, $F$37 etc shouldbe abolute, but when i run the macro, it makes it relative and thus, drags downt the formula. so not sure whats going wrong in there; also, the order is pretty sequential, the range changes after 20 cells (see attached sheet); but the range for P1W1 is going to be the same for P2W1, P3W1 all the way to P12W1 and then P1W2 should be the same as P2W2, P3W3 (hope you follow) until P1W5....

i hope someone can help me as its been time consuming, as i need to add 33 more worksheets (cities) and this macro raelly trims down the manual find/replace function by over 30 mins.....pls help!