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

Question

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.

Does the value of mytrim equal what you expected? Could you put a Watch on x, y and mytrim and confirm everything is evaluating as expected? — JustinJDavies
– JustinJDavies, Commented Jul 16, 2014 at 7:34
Yes mytrim is returning column name and i am able to get to the end function line with no errors. Error appears when i hit F8 at end function line. : str : "$BE$1" : String mytrim : "BE" : String — user2537864
– user2537864, Commented Jul 16, 2014 at 7:40
@user2537864 have you checked the value of Selection? I think that is your problem. I'm guessing it is Nothing or something else odd — psubsee2003
– psubsee2003, Commented Jul 16, 2014 at 7:51

Community · Accepted Answer · 2017-05-23 12:05:34Z

1

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.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jul 16, 2014 at 7:56

psubsee2003

8,7718 gold badges65 silver badges82 bronze badges

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

1 Comment

user2537864 Over a year ago

Found the problem. It is with the column index number. I thought it was not significant to question so just put that as 2. I am using mycell.column for index number and mycell is nothing, which is causing the error. i never used function within a formula so was skeptical about it's functionality. thanks for pointing me in the right direction.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related