Create a Macro to perform formula across all tabs in workbook

[rameezl17]

Hi Everyone,

I am trying to do a Addition Of an Index(Match,(Match formula across all my tabs in my workbook. For an example in excel the formula looks like this.

=INDEX('1'!$I$20:$O$24,MATCH(Database!A8,'1'!$G$20:$G$24,0),MATCH(Database!G1,'1'!$I$17:$O$17,0)) + INDEX('2'!$I$20:$O$24,MATCH(Database!A8,'2'!$G$20:$G$24,0),MATCH(Database!G1,'2'!$I$17:$O$17,0))

However in the future I will have more than just 2 tabs to do the formula above from. All the tabs that are created will keep going up in numerical order so 1,2,3,4, etc.

Is it possible to create a macro that will perform this action across all possible tab names in the future?

Thank you for your help.

Best Regards,

 

[rameezl17]

Got it! It was just as easy as adding IFERROR at the beginning of the formula in the macro

 

[rameezl17]

I got it to work! the updated macro:

Dim i As Long
Dim wsCOUNT As Long
Dim formPART As String
Dim formADD As String
Dim formFULL As String
Dim ws As Worksheet


'this counts all the sheets in your workbook and assigns it to wsCOUNT
wsCOUNT = Application.Sheets.Count




'this loops from sheet 5 to the end of your workbook
For i = 5 To wsCOUNT




'we use form to build your formula using dynamic strings
formPART = "IFERROR(INDEX('" & Sheets(i).Name & "'!$I$20:$O$24,MATCH(Database!$A2,'" & Sheets(i).Name & "'!$G$20:$G$24,0),MATCH(Database!B1,'" & Sheets(i).Name & "'!$I$17:$O$17,0)),0)"


formADD = formPART & "+" & formADD




'this goes through the loop
Next i


formFULL = "=" & Left(formADD, Len(formADD) - 1)


'this pastes the result of your formula to B2
Sheets("Database").Range("B2").Value = formFULL



Question for future references... If I wanted this formula to Reference Sheets("Database").Value("A3") & give me the result on Sheets("Database").Range("B3") As well as what I already have, then will I have to just recreate all of the Dim Strings with new variable names?

Sorry if the question doesn't make sense im having trouble trying to explain...

 

[DataBlake]

Question for future references... If I wanted this formula to Reference Sheets("Database").Value("A3") & give me the result on Sheets("Database").Range("B3") As well as what I already have, then will I have to just recreate all of the Dim Strings with new variable names?

Sorry if the question doesn't make sense im having trouble trying to explain...

unsure if you mean together or seperate so ill explain both
if you want both in the same formula you would just copy to original formPART and formADD lines and change whatever you want
so it would be indexDatabaseA2 + indexDatabaseA3
and then you would change your range from B2 to B3

'we use form to build your formula using dynamic strings
formPART = "IFERROR(INDEX('" & Sheets(i).Name &  "'!$I$20:$O$24,MATCH(Database!$A2,'" & Sheets(i).Name &  "'!$G$20:$G$24,0),MATCH(Database!B1,'" & Sheets(i).Name &  "'!$I$17:$O$17,0)),0)"
formADD = formPART & "+" & formADD
formPART = "IFERROR(INDEX('" & Sheets(i).Name &  "'!$I$20:$O$24,MATCH(Database!$A3,'" & Sheets(i).Name &  "'!$G$20:$G$24,0),MATCH(Database!B1,'" & Sheets(i).Name &  "'!$I$17:$O$17,0)),0)"
formADD = formPART & "+" & formADD

if you want them separate you can create an additional loop to go down however far you want it to.
so lets say you want to go down from A2 to A6

Dim i As Long, x as long
Dim wsCOUNT As Long
Dim formPART As String
Dim formADD As String
Dim formFULL As String
Dim ws As Worksheet


'this counts all the sheets in your workbook and assigns it to wsCOUNT
wsCOUNT = Application.Sheets.Count


'this loops from 2 to 6 to identify your range
for x = 2 to 6

'this loops from sheet 5 to the end of your workbook
For i = 5 To wsCOUNT




'we use form to build your formula using dynamic strings
formPART = "IFERROR(INDEX('" & Sheets(i).Name &  "'!$I$20:$O$24,MATCH(Database!$A" & x & ",'" & Sheets(i).Name &  "'!$G$20:$G$24,0),MATCH(Database!B1,'" & Sheets(i).Name &  "'!$I$17:$O$17,0)),0)"
formADD = formPART & "+" & formADD




'this goes through the loop
Next i


formFULL = "=" & Left(formADD, Len(formADD) - 1)


'this pastes the result of your formula to B2 - B6
Sheets("Database").Range("B" & x).Value = formFULL
next x

i can't test this so i have ZERO clue if this actually works hahahaha
this is just out of my head
but basically if you're referencing A2 it will paste to B2. If you're referencing A3 it will paste to B3 etc
i'm not sure what DatabseB1 is in your formula so for the third time, dunno what it does, can't make sure it works.

 

[DataBlake]

i forgot to reset the formula in the loop
add this below x = 2 to 6

formADD = ""
formPART = ""

 

[DataBlake]

I'm OCD heres the clean version with no comments

Dim i As Long, x As Long
Dim wsCOUNT As Long
Dim formPART As String, formADD As String, formFULL As String
Dim ws As Worksheet



wsCOUNT = Application.Sheets.Count

For x = 2 To 6
    formADD = ""
    formPART = ""

    For i = 5 To wsCOUNT
        formPART = "IFERROR(INDEX('" & Sheets(i).Name & "'!$I$20:$O$24,MATCH(Database!$A" & x & ",'" & Sheets(i).Name & "'!$G$20:$G$24,0),MATCH(Database!B1,'" & Sheets(i).Name & "'!$I$17:$O$17,0)),0)"
        formADD = formPART & "+" & formADD

    Next i

    formFULL = "=" & Left(formADD, Len(formADD) - 1)
    Sheets("Database").Range("B" & x).Value = formFULL
Next x

End Sub

 

[rameezl17]

You are incredible, this worked perfectly! Thank you again for the code and for teaching me, this will help me so much!