Type mismatch. Find address of Match to use as variable- syntax???

[jmthompson]

I am hoping this is just a syntax issue.

Within VB, I am trying to find the address for a match for my cell value to use later as part of an address for a hyperlink. Here is a snippet of my code

For Each c In Range("G2:I" & lastRow)
    If c <> "" Then
    myAddr = WorksheetFunction.Address(2, WorksheetFunction.Match(c, Workbooks(wbNew).Sheets("MCC_Breakout").Range("C2:CA2"), 0))

wbNew is dimmed as Workbook, myAddr is dimmed as Range

I keep getting a Type mismatch error on the red row

 

[Jonmo1]

You would dim myAddr as a String, since WorksheetFunciton.Address returns a text string.

 

[jmthompson]

Just tried it...denied. Still type mismatch.

Here's my full code (still in progress and not pretty), just in case something I don't suspect is the root:

Sub breakout()
Application.DisplayAlerts = False
Dim Sh As Worksheet
Dim lastRow As Long
Dim c As Range
Dim wbMain As Workbook
Dim wbNew As Workbook
Dim myYear As String
Dim myPath As String
Dim myAddr As String
Dim i As Long
myYear = Format(Now(), "yyyy")
myPath = "G:\PCard Directory\Quarterly Reviews\" & myYear & "\Q1\"

Set wbMain = ThisWorkbook

Sheets(Array("10100 Summary", "10100 Cardholders with MCC", "10100 Livelink_PaymentNet", "MCC")).Select

  Windows(1).SelectedSheets.Copy

  For Each Sh In Worksheets
    With Sh.UsedRange
      .Copy
      .PasteSpecial xlPasteValues
    End With
  Next

  Application.CutCopyMode = False

  
Set wbNew = ActiveWorkbook
wbNew.SaveAs (myPath & "Star-Ledger Q1.xls")

Sheets("MCC").Name = "MCC_Breakout"
Sheets("MCC_Breakout").Select
For i = 46 To 26 Step -1
If (Cells(2, i).Value) <> "987" Then
Cells(2, i).EntireColumn.Delete
End If
Next i


Sheets("10100 Cardholders with MCC").Select
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("G2:I" & lastRow)
    If c <> "" And c.Value <> "000" Then
    myAddr = WorksheetFunction.Address(2, WorksheetFunction.Match(c, Workbooks(wbNew).Sheets("MCC_Breakout").Range("C2:CA2"), 0))

    ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= _
        "Star-Ledger%20Q1.xls", SubAddress:="MCC_Breakout!" & myAddr, TextToDisplay:=c.Value
    End If
Next c

wbNew.Save

Application.DisplayAlerts = True

End Sub

 

[Jonmo1]

You have wbnew dimmed as Workbook, and set to activeworkbook..

Therefor
Workbooks(wbNew).Sheets("MCC_Breakout").Range("C2:CA2")
should be just
wbNew.Sheets("MCC_Breakout").Range("C2:CA2")

 

[jmthompson]

Progress! Got past the type mismatch, now I am getting Object doesn't support this property or method. Same row highlighted.

I am not entirely sure that Address is a worksheet function...

 

[Jonmo1]

Also, I don't think you can use worksheetfunction.address in VBA..

Try

x = Cells(2,match(...)).Address

 

[jmthompson]

Saved again, by jonmo1! You should really sell t-shirts!