CODEWITHSUNDEEP
excelexcel-formulavba
user2537864
user2537864

Reputation: 13

MS Excel 2007 VBA function error (Object variable or with block variable not set)

I am trying to pass a range reference to a vlookup formula using a function. Below is the vlookup formula with the mytrim function:

Selection.Formula = "=vlookup(D1, sheet1!A:" & mytrim(Range("XFD1").End(xlToLeft).Address) & ",2,0)"

Here's the code of my mytrim function:

Public Function mytrim(str As String) As String
Dim x
Dim y
x = InStr(1, str, "$")
y = InStr(x + 1, str, "$")
mytrim = Left(Replace(str, "$", ""), y - x - 1)
End Function

At the execution of end function step error is thrown "Object Variable or with block variable not set"

I am trying to get the vlookup formula as "=vlookup(D1,sheet1!A:AE,2,0)". AE being the last column in sheet1 which might change.

Upvotes: 1

Views: 468

Answers (1)

psubsee2003
psubsee2003

Reputation: 8741

The problem is not with your function. As I found and you have mentioned in the comments, your code runs just fine and generates the correct return value, but you get the error when trying to return from the method.

Your problem is Selection would appear to Nothing. Since you are getting the error on End Function, it means you are running into the problem when you are trying to set the resulting value.

I can't figure out how Selection would not be set (I didn't think it was possible actually) but I managed to repliciate the behavior with:

Sub test()
Dim Rng As Range

    Rng.Formula = "=vlookup(D1, sheet1!A:" & _ 
                   mytrim(Range("XFD1").End(xlToLeft).Address) & ",2,0)"    
End Sub

So you need to check how you are calling this function and make sure everything is defined correctly in the left side of the function call.

Upvotes: 1

Related Questions