Using String Variable in VBA Formula throwing object/application error

Question

I'm getting an "application-defined or object-defined error" being thrown when I try to set a cell in my active sheet to a formula. I think its due to me trying to use the Sheets.Name function in the formula, see code below:

Public Sub getChannels()
    Dim lastRow As Long
    Dim i As Integer, counter As Integer
    Dim rng As Range, rngB As Range, rngC As Range
    Dim sht As Worksheet

    Dim test As String
    Set sht = Sheets("Summary Sheet - 30-07-2015")
    sht.Activate

    lastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
    For counter = 1 To lastRow Step 3
        If ActiveSheet.Cells(counter, 12) = "LTE 2C" Then
            ActiveSheet.Cells(counter, 16) = _
                "=INDEX('LTE 2C'!C[55],MATCH(""'"" & sht.name &""'""!RC[-14],'LTE 2C'!C[-11],0))"
            ActiveSheet.Cells(counter, 17) = _
                "=INDEX('LTE 2C'!C[53],MATCH(""'"" & sht.name &""'""!RC[-15],'LTE 2C'!C[-12],0))"
            ActiveSheet.Cells(counter, 18) = _
                "=INDEX('LTE 2C'!C[55],MATCH(""'"" & sht.name &""'""!RC[-16],'LTE 2C'!C[-13],0))"

            Range("P" & counter & ":R" & counter).Select
            Selection.Copy
            Range("P" & counter + 1 & ":P" & counter + 2).Select
            ActiveSheet.Paste
        End If

        Next

End Sub

Am I missing something obvious?

Try replacing MATCH(""'"" & sht.name &""'""! with MATCH('" & sht.Name & "'! — BrakNicku
– BrakNicku, Commented Aug 7, 2015 at 13:35
Try ActiveSheet.Cells(Counter, 16) = _ "=INDEX('LTE 2C'!C[55],MATCH('" & sht.name & "'!RC[-14],'LTE 2C'!C[-11],0))" — MatthewD
– MatthewD, Commented Aug 7, 2015 at 13:37

ManishChristian · Accepted Answer · 2015-08-07 13:35:50Z

1

Change your formula like this:

ActiveSheet.Cells(counter, 16) = _
    "=INDEX('LTE 2C'!C[55],MATCH(" & "'" & sht.name & "'" & "!RC[-14],'LTE 2C'!C[-11],0))"
ActiveSheet.Cells(counter, 17) = _
    "=INDEX('LTE 2C'!C[53],MATCH(" & "'" & sht.name & "'" & "!RC[-15],'LTE 2C'!C[-12],0))"
ActiveSheet.Cells(counter, 18) = _
    "=INDEX('LTE 2C'!C[55],MATCH(" & "'" & sht.name & "'" & "!RC[-16],'LTE 2C'!C[-13],0))"

answered Aug 7, 2015 at 13:35

ManishChristian

3,7944 gold badges25 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KFichter · Accepted Answer · 2015-08-07 13:42:23Z

0

Nelly is correct, but another way to do this would be to simply remove the unnecessary extra string for each apostrophe and replace it with this:

ActiveSheet.Cells(counter, 16) = _
"=INDEX('LTE 2C'!C[55],MATCH('" & sht.name & "'!RC[-14],'LTE 2C'!C[-11],0))"

Where the apostrophes are just attached to the strings before and after. It really makes no difference, but it removes the extra (&)s.

answered Aug 7, 2015 at 13:42

KFichter

7934 silver badges15 bronze badges

1 Comment

Nate Eng Over a year ago

Thank you this was very helpful! I was unaware you could use the apostrophe and quotation marks in that way

Collectives™ on Stack Overflow

Using String Variable in VBA Formula throwing object/application error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related