Unable to get the HLookup property of the WorkbookFunction class

Status
Not open for further replies.

Firasath

Board Regular
Joined
Feb 20, 2011
Messages
115
Hi,

I am trying to build a code to assign a Comment to cell range based on a formula result. I am getting the subject error whenever I run the code. I am week in building codes, I tried my level best but doesn't able to figure out the reason for the same. Require your assistance, please. Please find below the code and the excel template for your reference.

- Trying to insert KPI Target in SLA Adherence based on formula result. Formula result is there in 2nd row for your reference.

VBA Code:
Sub AddComment()

Dim i As Long, rng As range, StartCell As range, StringCmt As Long

Set rng = range("b3")
Set StartCell = rng

'On Error Resume Next

    For i = 1 To 10
    StringCmt = Application.WorksheetFunction.HLookup(rng, range("$K$11:$N$12"), 2)
                 rng.Offset(0, i - 1).AddComment StringCmt
    Next i
'On Error GoTo 0
StartCell.Select
 
End Sub

Book12.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
2Target90%90%95%95%95%97%97%97%97%97%97%97%97%97%97%97%97%97%97%
3SLA Adherence88.0%90.0%93.0%94.0%95.0%97.0%96.0%88.0%96.0%97.0%99.0%88.0%90.0%95.0%97.5%98.9%99.3%100.0%100.0%
8
9
10
11MonthJun-20Aug-20Nov-20
12Target90%95%97%
13
Sheet1
Cell Formulas
RangeFormula
B2:T2B2=HLOOKUP(B1,$L$11:$T$12,2)
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
457
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

If you are getting Run Time error it's because the HLOOKUP function didn't find lookup value in the given range.

In the macro lookup value is B3 cell while in sheet the lookup value is cell B1.

Thanks,
Saurabh
 

Firasath

Board Regular
Joined
Feb 20, 2011
Messages
115
Hi Saurabh,

Thanks for your reply. Identified the error and revised the code. But I am getting error "Application defined or Object defined error". Please assist me in correcting the below code.

Rich (BB code):
Dim i As Integer, rng As range, StartCell As range, Cmt As Variant, dt As range

Set rng = range("b3")
Set StartCell = rng
Set dt = range("b1")

'On Error Resume Next

    For i = 1 To 10
    
                Cmt = Application.WorksheetFunction.HLookup(dt.Offset(0, i - 1), range("$K$11:$N$12"), 2)
                
                rng.Offset(0, i - 1).AddComment Cmt
              
    Next i
'On Error GoTo 0

StartCell.Select
  
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,640
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Invalid Procedue call or Argument

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Status
Not open for further replies.

Forum statistics

Threads
1,143,673
Messages
5,720,222
Members
422,270
Latest member
CaptainMurray

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top