Wild charakter in formula

[MiGon]

Hi,
I have created macro which suppose to do the index matching, works fine but need to change to work with the with wildcard in the name.
Thank you

Sub index_reclaims()
'
' index_reclaims
Dim w As Workbook
 
For Each w In Application.Workbooks
   If (w.Name) Like "*Summary*" Then
      Exit For
   End If
 Next w
 If Not w Is Nothing Then
 w.Activate
 Else
     MsgBox "Please open 'High Tech Wholesalers Summary Report all workbook'!"
     Exit Sub
 End If
' convert to numbers
    w.Activate
    Sheets(2).Activate
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]*1"
    Range("F2").Select
  
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
  
    Selection.AutoFill Destination:=Range("F2:F" & Lastrow)
    Range("F2:F" & Lastrow).Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A24").Select
    Windows("Payment Master template.xlsm").Activate

    
  
'index
  
'*********************************************************************************
  Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('[High Tech Wholesalers Summary Report all.xls]Reclaims'!R2C4:R15C4,MATCH(RC[-12],'[High Tech Wholesalers Summary Report all.xls]Reclaims'!R2C1:R15C1,0))"
  
    Range("M2").Select
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    Selection.AutoFill Destination:=Range("M2:M" & Lastrow)
  
'*********************************************************************************


 '    values_only Macro
'

    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    Range("M2:M" & Lastrow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("M2").Select
    Application.CutCopyMode = False

End Sub

 

[Joe4]

Do you mean this line here?

   If (w.Name) Like "*Summary*" Then

Try this instead:

    If InStr(w.Name, "Summary") > 0 Then

See here for details on the InStr function: MS Excel: How to use the INSTR Function (VBA)

 

[MiGon]

Do you mean this line here?

   If (w.Name) Like "*Summary*" Then

Try this instead:

    If InStr(w.Name, "Summary") > 0 Then

See here for details on the InStr function: MS Excel: How to use the INSTR Function (VBA)

Hi Joe4,
Sorry wasn't precise enough, firt part of the code works, only cannot figure out index match how to replace full name of the workbook with declared 'w'
not sure how to show vba should take info from 'w' again in the formula

Thanks

ActiveCell.FormulaR1C1 = _
"=INDEX('[w]Reclaims'!R2C4:R15C4,MATCH(RC[-12],'[w]Reclaims'!R2C1:R15C1,0))"