Prev: Automate Copy and Paste?
Next: How to evaluate VB constant name in string variable?
From: bigjim on 10 Feb 2010 20:34
I am trying to use the lookup function in my vba code. I'm using excel 2003.
Here is what I have so far, but it isn't working. I'm getting the error
"unable to get the lookup property of the worksheet function class"

Dim name As String
name = Range("e800")
Dim Start As Integer
Start = Application.WorksheetFunction.Lookup(name, "Q800:Q881", "t800:t881")


This last line is what is highlighted

I'm kind of new to this stuff, so I realize this may sound simple, but I'm
having trouble getting it to work and any help would be appreciated.

Jim
From: JLGWhiz on 10 Feb 2010 21:32
Try changing this line:

Start = Application.WorksheetFunction.Lookup(name, "Q800:Q881", "t800:t881")

To this:

Start = Application.WorksheetFunction.Lookup(name, _
Range("Q800:Q881"), Range("t800:t881"))





"bigjim" <bigjim(a)discussions.microsoft.com> wrote in message
news:265D979D-F551-4583-AFAD-4650B4128955(a)microsoft.com...
>I am trying to use the lookup function in my vba code. I'm using excel
>2003.
> Here is what I have so far, but it isn't working. I'm getting the error
> "unable to get the lookup property of the worksheet function class"
>
> Dim name As String
> name = Range("e800")
> Dim Start As Integer
> Start = Application.WorksheetFunction.Lookup(name, "Q800:Q881",
> "t800:t881")
>
>
> This last line is what is highlighted
>
> I'm kind of new to this stuff, so I realize this may sound simple, but I'm
> having trouble getting it to work and any help would be appreciated.
>
> Jim


From: bigjim on 10 Feb 2010 22:50

I knew it would be something simple. Thanks so much. It works perfectly now.
"bigjim" wrote:

> I am trying to use the lookup function in my vba code. I'm using excel 2003.
> Here is what I have so far, but it isn't working. I'm getting the error
> "unable to get the lookup property of the worksheet function class"
>
> Dim name As String
> name = Range("e800")
> Dim Start As Integer
> Start = Application.WorksheetFunction.Lookup(name, "Q800:Q881", "t800:t881")
>
>
> This last line is what is highlighted
>
> I'm kind of new to this stuff, so I realize this may sound simple, but I'm
> having trouble getting it to work and any help would be appreciated.
>
> Jim
From: JLGWhiz on 10 Feb 2010 23:05
If you had been posting a formula to the worksheet, then you would have had
the correct structure for the formula, but in code the keyword Range must be
used so that VBA can compile and execute the formula. Glad it worked for
you.


"bigjim" <bigjim(a)discussions.microsoft.com> wrote in message
news:07E84056-61D1-4ACE-BFBF-4751E09F7C7A(a)microsoft.com...
>
> I knew it would be something simple. Thanks so much. It works perfectly
> now.
> "bigjim" wrote:
>
>> I am trying to use the lookup function in my vba code. I'm using excel
>> 2003.
>> Here is what I have so far, but it isn't working. I'm getting the error
>> "unable to get the lookup property of the worksheet function class"
>>
>> Dim name As String
>> name = Range("e800")
>> Dim Start As Integer
>> Start = Application.WorksheetFunction.Lookup(name, "Q800:Q881",
>> "t800:t881")
>>
>>
>> This last line is what is highlighted
>>
>> I'm kind of new to this stuff, so I realize this may sound simple, but
>> I'm
>> having trouble getting it to work and any help would be appreciated.
>>
>> Jim


 | 
Pages: 1
Prev: Automate Copy and Paste?
Next: How to evaluate VB constant name in string variable?