Variable file name and looping macro to insert formula

[keaveneydan]

Hi
I would like to create a change event macro that will update multiple formulas that pull data from various tabs on a file. The tabs are named with a single date.
So ideally when a certain master cell (Allocation!H2) is updated to the current date the macro will be prompted to update the range that contains formulas and update accordingly...so its and index match fomula based that has a variable being the tab name which is a date.
I have a range of dates in cells F4:AB4 and want the formula to go into F5:AB5, I can then fill that down to whatever row I want...that bit I can handle.

I have tried in vain to cobble something together but I am pretty sure it is horrible. Can anyone help or rather does anyone want to help

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Worksheets("Allocation").Range("H2")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Worksheets("Rating").Select
Dim TABNAMEDATE(2, 23) As String
Dim iCell As Range
Dim i As Integer
Dim n As Integer
Dim Wb As Workbook

TABNAMEDATE(0, 0) = Range("F4").Value

i = 0
n = 0
For Each iCell In Range("F4:AB4")
TABNAMEDATE(0, i) = iCell.Value
i = i + 1
Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
Dim cell As Range: On Error Resume Next
For Each cell In ActiveSheet.Range("F4:AB4").Cells
If cell <> "" Then cell.Offset(1, 0).Formula = "=INDEX('\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$A:$IV,MATCH($A5,'\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$A$1:$A$65536,FALSE),MATCH('\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$R$5,'\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$A$5:$IV$5,FALSE))"
Next cell
Application.Calculation = xlCalculationAutomatic
End Sub

 

[anthonya2369]

I guess the first question is why the TABNAMEDATE array is set to (2,23). It appears that you are setting (0,0) to the F4 value. But then you are only using (0,i) to fill the rest. If that is the case, you only need to dim it to TABNAMEDATE(23).

Also, in the offset(1,0).formula you are using (i,0) but I believe it needs to be turned around to (0,i) to get it to work the way you want.

If cell <> "" Then cell.Offset(1, 0).Formula = "=INDEX('\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$A:$IV,MATCH($A5,'\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$A$1:$A$65536,FALSE),MATCH('\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$R$5,'\\svr01\data\Market_Funds\Fund SEBF\[SEBF Portfolio.xlsx]" & TABNAMEDATE(i, 0) & "'!$A$5:$IV$5,FALSE))"

This is indicating i but the last value of i declared was i = i + 1. What is the tabnamedate in the above formula suppose to actually be? Allocation!H2 value or something else?

 

[keaveneydan]

Hi
Yes I am quite of of my depth I think. I think the array might be OK and the problem lying in with my setting after that. I have an arry of two rows and 23 columns that I would like the loop to go through. So using the date.value in F4, insert a formula in F5, then go to G4 and use that date.value to paste a formula into G5. Then keep going through row 4 to AB4 until the end or until it reaches a blank cell in row 4. Equally I could just skip the blank cell bit and change my fowmula to If(ISNA.....
The offset also looks ok to me. I would like to use the value in row 4 in a formula in row 5, directly below.
I will try and simplify each part and run it on it's own until each component works before hopefully being able to put it together. I wish there was a way of tabbing though a looping macro and seeing hat is happening at each point, I mean displaying what excel is actually doing at each point.
Thanks for your input

 

[anthonya2369]

you could loop through it and see what is happening. If you place a breakpoint in your code and hit F8, it will let you see what is happening. However, you would have to allow for screen updating and calculations to be allowed during this to see what is happening. The screenupdating and manual calculation settings can be turned on after all code has been verified to work as it should.

 

[anthonya2369]

I also want to point out that you have a lot of errors in your code and I do not think your array is working as it should. Look at the formula and you are looping through but you have not indicated the 'i' value again so it is using 23. I had to add three lines to your code to even get it to start running. It is now getting an error on the formula since it is looking for TABNAMEDATE(23,0) which is 'subscript out of range'.