CODEWITHSUNDEEP
excelvba
A.N.
A.N.

Reputation: 590

Index/Match formula referencing separate sheet using VBA

I have an Excel spreadsheet with a number of sheets that will vary from one use case to another. In this example, let's assume I have 3 sheets called "Lever 1", "Lever 2" and "Lever 3". For each one of these there are 3 more sheets titled as follows:
For sheet "Lever 1" - "Lever 1Query1", "Lever 1Query2", "Lever 1Query3"
For sheet "Lever 2" - "Lever 2Query1", "Lever 2 Query2", "Lever 2Query3"
For sheet "Lever 3" - "Lever 3Query1", "Lever 3Query2", "Lever 3Query3"
and so on

I am trying to write a VBA script that will input the following formula in all "Lever X" sheets:

=INDEX('Lever XQuery2'!$C$2:$C$10000,MATCH(1,($F4='Lever XQuery2'!$B$2:$B$10000)*($G4='Lever XQuery2'!$A$2:$A$10000),0))

So far, I have this VBA code which I'm hoping someone can help me fix: 

For i = 1 To wBk.Worksheets.Count
Sheets("Lever " & i).Range("I4:I10000").Values = WorksheetFunction.Index(Range("'Lever " & i & "Query2'!$C$2:$C$10000"), _
            WorksheetFunction.Match(1, ("$F4"=Range("'Lever " & i & "Query2'!$B2$:$B$10000")) _
            * ("$G4"=Range("'Lever " & i & "Query2'!$A2$:$A$10000")),0)
Next i

Upvotes: 1

Views: 205

Answers (1)

VBasic2008
VBasic2008

Reputation: 54983

Copy Formulas Using VBA

  • This covers only Query2. I don't understand the connection to Query1 and Query3. Hopefully, you mean to do something similar using different formulas.

The Code

Option Explicit

Sub copyFormulas()
    
    Const lPattern As String = "LEVER *"
    Const lRangeAddress As String = "I4:I10000"
    Const qSuffix As String = "Query2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim lws As Worksheet ' Each (Possible Lever) Worksheet
    Dim qws As Worksheet ' Query Worksheet
    Dim lwsID As Variant ' Lever Worksheet ID
    Dim lwsName As String ' Lever Worksheet Name
    Dim qwsName As String ' Query Worksheet Name
    
    ' Loop through all worksheets.
    For Each lws In wb.Worksheets
        ' Write the name of the current worksheet to a variable.
        lwsName = lws.Name
        ' Test if current worksheet follows the pattern.
        If UCase(lwsName) Like UCase(lPattern) Then
            ' Attempt (As Variant) to determine the Lever Worksheet ID ('*').
            lwsID = Right(lwsName, Len(lwsName) - Len(lPattern) + 1)
            ' Test if '*' is a number.
            If IsNumeric(lwsID) Then
                ' Determine the Query Worksheet Name.
                qwsName = lwsName & qSuffix
                ' Attempt to create a reference to the Query Worksheet.
                On Error Resume Next
                Set qws = wb.Worksheets(qwsName)
                On Error GoTo 0
                ' Test for existence of Query Worksheet.
                If Not qws Is Nothing Then
                    ' Write formulas to Lever Worksheet.
                    lws.Range(lRangeAddress).Formula = "=INDEX('" _
                        & qwsName & "'!$C$2:$C$10000,MATCH(1,($F4='" _
                        & qwsName & "'!$B$2:$B$10000)*($G4='" _
                        & qwsName & "'!$A$2:$A$10000),0))"
                    Set qws = Nothing
                End If
            End If
        End If
    Next ws

End Sub

Upvotes: 1

Related Questions